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.

Answer 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.