FireDAC BatchMove from MemoryTable

Question

My incoming data is loaded in a TFDMemTable, (the reader). The writer is a TFDQuery.

Incoming data should be inserted if not in the target, otherwise updated. Matches are based on the UUID field.

I am unable to properly define that the UUID field is the key.

Here is a code example - does not work. FBatchMove.Execute fails because cannot it find any key fields.

procedure TSubDB.FindDestRecord(ASender: TObject; var AFound: Boolean);
var
  aSrc: TBytes;
begin
  SetLength(aSrc, 16);
  aSrc := FReader.DataSet.FieldByName('UUID').AsBytes;
  AFound := FWriter.DataSet.Locate('UUID', aSrc, []);
end;

function TSubDB.LoadDB(const aFilename: string): boolean;
var
  FQry: TFDQuery;
  FBatchMove: TFDBatchMove;
  FReader: TFDBatchMoveDataSetReader;
  FWriter: TFDBatchMoveDataSetWriter;
  FMemTable: TFDMemTable;
begin
  FQry := TFDQuery.Create(nil);
  FQry.Connection := dmFB.myDB;
  FQry.FetchOptions.AssignedValues := [evItems];
  FQry.FetchOptions.Items := [fiBlobs, fiDetails];
  FBatchMove := TFDBatchMove.Create(nil);
  FBatchMove.Analyze := [taDelimSep, taHeader, taFields];
  FReader := TFDBatchMoveDataSetReader.Create(FBatchMove);
  FWriter := TFDBatchMoveDataSetWriter.Create(FBatchMove);
  FMemTable := TFDMemTable.Create(nil);
  try
    FMemTable.LoadFromFile(aFileName, sfBinary);
    //Not sure how to make the BatchMove recognize that UUID is the key for OnFindDestRecord
    FMemTable.IndexFieldNames := 'UUID';
    with FMemTable.Indexes.Add do
    begin
      Name :='idxUUID';
      Fields := 'UUID';
      Active := true;
    end;
    FMemTable.IndexName := 'idxUUID';
    FMemTable.IndexesActive := true;
    FMemTable.FieldByName('UUID').ProviderFlags := FMemTable.FieldByName('UUID').ProviderFlags + [pfInKey];
    FReader.DataSet := FMemTable;
    FQry.SQL.Text := 'select * from test';
    FWriter.DataSet := FQry;
    FBatchMove.OnFindDestRecord := FindDestRecord;
    FBatchMove.Mode := dmAppendUpdate;
    //None of the above seems to keep the pfInKey in the UUID field's ProviderFlags
    FBatchMove.Execute;
    FQry.Open;
    FQry.Close;
  finally
    FMemTable.Free;
    FWriter.Free;
    FReader.Free;
    FBatchMove.Free;
    FQry.Free;
  end;
end;

I would really appreciate a working example of batch move (where the target has data, so the batch move mode is dmAppendUpdate).

Answer 1

The key here is that the writer needs to be a TFDBatchMoveSQLWriter with a TableName set. This way the destination had the primary key defined and it is then used to decide whether to insert or update.

function TSubDB.LoadDB(const aFilename: string): boolean;
var
  FQry: TFDQuery;
  FBatchMove: TFDBatchMove;
  FReader: TFDBatchMoveDataSetReader;
  FWriter: TFDBatchMoveSQLWriter;
  FMemTable: TFDMemTable;
begin
  FQry := TFDQuery.Create(nil);
  FQry.Connection := dmFB.myDB;
  FQry.FetchOptions.AssignedValues := [evItems];
  FQry.FetchOptions.Items := [fiBlobs, fiDetails];
  FBatchMove := TFDBatchMove.Create(nil);
  FBatchMove.Analyze := [taDelimSep, taHeader, taFields];
  FReader := TFDBatchMoveDataSetReader.Create(FBatchMove);
  FWriter := TFDBatchMoveSQLWriter.Create(FBatchMove);
  FMemTable := TFDMemTable.Create(nil);
  try
    FMemTable.LoadFromFile(aFileName, sfBinary);
    FReader.DataSet := FMemTable;
    FQry.SQL.Text := 'select * from test';
    FWriter.Connection := dmFB.myDB;
    FWriter.TableName := 'test';
    FBatchMove.Mode := dmAppendUpdate;
    FBatchMove.Execute;
    FQry.Open;
    FQry.Close;
  finally
    FMemTable.Free;
    FWriter.Free;
    FReader.Free;
    FBatchMove.Free;
    FQry.Free;
  end;
end;