Copying database with TFDBatchMove: How to set conversion from TIMESTAMP to INTEGER?

Question

My program should be able to work in offline-mode, without a connection to the remote MySQL database. For that, I want to create an option to download a specific table to local SQLite database and to reupload it back to the server when the work is done. I try to use the TFDBatchMove component for this task.

The solution works fine for all but one field: MySQL TIMESTAMP. Currently local DB uses INTEGER for storing the timestamp. When I copy data from SQLite to MySQL, i get an error. On the inverse operation timestamp is corrupted.

I suppose I need some type of data mapping, but I haven't found how to set it up. Can someone help?

Error message: "Project VRDB.exe raised exception class EVariantError with message 'Invalid variant type conversion'". "Project VRDB.exe raised exception class EDatabaseError with message 'Invalid value for field 'TimeStamp''".

My test code:

procedure TForm1.ButtonDownloadDBClick(Sender: TObject);
var queryFrom, queryTo: TFDQuery;
    FDBatchMove: TFDBatchMove;
begin
  queryFrom := TFDQuery.Create(nil);
  queryTo := TFDQuery.Create(nil);
  FDBatchMove := TFDBatchMove.Create(nil);

  with FDBatchMove do begin
    // Use "always insert record" mode
    Mode := dmAlwaysInsert;
    // Erase destination dataset before moving data
    Options := [poClearDest];
  end;

  try
    queryFrom.Connection := FDConnection2; //MySQL
    queryTo.Connection := FDConnection1; //SQLite

    queryFrom.SQL.Clear;
    queryFrom.SQL.Add('SELECT * FROM Clients');
    queryFrom.Open();
    queryFrom.Prepare;
    queryFrom.Close;

    queryTo.SQL.Clear;
    queryTo.SQL.Add('SELECT * FROM Clients');
    queryTo.Open();
    queryTo.Prepare;
    queryTo.Close;

    with TFDBatchMoveDataSetReader.Create(FDBatchMove) do begin
      DataSet := queryFrom;
      Optimise := False;
    end;
    with TFDBatchMoveDataSetWriter.Create(FDBatchMove) do begin
      DataSet := queryTo;
      Optimise := False;
    end;
    FDBatchMove.Execute;

  finally
    queryFrom.Close;
    queryFrom.Destroy;
    queryTo.Close;
    queryTo.Destroy;
    FDBatchMove.Destroy;
  end;
end;

One option is to modify the `From` query to do the conversion for the field in question. — Brian, Sep 12 '20 at 14:03
Wierd, but if I try `SELECT ID,Name,..,UNIX_TIMESTAMP(TimeStamp) FROM Clients;` BatchMove returns `Could not convert variant of type (UnicodeString) into type (Integer)` error. — Alexander, Sep 12 '20 at 14:34
Couldn't get it to work. `FDBatchMove.Mappings.Add` is not helping. Either I do something very wrong or functions in SELECT used as data source for BatchMove are not supported. May someone look into this? — Alexander, Sep 12 '20 at 14:48

score 1 · Answer 1 · edited Sep 12 '20 at 19:09

So it seems that I have resolved the issue. This is how I done it. I assume that TimeStamp field is TIMESTAMP in MySQL and INTEGER in SQLite, and DateTimeFormat = String.

Many thanks to Brian for the idea.

You should modify the queryFrom SQL. For MySQL -> SQLite, do this:

queryFrom.SQL.Add('SELECT ID,Phone,Name,<...>,UNIX_TIMESTAMP(TimeStamp) FROM Clients');

This will convert the Date fieldtype to the integer timecode.

For SQLite -> MySQL, do this:

queryFrom.SQL.Add('SELECT ID,Phone,Name,<...>,datetime(TimeStamp, "unixepoch") FROM Clients');

Important note: datetime returns string. TFDBatchMove will use the default Delphi StrToDateTime for conversion. If you have changed date format in your app, it will fail. So you either want to revert it before execution or format TimeStamp manually:

queryFrom.SQL.Add('SELECT ID,Phone,Name,<...>,strftime("%d.%m.%Y %H:%M:%S", TimeStamp, "unixepoch") FROM Clients');

Another important note: for some reason, I couldn't get it to work with Mappings. If someone knows how to map columns with functions properly, please reply to this post, I will add the recipe.

There's also an error in my test code. As you see, I want to keep my ID values, i.e. Primary Key values, exactly as they were. If you want it, you must specify poIdentityInsert in BatchMove Options:

FDBatchMove.Options := [poIdentityInsert, <OtherOptions>];

In other case, you will get a misterious Fetch command fetched [0] instead of [1] record. error.

Copying database with TFDBatchMove: How to set conversion from TIMESTAMP to INTEGER?

1 Answers1