Again on SQLite and date times.
lazarus / 16-Databases

----------------------------------------------------------------------
Чебурашка · 2022-11-14 13:14

Hello,

I have a table like this in SQlite

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE ExampleT(  id integer not null,  fieldDate datetime); 
If I do a TSQLQuery and I try to read the field fieldDate .AsDateTime
it succeds.

Instead if I define the table like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE ExampleT(  id integer not null,  fieldDate NUMERIC); 
(should be equivalent to the previous as DATETIME is treated as
NUMERIC in SQlite - https://www.sqlite.org/datatype3.html, also I can
see that the data stored in doth cases is the same, using the sqlite3
cli program)

If I do a TSQLQuery and I try to read the field fieldDate .AsDateTime
it fails telling: Invalid type conversion to TDateTime in field
fieldDate.

I guess that the free pascal code determines the field type using the
table field definition.

Is it possible to force a TField to be what one wants without relying
only on the table defintion?

----------------------------------------------------------------------
Zvoni · 2022-11-14 13:56

And as i told you in your other thread(s): Don't use those DataTypes!
There is no DateTime DataType, and NUMERIC is not a DataType but an
Affinity.
Use the proper Storage-Classes.

And TDateTime wants a double.
"Spot the mistake"

----------------------------------------------------------------------
Чебурашка · 2022-11-14 14:25

Quote from: Zvoni on November 14, 2022, 02:56:13 pmAnd as i told you
in your other thread(s): Don't use those DataTypes!
There is no DateTime DataType, and NUMERIC is not a DataType but an
Affinity.
Use the proper Storage-Classes.

And TDateTime wants a double.
"Spot the mistake"

After your reply, I recreated the DB with DATETIME mapped to DOUBLE,
in order to follow strictly your indications (unless I did not
undestrand them fully).

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQLite
version 3.34.1 2021-01-20 14:10:07Enter ".help" for usage
hints.sqlite> .schema 'T0041%'CREATE TABLE T0041_MachineLog
(refMachine INTEGER NOT NULL, id UNSIGNED BIG INT NOT NULL, deleted
INTEGER NOT NULL, refImport INTEGER NOT NULL, eventDate DOUBLE NOT
NULL, eventType INTEGER NOT NULL, eventVersion INTEGER NOT NULL,
eventDataType INTEGER, eventData BLOB, CONSTRAINT T0041_MachineLog_PK
PRIMARY KEY (refMachine, id), CONSTRAINT T0041_FK_T0003 FOREIGN KEY
(refMachine) REFERENCES T0003_ConfMachines(id));CREATE INDEX
T0041_MachineLog_FKI ON T0041_MachineLog(refMachine); 
The field in question now is:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i eventDate
DOUBLE NOT NULL, 
Hereafter it is a query made on that table, where you can see that it
does contain data (=writing seems to work, probably writing doe not
guess datatype from db structure)

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
SELECT * FROM T0041_MachineLog LIMIT 5;refMachine|id|deleted|refImport
|eventDate|eventType|eventVersion|eventDataType|eventData1|1|0|1|24556
15.89571759|1|1|0|1|2|0|1|2455615.89572917|11|1|0|1|3|0|1|2455615.8957
4074|11|1|0|1|4|0|1|2455615.89574074|11|1|0|1|5|0|1|2455615.89586806|2
|1|0| 
And here are the errors related to the reading of this field (and all
others that should be read AsDateTime):

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Localization system auto-analysis disabled. To set the compiler
directive DEBUG_LOCALIZATION and execute the programProcessing machine
"New machine1" with code "" ...Found 37 source prodlog filesProcessed
37 new files and 0 changed filedUnexpected error while LoadEvents on
eventId 1, eventType 1/version 1 form machine 1 (exception: Invalid
type conversion to TDateTime in field eventDate)Unexpected error while
ProcessEvents (exception: Invalid type conversion to TDateTime in
field eventDate)Unexpected error while recaching data of machine 1
(exception: Invalid type conversion to TDateTime in field
eventDate)Unexpected error while compiling daily summaries of machine
1 (exception: Invalid type conversion to TDateTime in field
eventDate)Processing machines completedUnexpected error while loading
products in interval (exception: Invalid type conversion to TDateTime
in field eventDate)Unexpected error while loading products in interval
(exception: Invalid type conversion to TDateTime in field eventDate) 

Any ideas at this point?

----------------------------------------------------------------------
Zvoni · 2022-11-14 14:32

