If looking for a free database, why not use PostgreSQL that is officially supported by PB?

I did some testing where I added a view to a SQLite database that used the date and time functions to return parts from a datetime column. I used the driver from http://www.ch-werner.de/sqliteodbc/ which was last updated June 20, 2020.

I then ran my ODBC API example against it.

The column type id is reported as -9 (SQL_WVARCHAR).

The column type name is reported as an empty string.

Hi Miguel;

  You are correct ... SQLite has never been a supported DBMS in either a PS/Sybase/SAP/Appeon versions of PB regardless of the driver (ODBC or JDBC). Thus, while you can try to use these access methods (JDBC coming back in PB 2022 MR#1) - they are very problematic from my varied experiences. Basically, "use at own risk" - especially for production heavy DML applications. IMHO

   If you look in the PBODB.INI file, you will not see any reference to SQLite either. The same for JDBC. In these cases, note that ODBC and JDBC are "middle-ware" and thus DBMS interaction is at "arms length" from PB and any compiled PB App. That also means that PB DB interfaces generate JDBC or ODBC SQL - not the actual DML of the target DBMS. The middle-ware then converts this SQL to the DBMS specific SQL (in this case SQLite). In the return trip, the SQLite "result set" is converted back to an ODBC or JDBC result set and that is what the PB ODBC/JDBS interface sees for both outbound and inbound DML operations. The middle-ware layer then in its DML conversions back & forth can easily misinterpret things at the expense of the PB App's execution. However, a native SQLite DB driver would be different as it would deal with the SQLite DBMS directly avoiding these DML conversions / misinterpretations.

    The JDBC & ODBC DB drivers too are often the culprit because they are often supplied by third party or Open Source software vendors. So again the "luck" of using these with a non-vendor supplied / supported DB client driver is often the cause of many headaches. I have seen this countless times before.

    Anyway, that's just my take on SQLite from my many failed experiences over a few decades to make this DBMS work in a more complex DBMS schema based application.  However, it maybe OK for very small "simplex" DBMS use. Of course personally, I would go with SQLServer Express even for those simplex DBMS applications. Just my $0.02.

HTH

Regards ... Chris

Thanks John Fauss,

I slightly modified the query and added two more fields:
***********************************************
The query is

ls_sqlstatement = "Select 'Test Value' as field1, " + &
"cast('2022-11-01' as date) as field2, " + &
"cast('2022-11-01 09:36:00' as datetime) as field3, " + &
"cast(null as time) as field4, " + &
"cast(1.2345 as float) as field5, " + &
"'2022-11-01 09:35:00' as field6, " + &
"3.444 as field7 " + &
"From Departments "

Here is the result:
Field#01: Type = String    Value: Test Value
Field#02: Type = Long      Value: 2022
Field#03: Type = Long      Value: 2022
Field#04: Type = String    Value: (null)
Field#05: Type = Double   Value: 1.2345
Field#06: Type = String     Value: 2022-11-01 09:35:00
Field#07: Type = Double    Value: 3.444

Btw, my reported issue is NOT there when we select a field from a database table, but the issue is there only when we create computed column(s).   All columns' datatype is char(255) irrespective of whatever we try to cast.   

Without getting actual datatype(s) for computed columns, it might be hard to validate the input(s) for those columns.  

I wonder if adding a section to pbodb.ini would help.

Here's some code you can use to determine what PB is "seeing" from the ODBC (or any other) DB driver.

Replace the supplied SELECT statement assignment at the beginning with a SELECT statement that is valid for your DBMS:

Integer li_response
Long    ll_rc, ll_row, ll_index
String  ls_sqlstatement, ls_datatype, ls_value, ls_name, ls_msg

// Define the SQL SELECT statement.

// Note: The supplied SELECT statement is valid for SQL Server... 
//       Code a SELECT statement that is appropriate for your DBMS.
ls_sqlstatement = "Select 'Test Value' as field1, " + &
   "cast('2022-11-01' as date) as field2, " + &
   "cast('2022-11-01 09:36:00' as datetime) as field3, " + &
   "cast(null as time) as field4, " + &
   "cast(1.2345 as float) as field5 " + &
   "From (Select a=1) t"

// Declare a cursor that will examine each row.
DECLARE c_values_cursor	DYNAMIC CURSOR FOR SQLSA;

// Obtain information about the dynamic SQL statement.
PREPARE SQLSA FROM :ls_sqlstatement USING SQLCA;
DESCRIBE SQLSA INTO SQLDA;

// Execute the SELECT statement by opening the cursor.
OPEN DYNAMIC c_values_cursor USING DESCRIPTOR SQLDA;

// Fetch the first row of the result set.
FETCH c_values_cursor USING DESCRIPTOR SQLDA;
ll_rc  = SQLCA.SQLCode
ll_row = 0

// Obtain the descriptor and value for each column in the result set.
Do While SQLCA.SQLCode = 0
   ll_row ++
   ls_msg = ""
   
   For ll_index = 1 To SQLDA.NumOutputs
      Choose Case SQLDA.OutParmType[ll_index]
         Case TypeBoolean!
            ls_datatype = "Boolean"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then
               ls_value = "(null)"
            ElseIf ls_value = "0" Then
               ls_value = "False"
            Else
               ls_value = "True"
            End If
         Case TypeByte!
            ls_datatype = "Byte"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeDate!
            ls_datatype = "Date"
            ls_value    = String(GetDynamicDate(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeDateTime!
            ls_datatype = "Datetime"
            ls_value    = String(GetDynamicDateTime(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeDecimal!
            ls_datatype = "Decimal"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeDouble!
            ls_datatype = "Double"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeInteger!
            ls_datatype = "Integer"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeLong!
            ls_datatype = "Long"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeLongLong!
            ls_datatype = "LongLong"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeReal!
            ls_datatype = "Real"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeString!
            ls_datatype = "String"
            ls_value    = GetDynamicString(SQLDA,ll_index)
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeTime!
            ls_datatype = "Time"
            ls_value    = String(GetDynamicTime(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeUInt!
            ls_datatype = "UInt"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case TypeULong!
            ls_datatype = "ULong"
            ls_value    = String(GetDynamicNumber(SQLDA,ll_index))
            If IsNull(ls_value) Then ls_value = "(null)"
         Case Else
            ls_datatype = "Unknown"
            ls_value    = "????"
      End Choose
      
      If ls_msg <> "" Then ls_msg += "~r~n"
      ls_msg += "Field #" + String(ll_index,"00") + ":  Type = " + &
                ls_datatype + "~tValue: " + ls_value
   Next
   
   ls_msg += "~r~n~r~nContinue with the next row?"
   
   li_response = MessageBox("Row Number " + String(ll_row), &
                            ls_msg,Question!,YesNoCancel!,1)
   
   If li_response = 1 Then
   FETCH c_values_cursor USING DESCRIPTOR SQLDA;
      ll_rc = SQLCA.SQLCode
   Else
      // Exit the Fetch loop.
      SQLCA.SQLCode = 999
   End If
Loop

// Close the cursor before leaving.
Close c_values_cursor;

Return ll_rc

Appeon does not support ODBC SQLLite. For this very reason you'll find lots an lots of problems.

If you're not using PB2022, you might have better luck using a JDBC driver for SQLLite.

I think you are on PB2019, so it's worth giving it a try.

(I've been told that Appeon will bring back JDBC connectivity somewhere at the end of next year, but for now PB2022 no longer supports it.)

regards.

Hi.

Also not having great experience in sqlite. I want to add to John's answer the following info from sqlite documentation (Datatypes In SQLite):

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

As for computed columns, I see the following (Generated Columns (sqlite.org)):

1. Introduction

Generated columns (also sometimes called "computed columns") are columns of a table whose values are a function of other columns in the same row. Generated columns can be read, but their values can not be directly written. The only way to change the value of a generated column is to modify the values of the other columns used to calculate the generated column.

I guess that powerbuilder isn't aware of the way sqlite internally handles computed columns. But I may be wrong.

Andreas.

When PB asks the database "under the covers" for a description of the query's result set, it appears that the ODBC driver is telling PB that the column(s) in question are a string.

Purely a guess on my part, since I don't use SQLite or ODBC, but I suspect it may be due to the antiquated ODBC driver. A quick look at the "online documentation" provided at the URL you supplied contained this (emphasis mine):

Restrictions of data type mapping:

- Integer and floating point columns in the database are reported
  as NULLs when no digit seen in the column, otherwise all digits
  up to end of string or non-digit are interpreted as the value,
  i.e. '10blurk' is ten, '0blurk' is zero, but 'blurk' is NULL.
- Format for SQL_DATE is YYYY-MM-DD or YYYYMMDD
- Format for SQL_TIME is hh:mm:ss or hhmmss
- Format for SQL_TIMESTAMP is
      YYYYMMDDhhmmss[fraction]
  or  YYYY-MM-DD hh:mm:ss[.fraction]
  or  hh:mm:ss[.fraction] YYYY-MM-DD
  The fractional part is expressed as 1E-09 seconds
- The driver puts the ODBC string representations for date/time,
  (eg for "{ts '2001-10-10 12:58:00'}" the substring within the
  single quotes) directly into the SQLite column

Also, your question states you are trying to obtain a date, yet the SQL snippet shows the CAST datatype as datetime.

The documentation appears to be from around 2001 based solely on my very quick glance, so I cannot help but wonder if you should try using an alternative ODBC driver for SQLite that is newer. Perhaps, however, it is only the documentation and web site that appear to be so greatly out of date.

Best regards, John