FireDAC: Saving Time to SQLite DB

Question

I'm new to FireDAC and have a problem. I want to read and write a SQLite database with FireDAC in Delphi XE7. Most what I tried worked, but I have a problem with saving a TTime to the SQLite DB.

This works:

FDQuery1.Fields[0].AsString := EdName.Text;

This doesn't:

FDQuery1.Fields[1].Value := TeTime.Time; // TeTime = TTimeEdit (FMX)

Why? The first field is a "REAL" and the second one is a "NUMERIC" as explained here: https://www.sqlite.org/datatype3.html

Thanks, LuMa

Answer 1

I don't have the same environment to test, so the results might differ for you, but in Delphi XE3 with an older version of AnyDAC I ran this simple test:

ADQuery.Close;
ADQuery.SQL.Text := 'CREATE TABLE MyTable (Col NUMERIC)';
ADQuery.ExecSQL;

ADQuery.Close;
ADQuery.SQL.Text := 'INSERT INTO MyTable (Col) VALUES (:Val)';
ADQuery.Params[0].Value := TTime(EncodeTime(1, 2, 3, 4));
ADQuery.ExecSQL;

ADQuery.Close;
ADQuery.SQL.Text := 'SELECT Col FROM MyTable';
ADQuery.Open;

The result was that the fetched table field was of type ftLargeint and its value was 0. So you've just lost your value with this code. FireDAC fortunately offers you a better approach. You can create a table with a custom TIME field type like this:

CREATE TABLE MyTable (Col TIME)

FireDAC internally maps such data type to a dtTime field type (it is described in this topic), so you can then natively access such field as being a real time field, like e.g.:

ADQuery.Close;
ADQuery.SQL.Text := 'CREATE TABLE MyTable (Col TIME)';
ADQuery.ExecSQL;

ADQuery.Close;
ADQuery.SQL.Text := 'INSERT INTO MyTable (Col) VALUES (:Val)';
ADQuery.Params[0].AsTime := TTime(EncodeTime(1, 2, 3, 4));
ADQuery.ExecSQL;

ADQuery.Close;
ADQuery.SQL.Text := 'SELECT Col FROM MyTable';
ADQuery.Open;

ShowMessage(FormatDateTime('hh:nn:ss.zzz', ADQuery.Fields[0].AsDateTime));