Quote from: tt on November 14, 2022, 03:25:49 pm
After your reply, I recreated the DB with DATETIME mapped to DOUBLE,
in order to follow strictly your indications (unless I did not
undestrand them fully).

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQLite
version 3.34.1 2021-01-20 14:10:07Enter ".help" for usage
hints.sqlite> .schema 'T0041%'CREATE TABLE T0041_MachineLog
(refMachine INTEGER NOT NULL, id UNSIGNED BIG INT NOT NULL, deleted
INTEGER NOT NULL, refImport INTEGER NOT NULL, eventDate DOUBLE NOT
NULL, eventType INTEGER NOT NULL, eventVersion INTEGER NOT NULL,
eventDataType INTEGER, eventData BLOB, CONSTRAINT T0041_MachineLog_PK
PRIMARY KEY (refMachine, id), CONSTRAINT T0041_FK_T0003 FOREIGN KEY
(refMachine) REFERENCES T0003_ConfMachines(id));CREATE INDEX
T0041_MachineLog_FKI ON T0041_MachineLog(refMachine); 
The field in question now is:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i eventDate
DOUBLE NOT NULL, Close. Use REAL instead of Double (but SQLite will
map Double to Real in 99.9999% of cases)
QuoteHereafter it is a query made on that table, where you can see
that it does contain data (=writing seems to work, probably writing
doe not guess datatype from db structure)

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
SELECT * FROM T0041_MachineLog LIMIT 5;refMachine|id|deleted|refImport
|eventDate|eventType|eventVersion|eventDataType|eventData1|1|0|1|24556
15.89571759|1|1|0|1|2|0|1|2455615.89572917|11|1|0|1|3|0|1|2455615.8957
4074|11|1|0|1|4|0|1|2455615.89574074|11|1|0|1|5|0|1|2455615.89586806|2
|1|0| Remove the LIMIT 5
I want to see ALL those Dates

----------------------------------------------------------------------
Чебурашка · 2022-11-14 14:41

Quote from: Zvoni on November 14, 2022, 03:32:21 pmRemove the LIMIT 5
I want to see ALL those Dates

Attached

----------------------------------------------------------------------
Чебурашка · 2022-11-14 14:49

Quote from: Zvoni on November 14, 2022, 03:32:21 pmClose. Use REAL
instead of Double (but SQLite will map Double to Real in 99.9999% of
cases)

Modified DB to REAL, retried.
Same problem.

Any other idea?

----------------------------------------------------------------------
Zvoni · 2022-11-14 15:05

Quote from: tt on November 14, 2022, 03:41:02 pmQuote from: Zvoni on
November 14, 2022, 03:32:21 pmRemove the LIMIT 5
I want to see ALL those Dates

Attached
OK, looks consistent.
Will have to check what got borked.

By any chance: Could you give me the INSERT-Statements for those
Values? (dump the table).
Otherwise i would have to write a routine to import it to my local
sqlite-database

----------------------------------------------------------------------
Чебурашка · 2022-11-14 15:17

Quote from: Zvoni on November 14, 2022, 04:05:49 pmQuote from: tt on
November 14, 2022, 03:41:02 pmQuote from: Zvoni on November 14, 2022,
03:32:21 pmRemove the LIMIT 5
I want to see ALL those Dates

Attached
OK, looks consistent.
Will have to check what got borked.

By any chance: Could you give me the INSERT-Statements for those
Values? (dump the table).
Otherwise i would have to write a routine to import it to my local
sqlite-database

Attached!

----------------------------------------------------------------------
Zvoni · 2022-11-14 15:41

Quote from: tt on November 14, 2022, 04:17:27 pmQuote from: Zvoni on
November 14, 2022, 04:05:49 pmQuote from: tt on November 14, 2022,
03:41:02 pmQuote from: Zvoni on November 14, 2022, 03:32:21 pmRemove
the LIMIT 5
I want to see ALL those Dates

Attached
OK, looks consistent.
Will have to check what got borked.

By any chance: Could you give me the INSERT-Statements for those
Values? (dump the table).
Otherwise i would have to write a routine to import it to my local
sqlite-database

Attached!
Thx.
Will take a look at it tomorrow.
Am currently swamped with work

----------------------------------------------------------------------
Zvoni · 2022-11-15 13:29

Oh, you gotta be kidding me.
From Fields.inc - Line 407 --> Line 513
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i property
AsDateTime: TDateTime read GetAsDateTime write SetAsDateTime;    
....function TField.GetAsDateTime: TDateTime; begin  raise
AccessError(SdateTime);end;                             Funny enough:
AsFloat throws no exception despite the same code as above being in
there
and this also didn't throw an exception
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var
d:TDateTime;Writeln(Qry.FieldByName('eventDate').AsDateTime);
//Exception!d:=Qry.FieldByName('eventDate').AsFloat;  //No
ExceptionWriteln(d);   //No Exception  So what in blazes is going on?

That said: I apologize to tt
I did get the same Exception.

----------------------------------------------------------------------
Чебурашка · 2022-11-15 13:54

Quote from: Zvoni on November 15, 2022, 02:29:51 pmOh, you gotta be
kidding me.
From Fields.inc - Line 407 --> Line 513
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i property
AsDateTime: TDateTime read GetAsDateTime write SetAsDateTime;    
....function TField.GetAsDateTime: TDateTime; begin  raise
AccessError(SdateTime);end;                             Funny enough:
AsFloat throws no exception despite the same code as above being in
there
and this also didn't throw an exception
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var
d:TDateTime;Writeln(Qry.FieldByName('eventDate').AsDateTime);
//Exception!d:=Qry.FieldByName('eventDate').AsFloat;  //No
ExceptionWriteln(d);   //No Exception  So what in blazes is going on?

That said: I apologize to tt
I did get the same Exception.

Did you try also havin the eventDate field defined as follows?

eventDate DATETIME NOT NULL

That will work for some criptic reason (feeling is that TField or
derived class guesses file type from ddl).

