Jump to content
Sign in to follow this  
Alberto Fornés

Firedac - Sqlite - DateTime field

Recommended Posts

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:

 

sqlitedatetime.thumb.png.f69229d8032dc3ce3b9d2adfde39756b.png

 

 but using SQLiteStudio 3.2.1 , I can see the data:

 

tabla_fichadas.thumb.png.4c309d4f822717c731facb1e297ec26a.png

 

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

Share this post


Link to post

    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.

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post

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.

  • Thanks 1

Share this post


Link to post

Hi...:classic_cool:

Quote

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

....please use every time SQL Parameters! :classic_huh:

 

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) :classic_cool:

Edited by haentschman
  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×