Firedac - Sqlite - DateTime field

[Alberto Fornés 22]

I have created a table with firedac using sqlite as database, using this script: 

CREATE TABLE FICHADAS (IDFICHADA INTEGER PRIMARY KEY AUTOINCREMENT,IDJORNADA INTEGER,IDEMPLEADO INTEGER,DESDE DATETIME ,HASTA DATETIME ,MINORMAL SMALLINT,MINEXTRA SMALLINT)

Therefore I define the DESDE and HASTA fields as datetime, I can insert into the table as datetime fields and when I put a TFDTable component bound to the table it recognizes the fields as TDateTimeField, but when I try to visualize it in a TDBGrid it does not show anything in those two fields and nothing appears if I run the select on the connection editor:

 

 

 but using SQLiteStudio 3.2.1 , I can see the data:

 

 

 I guess I need to configure something in Firedac to work with these fields, but I can't know what, any suggestions? Thanks.

[Dmitry Arefiev 101]

You have to read:

http://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC#SQLite_Data_Types
http://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

[Dmitry Arefiev 101]

You have to read:

http://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC#SQLite_Data_Types
http://docwiki.embarcadero.com/RADStudio/Sydney/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

[Dmitry Arefiev 101]

Sorry for spamming, dont know how it happened ...

[Alberto Fornés 22]

    Hello Dmitry, thanks for your response, but changing DateTimeFormat driver parameter to Binary or DateTime the grid don't show me this two datetime fields data, also I tried this select: SELECT FICHADAS.IDFICHADA,FICHADAS.IDEMPLEADO,FICHADAS.DESDE AS "FDESDE::DATETIME",FICHADAS.HASTA AS "FHASTA::DATETIME" , but get the same behaivor.

[Dmitry Arefiev 101]

Most right way will be to create an issue at quality.embarcadero.com. And attach the DB file with the words explaining what you did.

I would expect, dates were inserted using format not supported by FireDAC.

[Arnaud Bouchez 407]

IIRC, SQLite3 has no native date/time format, but it can understand ISO 8601 text fields and Unix Epoch integer fields.

[Alberto Fornés 22]

1 hour ago, Dmitry Arefiev said:

Most right way will be to create an issue at quality.embarcadero.com. And attach the DB file with the words explaining what you did.

I would expect, dates were inserted using format not supported by FireDAC.

  You are right (maybe you know something about FireDac 😀), previously I used this format to insert values:

 

UPDATE FICHADAS SET HASTA = ' + QuotedStr(FormatDateTime('dd-mm-yyyy HH:nn',FDMemTbCSV.FieldByName('Fecha').AsDateTime))

 Now it works with this (using DateTimeFormat driver parameter to DateTime):

 

'UPDATE FICHADAS SET HASTA = ' + QuotedStr(FormatDateTime('yyyy-mm-dd HH:nn',FDMemTbCSV.FieldByName('Fecha').AsDateTime))

what made me doubt was seeing that with SQLiteStudio 3.2.1 it was possible to see the data (there was no error in the update, I imagine because it is finally a double). Thank you.

[Arnaud Bouchez 407]

As I wrote it never stores a double - SQLite3 doesn't support TDateTime double which is Ole/Windows specific.

In your code, text will be stored.

It is because that SQliteStudio display what is stored, i.e. text, trying several date/time layouts.

It seems that FireDac expects ISO-8601 encoding - just as SQLite3. And don't forget to set the seconds - even :00.

[haentschman 92]

Hi...

Quote

UPDATE FICHADAS SET HASTA = ' + QuotedStr(FormatDateTime('dd-mm-yyyy HH:nn',FDMemTbCSV.FieldByName('Fecha').AsDateTime))

....please use every time SQL Parameters!

 

Qry.SQL.Text := 'select Blubb from Bla where Date = :BDA';
Qry.ParamByName('BDA').AsDateTime := Now;

See SQL Injection:

https://en.wikipedia.org/wiki/SQL_injection

https://de.wikipedia.org/wiki/SQL-Injection

 

Then you will see that the DateTime problems will disappear. (Field in DataBase = DateTime)

Edited September 10, 2020 by haentschman