----------------------------------------------------------------------
Zvoni · 2022-11-15 14:20

Quote from: tt on November 15, 2022, 02:54:00 pm
Did you try also havin the eventDate field defined as follows?

eventDate DATETIME NOT NULL

That will work for some criptic reason (feeling is that TField or
derived class guesses file type from ddl).
I just did. And you're right: AsDateTime is not throwing an exception.
So basically, it's hardwired between the DataType sent from the
DataBase to the FieldType, (Which actually makes sense, since
TSQLQuery is DBMS-Agnostic)

And with SQLite you're stuck with what to use: Do it correctly, and
use REAL, INTEGER or TEXT for Date(Time), and remember it in the
Frontend-Code,
or do it wrong using DATETIME as DataType for the Column during
creation, so it gets written into the DDL-Metadata, and HOPE SQLite
never changes this.

What baffles me the most: Why is AsDateTime not recognizing that it's
basically still a Double (Float)?

----------------------------------------------------------------------
Чебурашка · 2022-11-15 15:46

Quote from: Zvoni on November 15, 2022, 03:20:56 pmAnd with SQLite
you're stuck with what to use: Do it correctly, and use REAL, INTEGER
or TEXT for Date(Time), and remember it in the Frontend-Code,
or do it wrong using DATETIME as DataType for the Column during
creation, so it gets written into the DDL-Metadata, and HOPE SQLite
never changes this.

Due to the exceptionality of SQLite in terms of dynamic typing (they
state that they are the only RDBMS with that feature), my feeling is
that we should not enforce field/param data type inside the object
itself (field/param in this case).

It should be something like

if I have a value with some storage class, I try to represent to the
caller according to how the caller asks for.

In this way if value is REAL and I call AsFloat I will get a float,
AsDateTime gives datetime.
Similarly if I have a TEXT, AsDateTime gives datetime tring to
interpret the TEXT. Or a string if I ask for AsString on that record.

I don't know if this appears senseful, but I see few possibilities of
relying on SQLite DDL information, as sometimes it is also possible to
define columns with no type at all
(https://www.sqlite.org/syntax/column-def.html - it is possible to
bypass the type-name).

Moreover, with this dynamic model, I believe is very possible to have,
for the same column, different storage types in different rows, so for
sure an approach based on the datatype fixed inside the TField object
has few chances to work.

----------------------------------------------------------------------
Zvoni · 2022-11-15 16:42

Yeah, that about sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and
override AsDateTime and the others with your own Getter/Setter‘s

EDIT: or maybe a class-helper.
Would have to chew on it.

----------------------------------------------------------------------
LacaK · 2022-11-15 18:52

Regarding to TField.AsDateTime - TField is generic ancestor for all
"specialized" field types. So it is expected that getter for
TField.AsDateTime raises exception. Because these methods are
implemented at TDateTimeField or TDateField level.

Regarding DateTime data type in SQLite3 look at "2.2. Date and Time
Datatype" on https://www.sqlite.org/datatype3.html.
You can use DATETIME as "datatype" in CREATE TABLE statement. SQLite3
will perfectly accept it and Lazarus/FPC will recognize it and will
map it to ftDateTime field type.
But you must ensure that data written into this column will satisfy
conditions from 2.2:
- so either it must be string in format "YYYY-MM-DD HH:MM:SS.SSS"
- or it must be real number in Julian day numbers

----------------------------------------------------------------------
Чебурашка · 2022-11-16 07:33

Quote from: LacaK on November 15, 2022, 07:52:24 pmRegarding DateTime
data type in SQLite3 look at "2.2. Date and Time Datatype" on
https://www.sqlite.org/datatype3.html.
You can use DATETIME as "datatype" in CREATE TABLE statement. SQLite3
will perfectly accept it and Lazarus/FPC will recognize it and will
map it to ftDateTime field type.

Thanks, I agree and it was the way I was working originally. Later I
needed to reimplement my code/db in order to support
SQLite+PostgreSQL+SQLServer+Oracle and I decided to start using
liquibase to manage the various details of the db creation step. At
this point I noticed that liquibase was mapping DATETIME datatype to
REAL and all this story started.

----------------------------------------------------------------------
Чебурашка · 2022-11-16 07:34

Quote from: Zvoni on November 15, 2022, 05:42:18 pmYeah, that about
sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and
override AsDateTime and the others with your own Getter/Setter‘s

EDIT: or maybe a class-helper.
Would have to chew on it.

I would be glad to be helpful in some way.

----------------------------------------------------------------------
Zvoni · 2022-11-16 08:49

Quote from: tt on November 16, 2022, 08:34:25 amQuote from: Zvoni on
November 15, 2022, 05:42:18 pmYeah, that about sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and
override AsDateTime and the others with your own Getter/Setter‘s

EDIT: or maybe a class-helper.
Would have to chew on it.

I would be glad to be helpful in some way.
After thinking about it, i don't think there is an "easy" solution
(nevermind that subclassing/extending TSQLQuery would be the wrong
class, since AsDateTime is a property of TField)

Bottom Line: Since you have to "support" multiple DBMS (are you using
TSQLConnector?), you'll probably have to write individual code for
each connection-type
Kind of like (Pseudo-Code)
If ConnType='SQLite' And FieldType='Float' Then ReadAsFloat

