Cannot save SQLITE DATETIME fields using UNIDAC - 0.0 is not a valid timestamp

[dpadureanu]

Hi,
We use latest 9.1 UNIDAC components.
When trying to store a Delphi TDateTIme field inside SQLITE using UNIDAC we receive error "0.0 is not a valid timestamp".
We use code like this

Code: Select all

dbSource.StartTransaction;
          qGetLicenses.Insert;
          ....
          qGetLicenses.FieldByName('ACQUISITION_CONTRACT_DATE').AsDateTime := tbMemLicenteDisponibile.FieldByName('ACQUISITION_CONTRACT_DATE').AsDateTime;
          .....
          qGetLicenses.Post
dbSource.Commit;          

At the DateTime field it raises the error.

Please help us to understand how UNIDAC handles TDateTime fields under SQLITE.
Until now we used FireDac and we didn't encountered any problem with TDateTime fields formatting on SQLite.

We found the 2 params
workDatabase.SpecificOptions.Values['DateFormat'] := 'YYYY.MM.DD';
workDatabase.SpecificOptions.Values['TimeFormat'] := 'HH:MM:SS';

but when we used like here the date is always 0.
Please help!

[frickler]

Problem is: SQLite doesn't really have a DATETIME field type. One must either use TEXT or REAL. In the source code of "LiteClassesUni.pas" you'll see that dtDateTime and dtSQLTimeStamp are internally mapped to SQLite's TEXT field type. The two parameters you mentioned should match the actually used format of dates und times in the database. Use a third party tool like the free "SQLite expert" to look, which format is actually used.

[dpadureanu]

OK, this I know, but why UNIDAC doesn't recognize by himself the formatting string when assigning TDateTime fields, and raises an format error at fields values assignment? It happens only for Delphi TimeStamp Fields. TDatetime fields that does not include time works OK.

[MaximG]

Thank you for the information. Please send us a sample project where the issue can be reproduced, including the DDL script for creating database objects used in the project. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html