Sqlite auto timestamp - insert then display on DB aware grid

Question

I have some experince with MySql, and am moving to Sqlite for the first time.

The Sqlite documentation for data types, section 1.2 states that

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values

I would prefer an auto timestamp, but will live with having to pass it in every time if it will get my code working.

Followinf this question, I have declared my field as

`time_stamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

However, it is not displaying anything on a DB ware grid.

I added an OnDrawCell() and thought that this might help:

   var cellText : String;
       cellValue : String;
       dateTime : TDateTime;
begin
   if ARow = 0 then
      Exit;

      cellValue := myGrid.Cells[ACol, ARow];

   case ACol of
      0: ; // invisibe column, do nothing
      1: cellText := cellValue;
      2: begin
            dateTime := StrToDateTime(cellValue);
            cellText := DateTimeToStr(dateTime);
         end;
      3: cellText := cellValue;
      4: cellText := cellValue;
   end;

   myGrid.Canvas.FillRect(Rect);
   myGrid.Canvas.TextOut(Rect.Left + 2, Rect.Top + 2, cellText);

where column 2 is my timestamp, but is apparently empty.

So, my question is, can anyone correct this code snippet, or show me a code example of how to declare an Sqlite column which defaults to the current timestamp and how to display that in a DB aware grid? I am happy enough to store a Unix timestamp, if that would help.

Btw, I am using XE7, FireDac with a TMS TAdvDbGrid.

[Update] As mentioned it a comment thread below (and as perhaps ought to have been mentioned originally), in this case I am generating some dummy data for testing porpoises using a TDateTime and IncSecond(startTime, delay * i). So, effectively, I am writing a TDateTime to that field, then I close/open the datasource and all other fields of the new row are shown, but not that one.

But, that actually digresses from my original, "please provide an example" question and turns it into a "please fix my code" question. An answer to either will make me very happy.

FireDAC uses [`mapping to pseudo types`](http://docwiki.embarcadero.com/RADStudio/XE6/en/Using_SQLite_with_FireDAC#Mapping_SQLite_to_FireDAC_Data_Types). So define your field to be `DATETIME`. The `TIMESTAMP` data type FireDAC maps to the `dtDateTimeStamp` data type (hence you probably cannot get proper output). — TLama, Mar 21 '15 at 21:00
@TLama Thanks (+1) but that is confusing to a bear of very little brain. Can you post a concrete answer? — Mawg says reinstate Monica, Mar 21 '15 at 21:06

score 1 · Accepted Answer · answered Mar 22 '15 at 19:32

1

You are looking in the wrong place, your problem is in the dataset. It is a generic problem for all datasets that get their data from an external database.

Your query/dataset has a copy of the data in your database. It gets that copy from the database when it is opened or when you use it to update/insert records into the database. If the data in the database is changed some other way, your dataset will not have those changes until the changed record(s) are re-read. This applies to you, because the timestamp value is being set in database, not through the dataset. This can be accomplished by closing then opening the dataset.

With FireDAC, try setting the query's UpdateOptions .RefreshMode := rmAll. It has worked for me when there is a single table in the query, i.e. no joins.

answered Mar 22 '15 at 19:32

crefird

1,590
11
17

So the grid can display a field of type `dtDateTimeStamp` properly ? Because that's how the column of type `TIMESTAMP` should be mapped according to help. But you are right, the tuple must be re-fetched to see the value generated by DBMS. [don't have Delphi by hand] – TLama Mar 22 '15 at 20:42
1

I tried you your table in SQLite with FireDAC and DBGrid. The timestamp field displayed fine. I used DBGrid instead of DBAdvGrid because i could not insert records when using DBAdvGrid. – crefird Mar 22 '15 at 20:49
1

When I created your table and used SQLiteSpy to inspect it, timestamp column's datatype showed as datetime. – crefird Mar 22 '15 at 20:51
1

Thank you, but It's not my table :) What you observe can be explained as that the field becomes `TEXT` with the format of `YYYY-MM-DD HH:MM:SS` (as desribed [`here`](https://www.sqlite.org/lang_createtable.html)). Still, I would prefer defining the column to be `DATETIME` and filling it manually with every insert (no need for extra re-fetch). But it's a matter of taste dependent on the actual application. – TLama Mar 22 '15 at 21:12
1

I've just checked the `TADParam` implementation and it's smart enough so you can access a field of type `dtDateTimeStamp` as being `ftDateTime` (through the `AsDateTime` access). So even defining the column as `TIMESTAMP` which is mapped to the field type `dtDateTimeStamp` does not limit you to treat it as a real date time field. – TLama Mar 22 '15 at 21:15
Well, we agree that it's not a close/open problem (which I do anyway), and it works for you guys, which is great. Which narrows it down to the nature to the data which I am inserting. In real life, I won't write that file and will let the database take care of it with the default. However (and I will update the question to reflect this, as I ought ot have done originally), I am generating some dummy data for testing porpoises using a `TDateTime` and `IncSecond(startTime, delay * i)`. – Mawg says reinstate Monica Mar 23 '15 at 06:40
So, effectively, I am writing a TDateTime to that field, then I close/open the datasource and all other fields of the new row are shown, but not that one. – Mawg says reinstate Monica Mar 23 '15 at 06:40

Sqlite auto timestamp - insert then display on DB aware grid

1 Answers1