Down-Side: You'd have to know if the Field you want to read is a
"real" Float-Value (Say, Money-Balance) or a DateTime-Value

----------------------------------------------------------------------
Чебурашка · 2022-11-16 09:48

Quote from: Zvoni on November 16, 2022, 09:49:45 amBottom Line: Since
you have to "support" multiple DBMS (are you using TSQLConnector?),
you'll probably have to write individual code for each connection-type
Kind of like (Pseudo-Code)
If ConnType='SQLite' And FieldType='Float' Then ReadAsFloat

Down-Side: You'd have to know if the Field you want to read is a
"real" Float-Value (Say, Money-Balance) or a DateTime-Value

To support different RDBMS I simply create the right (subclass of)
TSQLConnection object according database type (SQLITE ->
TSQLite3Connection, MSSQL -> TMSSQLConnection, ...). Once this is done
TSQLTransation and TSQLQuery are always the same. And from my
persective also TParam and TField are always the same, despite actual
ones could be subclasses to adapt to actual connection type.

And for queries: given the SQL query, each parameter/field has a fixed
and known datatype. So not need to do "If ConnType='SQLite' And
FieldType='Float' Then ReadAsFloat".

For example, with this update query:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   query.SQL
:= 'update T0003_ConfMachines'              + ' set'              + '
 name = :name'              + ' , deleted = :deleted'              + '
, machineType = :machineType'              + ' , machineCode =
:machineCode'              + ' , machineAcquisitionType =
:machineAcquisitionType'              + ' ,
machineAcquisitionDirectory = :machineAcquisitionDirectory'          
  + ' , machineImportAtStartup = :machineImportAtStartup'            
+ ' , machineAcquisitionFileName = :machineAcquisitionFileName'      
      + ' , machineAcquisitionSerialIdentifier =
:machineAcquisitionSerialIdentifier'              + ' ,
machineHasEnergyMeasuringDevice = :machineHasEnergyMeasuringDevice'  
          + ' , listeningMode = :listeningMode'              + ' ,
listeningPort = :listeningPort'              + ' where id = :id';  
for each parameter I know what is going to be the datatype (regardless
of RDBMS), so I'll write

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
query.ParamByName('name').AsString := 'value'; 
query.ParamByName('listeningMode').AsInteger := 4;  ... 
Same applies to select. For each field I know pascal datatype so I
will simple ask for it.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i query.SQL
:= 'select eventDate from T0041_MachineLog where (refMachine =
:refMachine) and (id = :id)';query.ParamByName('refMachine').AsInteger
= 4;query.ParamByName('id').AsLargeInt= 214; query.Open(); eventDate
:= query.FieldByName('eventDate').AsDateTime; 
Basically my perspective is: in code each field has its own fixed data
type, then in each RDBMS it has to be choosen the "datatype" capable
of storing/suppyling the correspondent correct datatype. Always very
easy and working, except with SQLite that sees datatypes as something
less strict than other RDBMS.

----------------------------------------------------------------------
Zvoni · 2022-11-16 11:36

Just out of curiosity: How do you handle the different SQL-"Dialects"?
(Yeah, nothing to do with the Problem of this Thread)

----------------------------------------------------------------------
Thaddy · 2022-11-16 11:42

Just make sure you store in either Tdatetime (double) or year-month-
date-time order and time in UTC.
Handle local date/time in code. But everybody knows that.

----------------------------------------------------------------------
Чебурашка · 2022-11-16 12:17

Quote from: Zvoni on November 16, 2022, 12:36:29 pmJust out of
curiosity: How do you handle the different SQL-"Dialects"? (Yeah,
nothing to do with the Problem of this Thread)

So far I didn't encounter situations in which I need it, mostly my
programs do quite standard sql queryies and if there is some logic
involved I prefer to keep it in code, it makes me do more work in some
situations, but reduces need to dive into db specific operations (like
stored procedures). 

In case it happens it would be something like

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
(connection-database-type = SQLite) then   query.SQL := 'SQL dialect
text';   SQLLite specific processingend;if (connection-database-type =
MSSQL) then   query.SQL := 'MSSQL dialect text';   MSSQL specific
processingend 
more or less what you suggested.

----------------------------------------------------------------------
Zvoni · 2022-11-16 12:21

Quote from: Thaddy on November 16, 2022, 12:42:17 pm*snipp* or year-
month-date-time order and time in UTC.
Handle local date/time in code. But everybody knows that.
Whatever for?
SQLite does offer a modifier for localtime, IF you use the Built-in
Date(Time)-Functions of SQLite's own SQL-"Dialect".
If you INSERT/UPDATE DateTimes, this information comes from the
Frontend in 99% of cases, and then it's usually according to system-
Date/Time of the Workstation.

Of course, if you use the builtin Date/Time-Functions of SQLite itself
to Insert/Update Date/Times, then there is a hideyhole for a serious
Bug
kinda like
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
MyTable (ID, SomeDateTime) VALUES (1, DateTime('now'));  //THIS STORES
UTC!!!!INSERT INTO MyTable (ID, SomeDateTime) VALUES (2,
DateTime('now','localtime'));

----------------------------------------------------------------------
Чебурашка · 2022-11-16 12:22

