Can changes made to a TFDMemTable cloned from a TFDQuery be sent back to the database?

Question

With Delphi 10 Seattle I am attempting to clone a TFDQuery with a TFDMemTable and persist the changes made in the TFDMemTable through to the database.

The memtable changes appear in the query but don't go all the way to the DB. If I modify values in the query directly they do get saved in the database.

var
  query : TFDQuery;
  memtable : TFDMemTable;
begin
  { run TFDQuery }
  query := TFDQuery.Create(nil);
  query.SQL.Text := 'select * from employee';
  query.Open;

  { clone query into memtable }
  memtable := TFDMemTable.Create(nil);
  memtable.CloneCursor(query);

  { go to the same record in query and memtable }
  memtable.First;
  query.First;
  Assert(memtable.Fields[0].Value = query.Fields[0].Value);

  { edit the record in the memtable from ABC to XYZ }
  Assert(memtable['SomeField'] = 'ABC');
  memtable.Edit;
  memtable['SomeField'] := 'XYZ';
  memtable.Post;

  { verify record also changed in query }
  Assert(query['SomeField'] = 'XYZ');

  { has the change gone through to the database? }
  query.Close;
  query.Open;
  query.First;
  Assert(query['SomeField'] = 'XYZ');  // assertion fails, value is still ABC

Answer 1

Interesting q. I drew a blank trying to d this by using MergeDataSet.

However, then I wondered if I wasn't trying to make too much of a meal of it. Rather to my surprise, the following works find for me, and changes made in the FDMemTable are persisted back to the database (MS Sql Server 2014 using the authors table in the MS pubs database, Delphi Seattle):

procedure TForm1.btnCopyToMemTableClick(Sender: TObject);
begin
  FDMemTable1.CloneCursor(FDQuery1);
end;

procedure TForm1.btnSaveBackClick(Sender: TObject);
var
  Errors : Integer;
begin
  Errors := FDQuery1.ApplyUpdates;
  FDQuery1.Close;
  //  need to close FDMemTable1 before re-opening FDQuery1, otherwise the
  //  call to FDQuery1 provokes a complaint about a duplicated column.
  FDMemTable1.Close;

  FDQuery1.Open;
  FDMemTable1.CloneCursor(FDQuery1);
end;

From this, it seems that your code is lacking a query.ApplyUpdates(0) before queryis closed, and a call to memtable.close before attempting to re-open query to avoid the error noted in the code.

So in this case, KISS seems to apply, but I'd love to know whether there is a way to achieve the same result using MergeDataSet.

Btw, the following is a minor variation of your code to adapt it to my testbed:

var
  query : TFDQuery;
  memtable : TFDMemTable;
begin
  //  Using the MS pubs demo database
  query := FDQuery1;
  query.SQL.Text := 'select * from authors';
  query.CachedUpdates := True;
  query.Open;

  { clone query into memtable }
  memtable := FDMemTable1;
  memtable.CloneCursor(query);

  { go to the same record in query and memtable }
  memtable.First;
  query.First;
  Assert(memtable.Fields[0].Value = query.Fields[0].Value);

  { edit the record in the memtable from ABC to XYZ }
  //Assert(memtable[''] = 'ABC');
  memtable.Edit;
  memtable['Phone'] := '666';
  memtable.Post;

  { verify record also changed in query }
  Assert(query['Phone'] = '666');

  //  Added
  query.ApplyUpdates(0);
  { has the change gone through to the database? }
  query.Close;

  //  Added
  //  need to close memtable before re-opening query, otherwise the
  //  call to query provokes a complaint about a duplicated column.
  memtable.Close;

  query.Open;
  query.First;
  Assert(query['Phone'] = '666');  // assertion succeeds