Again on SQLite and date times.

[Чебурашка]

Hello,

I have a table like this in SQlite

Code: Pascal  [Select][+][-]

1. CREATE TABLE ExampleT
2. (
3.   id integer not null,
4.   fieldDate datetime
5. );
6.  

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][+][-]

1. CREATE TABLE ExampleT
2. (
3.   id integer not null,
4.   fieldDate NUMERIC
5. );
6.  

(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]

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"

[Чебурашка]

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"

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][+][-]

1. SQLite version 3.34.1 2021-01-20 14:10:07
2. Enter ".help" for usage hints.
3. sqlite> .schema 'T0041%'
4. 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));
5. CREATE INDEX T0041_MachineLog_FKI ON T0041_MachineLog(refMachine);
6.  

The field in question now is:

Code: SQL  [Select][+][-]

1. eventDate DOUBLE NOT NULL,
2.  

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][+][-]

1. sqlite> SELECT * FROM T0041_MachineLog LIMIT 5;
2. refMachine|id|deleted|refImport|eventDate|eventType|eventVersion|eventDataType|eventData
3. 1|1|0|1|2455615.89571759|1|1|0|
4. 1|2|0|1|2455615.89572917|11|1|0|
5. 1|3|0|1|2455615.89574074|11|1|0|
6. 1|4|0|1|2455615.89574074|11|1|0|
7. 1|5|0|1|2455615.89586806|2|1|0|
8.  

And here are the errors related to the reading of this field (and all others that should be read AsDateTime):

Code: Text  [Select][+][-]

1. Localization system auto-analysis disabled. To set the compiler directive DEBUG_LOCALIZATION and execute the program
2. Processing machine "New machine1" with code "" ...
3. Found 37 source prodlog files
4. Processed 37 new files and 0 changed filed
5. Unexpected error while LoadEvents on eventId 1, eventType 1/version 1 form machine 1 (exception: Invalid type conversion to TDateTime in field eventDate)
6. Unexpected error while ProcessEvents (exception: Invalid type conversion to TDateTime in field eventDate)
7. Unexpected error while recaching data of machine 1 (exception: Invalid type conversion to TDateTime in field eventDate)
8. Unexpected error while compiling daily summaries of machine 1 (exception: Invalid type conversion to TDateTime in field eventDate)
9. Processing machines completed
10. Unexpected error while loading products in interval (exception: Invalid type conversion to TDateTime in field eventDate)
11. Unexpected error while loading products in interval (exception: Invalid type conversion to TDateTime in field eventDate)
12.  

Any ideas at this point?

[Zvoni]

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][+][-]

1. SQLite version 3.34.1 2021-01-20 14:10:07
2. Enter ".help" for usage hints.
3. sqlite> .schema 'T0041%'
4. 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));
5. CREATE INDEX T0041_MachineLog_FKI ON T0041_MachineLog(refMachine);
6.  

The field in question now is:

Code: SQL  [Select][+][-]

1. eventDate DOUBLE NOT NULL,
2.  

Close. Use REAL instead of Double (but SQLite will map Double to Real in 99.9999% of cases)

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][+][-]

1. sqlite> SELECT * FROM T0041_MachineLog LIMIT 5;
2. refMachine|id|deleted|refImport|eventDate|eventType|eventVersion|eventDataType|eventData
3. 1|1|0|1|2455615.89571759|1|1|0|
4. 1|2|0|1|2455615.89572917|11|1|0|
5. 1|3|0|1|2455615.89574074|11|1|0|
6. 1|4|0|1|2455615.89574074|11|1|0|
7. 1|5|0|1|2455615.89586806|2|1|0|
8.  

Remove the LIMIT 5
I want to see ALL those Dates

[Чебурашка]

Remove the LIMIT 5
I want to see ALL those Dates

Attached

[Чебурашка]

Close. 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]

Remove 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

[Чебурашка]

Remove 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]

Remove 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]

Oh, you gotta be kidding me.
From Fields.inc - Line 407 --> Line 513

Code: Pascal  [Select][+][-]

1. property AsDateTime: TDateTime read GetAsDateTime write SetAsDateTime;    
2. .
3. .
4. .
5. .
6. function TField.GetAsDateTime: TDateTime;
7.  
8. begin
9.   raise AccessError(SdateTime);
10. end;                            
11.  

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][+][-]

1. Var d:TDateTime;
2. Writeln(Qry.FieldByName('eventDate').AsDateTime); //Exception!
3. d:=Qry.FieldByName('eventDate').AsFloat;  //No Exception
4. Writeln(d);   //No Exception
5.  

So what in blazes is going on?

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

[Чебурашка]

Oh, you gotta be kidding me.
From Fields.inc - Line 407 --> Line 513

Code: Pascal  [Select][+][-]

1. property AsDateTime: TDateTime read GetAsDateTime write SetAsDateTime;    
2. .
3. .
4. .
5. .
6. function TField.GetAsDateTime: TDateTime;
7.  
8. begin
9.   raise AccessError(SdateTime);
10. end;                            
11.  

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][+][-]

1. Var d:TDateTime;
2. Writeln(Qry.FieldByName('eventDate').AsDateTime); //Exception!
3. d:=Qry.FieldByName('eventDate').AsFloat;  //No Exception
4. Writeln(d);   //No Exception
5.  

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]

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)?

[Чебурашка]

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.

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]

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]

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