Quote from: Thaddy on November 16, 2022, 12:42:17 pmJust make sure you
store in either Tdatetime (double) or year-month-date-time order and
time in UTC.
Handle local date/time in code. But everybody knows that.

I agree.
And, extending the reasonment, in the past I was putting in DB only
numeric values in SI unit, so that given a variable it was automatic
to know the measure unit. But later I changed my mind and created
something that allows me to store values in a somewhat more convenient
measure unit system, more usefult during debugging, even if the
software during it's internal calculations goes to SI. I am a maniac
about these things. If my father understood about these topics I am
sure he would tell me too.

----------------------------------------------------------------------
Zvoni · 2022-11-16 12:25

Quote from: tt on November 16, 2022, 01:17:11 pmQuote from: Zvoni on
November 16, 2022, 12:36:29 pmJust out of curiosity: How do you handle
the different SQL-"Dialects"? (Yeah, nothing to do with the Problem of
this Thread)

So far I didn't encounter situations in which I need it, mostly my
programs do quite standard sql queryies and if there is some logic
involved I prefer to keep it in code, it makes me do more work in some
situations, but reduces need to dive into db specific operations (like
stored procedures). 

In case it happens it would be something like

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
(connection-database-type = SQLite) then   query.SQL := 'SQL dialect
text';   SQLLite specific processingend;if (connection-database-type =
MSSQL) then   query.SQL := 'MSSQL dialect text';   MSSQL specific
processingend 
more or less what you suggested.
Ah.... what i thought.
I've left that approach, and worked out a "generic" way to address
that specific problem.
So no need anymore for DBMS-specific handling
If you're intested, send me a PM

----------------------------------------------------------------------
Thaddy · 2022-11-16 12:27

@Zvoni
That is what I mean, but how a programmer handles it is often plain
wrong. Of course a decent Database adheres to what I wrote.
Programmers often make the wrong assumptions about date and time
handling. That is all. Easy for you and me, but not for everyone.

----------------------------------------------------------------------
Zvoni · 2022-11-16 13:27

Quote from: Thaddy on November 16, 2022, 01:27:04 pm@Zvoni
That is what I mean, but how a programmer handles it is often plain
wrong. Of course a decent Database adheres to what I wrote.
Programmers often make the wrong assumptions about date and time
handling. That is all. Easy for you and me, but not for everyone.
Ah... OK

----------------------------------------------------------------------
LacaK · 2022-11-17 08:05

Quote from: tt on November 16, 2022, 08:33:12 amAt this point I
noticed that liquibase was mapping DATETIME datatype to REAL and all
this story started.
It is no surprise. SQLite3 in fact stores datetime as REAL (or STRING)
as stated in documentation.
So another library may recognize this data (which have real affinity)
as real numbers - these real nubers represents Julian days ...
But when you use SQLite3 built-in date and time functions like
datetime() on this "real" column you will see valid datetime values
...

----------------------------------------------------------------------
Zvoni · 2022-11-17 08:14

Quote from: LacaK on November 17, 2022, 09:05:24 amQuote from: tt on
November 16, 2022, 08:33:12 amAt this point I noticed that liquibase
was mapping DATETIME datatype to REAL and all this story started.
It is no surprise. SQLite3 in fact stores datetime as REAL (or STRING)
as stated in documentation.
So another library may recognize this data (which have real affinity)
as real numbers - these real nubers represents Julian days ...
But when you use SQLite3 built-in date and time functions like
datetime() on this "real" column you will see valid datetime values
...
We know that.
OP's problem was how TSQLQuery (and TField) determine (and set) the
Datatype from the Column-MetaData, because if the Metadata of the
Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime

----------------------------------------------------------------------
Чебурашка · 2022-11-17 08:56

Hi guys,
today I made a small test with SQLite related to datatype freedom.
Please see what I got.

Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
user@debian:~/tmp/sqlite-test$ sqlite-cli notypes.dbSQLite version
3.34.1 2021-01-20 14:10:07Enter ".help" for usage hints.sqlite> create
table NoTypesTable(  field_pk integer not null,  field_int, 
field_string,  field_datetime,  field_float);sqlite> insert into
NoTypesTable (field_pk, field_int, field_string, field_datetime,
field_float) values (1, 1, 'hello 1', '2022-11-16 14:51:36.541',
4.32);sqlite> insert into NoTypesTable (field_pk, field_int,
field_string, field_datetime, field_float) values (2, 2, 'hello 2',
'2022-11-16 14:52:36.541', 4.36);sqlite> insert into NoTypesTable
(field_pk, field_int, field_string, field_datetime, field_float)
values (3, '3', 'hello 3', 'hello 3', '4.36');sqlite> insert into
NoTypesTable (field_pk, field_int, field_string, field_datetime,
field_float) values (4, 'hello 4', 'hello 4', 'hello 4', 'hello
4');sqlite> select * from NoTypesTable;1|1|hello 1|2022-11-16
14:51:36.541|4.322|2|hello 2|2022-11-16 14:52:36.541|4.363|3|hello
3|hello 3|4.364|hello 4|hello 4|hello 4|hello 4 
Sorry if I say a stupid thing, I am old school, but I wonder if we can
still call SQLite a database. It seems to me rather a byte array
logger.

Apart my uninteresting thoughts, with this example we see that column
datatype definition is optional, and even different rows of a table
can contain unrelated datatypes.

Is the concept of datatype itself to be senseless here.

----------------------------------------------------------------------
Zvoni · 2022-11-17 09:05

Quote from: tt on November 17, 2022, 09:56:08 amSorry if I say a
stupid thing, I am old school, but I wonder if we can still call
SQLite a database. It seems to me rather a byte array logger.
Apart my uninteresting thoughts, with this example we see that column
datatype definition is optional, and even different rows of a table
can contain unrelated datatypes.
Is the concept of datatype itself to be senseless here.
It's a clear Yes and No 
SQLite offers the flexibility to use it kind of in a "typeless" mode
(or weakly typed)
OTOH, there is the STRICT-Qualifier -->
https://www.sqlite.org/stricttables.html
QuoteSQLite supports a strict typing mode, as of version 3.37.0
(2021-11-27), that is enabled separately for each table.
.....
.....
If the value cannot be losslessly converted in the specified datatype,
then an SQLITE_CONSTRAINT_DATATYPE error is raised.  Bottom Line: Use
the modifier, and you get what you're used to
BUT: As the doc's say
Quote Every column definition must specify a datatype for that column.
The freedom to specify a column without a datatype is removed.

The datatype must be one of following:

    INT
    INTEGER
    REAL
    TEXT
    BLOB
    ANY

No other datatype names are allowed, though new types might be added
in future releases of SQLite.
So, no DATETIME, VARCHAR and companions allowed (at least up until
now)

----------------------------------------------------------------------
LacaK · 2022-11-17 17:28

Quote from: Zvoni on November 17, 2022, 09:14:51 am..., because if the
Metadata of the Column is Real, he cannot use
Qry.FieldbyName('SomeDate').AsDateTime

He can use AsDateTime as long as real values stored in such column
satisfy: "..., the number of days since noon in Greenwich on November
24, 4714 B.C. according to the proleptic Gregorian calendar. "

----------------------------------------------------------------------
Zvoni · 2022-11-17 19:53

Quote from: LacaK on November 17, 2022, 06:28:45 pmQuote from: Zvoni
on November 17, 2022, 09:14:51 am..., because if the Metadata of the
Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime

He can use AsDateTime as long as real values stored in such column
satisfy: "..., the number of days since noon in Greenwich on November
24, 4714 B.C. according to the proleptic Gregorian calendar. "
No, he can‘t, as long as the returned Fieldtype is not ftDateTime.
Tested and confirmed.

----------------------------------------------------------------------
Чебурашка · 2022-11-18 13:31

If I understand correctly, this is the place where the problem arises.

In "C:\lazarus\fpc\3.2.2\source\packages\fcl-
db\src\sqlite\sqliteds.pas":

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSqliteDataset.RetrieveFieldDefs;var  ColumnCount, i,
DataSize:Integer;  AType: TFieldType;  vm: Pointer;  ColumnNames,
ColumnValues:PPAnsiChar;  ColumnStr: String;begin  FieldDefs.Clear; 
FAutoIncFieldNo := -1;  FReturnCode := sqlite_compile(FSqliteHandle,
PAnsiChar(FEffectiveSQL), nil, @vm, nil);  if FReturnCode <> SQLITE_OK
then    DatabaseError(ReturnString, Self);  sqlite_step(vm,
@ColumnCount, @ColumnValues, @ColumnNames);  //Prepare the array of
pchar2sql functions  SetLength(FGetSqlStr, ColumnCount);  // Sqlite is
typeless (allows any type in any field)  // regardless of what is in
Create Table, but returns  // exactly what is in Create Table
statement  // here is a trick to get the datatype.  // If the field
contains another type, may have problems  for i := 0 to ColumnCount -
1 do  begin    DataSize := 0;    ColumnStr :=
UpperCase(String(ColumnNames[i + ColumnCount]));    if (ColumnStr =
'INTEGER') or (ColumnStr = 'INT') then    begin      if
AutoIncrementKey and           (UpperCase(String(ColumnNames[i])) =
UpperCase(PrimaryKey)) then      begin        AType := ftAutoInc;    
  FAutoIncFieldNo := i;      end      else        AType := ftInteger; 
  end else if Pos('VARCHAR', ColumnStr) = 1 then    begin      AType
:= ftString;      DataSize := StrToIntDef(Trim(ExtractDelimited(2,
ColumnStr, ['(', ')'])), DefaultStringSize);    end else if
Pos('BOOL', ColumnStr) = 1 then    begin      AType := ftBoolean;   
end else if Pos('AUTOINC', ColumnStr) = 1 then    begin      AType :=
ftAutoInc;      if FAutoIncFieldNo = -1 then        FAutoIncFieldNo :=
i;    end else if (Pos('FLOAT', ColumnStr)=1) or (Pos('NUMERIC',
ColumnStr) = 1) then    begin      AType := ftFloat;    end else if
(ColumnStr = 'DATETIME') then    begin      AType := ftDateTime;   
end else if (ColumnStr = 'DATE') then    begin      AType := ftDate; 
 end else if (ColumnStr = 'TIME') then    begin      AType := ftTime; 
  end else if (ColumnStr = 'LARGEINT') or (ColumnStr = 'BIGINT') then 
  begin      AType := ftLargeInt;    end else if (ColumnStr = 'TEXT')
then    begin      AType := ftMemo;    end else if (ColumnStr =
'CURRENCY') then    begin      AType := ftCurrency;    end else if
(ColumnStr = 'WORD') then    begin      AType := ftWord;    end else 
 begin      AType := ftString;    end;   
FieldDefs.Add(FieldDefs.MakeNameUnique(String(ColumnNames[i])), AType,
DataSize);    //Set the pchar2sql function    case AType of     
ftString:        FGetSqlStr[i] := @Char2SQLStr;      ftMemo:       
FGetSqlStr[i] := @Memo2SQLStr;    else      FGetSqlStr[i] :=
@Num2SQLStr;    end;  end;  sqlite_finalize(vm, nil);  {  if
FReturnCode <> SQLITE_ABORT then   
 DatabaseError(ReturnString,Self);  }end; 
I was thinking that a possible way is:

1. tell the TSQLQuery that we want it not to try to reconstruct the
AType because we will force it.
2. make in a way that this info is passed to this method that will
avoid any guessing.
3. set the AtType manually from outside
4. call the As<something> we like.

or event better

1. modify TField so that AType can be forced
2. leave TSQLQuery do all its guessings
3. force for a certain Tfield to have the AType we want
4. call the As<something> we like on that TField

----------------------------------------------------------------------
Zvoni · 2022-11-18 13:47

I was thinking about that, too, this morning when i was driving to
work.

I was thinking about maybe a Class Helper for TField (because it's
TField offering "AsDateTime"), overriding the Property AsDateTime
including its Getter/Setter

But there is still something i don't understand:
https://www.freepascal.org/docs-html/fcl/db/tfield.asdatetime.html
QuoteIf the native type of the field is not a date or time value, then
an attempt will be made to convert the field value from the native
format to a TDateTime value when reading the field's content.
...
Therefor, when reading or writing a field value for a field whose
native data type is not a TDateTime-compatible value (dates or string
values), an exception may be raised.
That's what i don't understand.
In my tests, if in the SQLite-Table the Column was declared as REAL,
AsDateTime failed with the Error-Message from the beginning of the
Thread.
I could read it as a Float.

Funny thing: In my test i had two columns, one delcared REAL, the
other declared DATETIME (a DataType which officially (!!) doesn't
exist in SQLite)
I entered exactly the same floating point values in both columns

But i had no problem to
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var 
d:TDateTime;Begin  d:=Query.FieldByName('DateAsReal').AsFloat;  //No
Exception!  writeln(d); 
d:=Query.FieldByName('DateAsDateTime').AsDateTime;  //No Exception! 
writeln(d);End; both times the writeln showed the same value.

That's what threw me off.

Anyone a clou if there is a bug here?
Quotethen an attempt will be made to convert the field value from the
native format to a TDateTime

----------------------------------------------------------------------
LacaK · 2022-11-22 11:15

C:\lazarus\fpc\3.2.2\source\packages\fcl-db\src\sqlite\sqliteds.pas is
not related to TSQLite3Connection and TSQLQuery
You must look into C:\lazarus\fpc\3.2.2\source\packages\fcl-
db\src\sqldb\sqlite\sqlite3conn.pp

According to TField (I wrote it before): do not look at methods of
TField but at TDateTimeField.
For given FieldType, in our case ftDateTime is created corresponding
Field object. In our case TDateTimeField. So TField does not enters
into "game".

----------------------------------------------------------------------
Чебурашка · 2022-11-22 11:30

Quote from: LacaK on November 22, 2022, 12:15:34 pmFor given
FieldType, in our case ftDateTime is created corresponding Field
object. In our case TDateTimeField. So TField does not enters into
"game".

The problem is that in the situation described we do not obtain a
FieldType whose value is a ftDateTime.

The only way to have this is to create the table in SQLite indicating
the data type "DATETIME".
For all other cases in which is possible to write a datetime (1. REAL,
2.TEXT, 3.NO DATATYPE) the fp library does not understand that the
values we are reading are TDateTime, so if you try to call AsDateTime
in this case you get a Exeception. The reason why our library does not
understand that we are dealing with a TDateTime is that uses the
"DATETIME" in the table creation statement to understand what kind of
field type is this. In SQLite such a question is more or less without
meaning, due to it's type freedom.

Just try creating a table with a REAL field, popolate it with
TDateTime double values a read it doing AsDateTime. I will get the
Execption.

----------------------------------------------------------------------
LacaK · 2022-11-22 11:41

Quote from: tt on November 22, 2022, 12:30:26 pmThe problem is that in
the situation described we do not obtain a FieldType whose value is a
ftDateTime.
The only way to have this is to create the table in SQLite indicating
the data type "DATETIME".
Yes this is the only way - use DATETIME as column data type in create
table statement.

Quote from: tt on November 22, 2022, 12:30:26 pmJust try creating a
table with a REAL field, popolate it with TDateTime double values a
read it doing AsDateTime. I will get the Execption.
No use REAL as column data type is not a way.

----------------------------------------------------------------------
Thaddy · 2022-11-22 12:15

Yes no workarounds. Stick to that advice.

----------------------------------------------------------------------
Чебурашка · 2022-11-22 12:23

Quote from: LacaK on November 22, 2022, 12:41:08 pmYes this is the
only way - use DATETIME as column data type in create table statement.
No use REAL as column data type is not a way.

Of course your suggestions are senseful and I could do as you say.

But there are at least two situations in which these are not
applicable with the current status to the free pascal SQLite library:

1. Situations where SQLite table creation statements are managed by
software tools (in my case was liquibase) and not written directly.
For example, liquibase automatically remaps the columns DATETIME to
REAL when generating the actual SQLite creation statement. One could
force with tricks to stick to DATETIME in the generated SQL, but it is
senseful what liquibase does, since the storage class is REAL. I don't
think the liquibase guys are stupid to make this remapping
automatically.
2. Situations in which I cannot create the table creation statements
because they are pre-existing.

My idea is that the free pascal SQLite library should be modified so
that:

1. if the column is DATETIME then is read as such (and this is already
like this now)
2. if the column is of a type that allows DATETIME to be stored (REAL,
TEXT, NOTHING) allow the client of the library to tell that a value is
AsDateType even if the column seems be something else (this is what is
missing).

I will try to see If I can make it on my own and later to make it
publicly available. 

@Thaddy: we should give the same order to the ones dealing with the
situations 1 and 2.

----------------------------------------------------------------------
LacaK · 2022-11-22 18:57

Yes I understand.
At client side you can use event handler TField.OnGetText, OnSetText
https://www.freepascal.org/docs-html/fcl/db/tfield.ongettext.html to
handle content of column and prepare it for DB-aware controls (such as
TDBGrid etc.)

----------------------------------------------------------------------
Чебурашка · 2022-11-23 08:34

Quote from: LacaK on November 22, 2022, 07:57:29 pmYes I understand.
At client side you can use event handler TField.OnGetText, OnSetText
https://www.freepascal.org/docs-html/fcl/db/tfield.ongettext.html to
handle content of column and prepare it for DB-aware controls (such as
TDBGrid etc.)

This is for sure one possible way.

However it does not solve the original problem:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i given Table
= (dateTimeColumn REAL) // liquibase did that, I asked for DATETIME
but he did that, blame on it qwrite = 'insert into Table
(dateTimeColumn) values
(:dateTimeColumn)';qwrite.ParamByName('dateTimeColumn').AsDateTime :=
Today; qread = 'select dateTimeColumn from Table';value :=
qread.FiledByName('dateTimeColumn').AsDateTime; // -> Exception is
thrown  
Clients (like I was) could be confused when this occurs.
So I still think that is necessary something that solves this problem.

----------------------------------------------------------------------
Thaddy · 2022-11-23 08:48

External tools that do no adhere to the standard are not supported on
this platform, nor on the Sqlite platforms.
Drop the external tool because it is broken.
Lacak - who is the walking encyclopedia concerning databases and
Freepascal - gave you enough info on how to properly handle it.
I am not going to investigate any further, since it is the external
tool that is broken, not FPC's bindings and not Sqlite itself.

----------------------------------------------------------------------
Чебурашка · 2022-11-23 09:14

Quote from: Thaddy on November 23, 2022, 09:48:54 amExternal tools
that do no adhere to the standard are not supported on this platform,
nor on the Sqlite platforms.
Drop the external tool because it is broken.
Lacak - who is the walking encyclopedia concerning databases and
Freepascal - gave you enough info on how to properly handle it.
I am not going to investigate any further, since it is the external
tool that is broken, not FPC's bindings and not Sqlite itself.

I also trust that Lacak is a walking encyclopedia databases and
Freepascal.

Liquibase broken? Maybe. If you help me, we could try to advance these
observation to their forum. Maybe supported by previous statement.

But here things are very simple: the normal (and IMHO very senseful)
approach to the db access software based on the concept of field with
a "fixed/explicit datatype" does not work. Not because is wrong, just
because SQLite does not respect this fixed/explicit datatype model
common to all the normal dbs. They say is a feature. I do not discuss
this. Of course this requires to write a software that copes with it.
And current software does not. As I repeatedly demonstrated with many
examples, that everyone can reproduce on it own PC if necessary.

Thanks for investigating so far.

----------------------------------------------------------------------
LacaK · 2022-11-25 12:44

You can create descendant of TSQLQuery where you can override virtual
method GetFieldClass() and return own TField descendant which will
handle AsDateTime in a special way
(you can create own TSqliteFloatField = class(FloatField) ... define
there GetAsDateTime and use this new field type for REAL columns
(ftFloat))
For usage you can look at fpjsondataset.

----------------------------------------------------------------------
Чебурашка · 2022-11-29 21:03

Quote from: LacaK on November 25, 2022, 01:44:20 pmYou can create
descendant of TSQLQuery where you can override virtual method
GetFieldClass() and return own TField descendant which will handle
AsDateTime in a special way
(you can create own TSqliteFloatField = class(FloatField) ... define
there GetAsDateTime and use this new field type for REAL columns
(ftFloat))
For usage you can look at fpjsondataset.

This week I am kinda busy, but next I have some more time. This hint
will be very useful I am sure.
Thanks