Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate

[egsuh]

I'm expanding previous discussion.
https://forum.lazarus.freepascal.org/index.php/topic,60674.0.html

I'm testing TSQLQuery.ApplyUpdates, but the content of TSQLQuery is copied from TBufDataSet (or any other dataset).

To explain, with direct opening of  a TSQLQuery like:

      qr1.SQL.Text := 'select * from testtable';   // There is a primary key.
      qr1.Open;

I can do editing (insert, delete, and modify records). In this case, to save the changes, I only have to call

      qr1.ApplyUpdates;

without specifying UpdateSQL, DeleteSQL and InsertSQL (qr1.UsePrimaryKeyAsKey = true).


Now I'd like to download a set of records from webserver, and edit it on client PC, and then save it back.

So I made a simple test application having both TSQLQuery(qr1) and TBufDataSet (bds1).  First I open qr1, copy it to bds1, edit with bds1, and then copy bds1 back to qr1. And I'd like to check whether I can call qr1.ApplyUpdates. 

Code: Pascal  [Select][+][-]

1.       qr1.SQL.text := 'select * from testtable';
2.       qr1.Open;
3.       qr1.SaveToStream(AMemoryStream);
4.       // As you know, AMemoryStream must sail over Internet in real world
5.       bds1.LoadFromStream(AMemoryStream);
6.       qr1.Close;
7.  
8.       //  database editings are done here --- datasource is linked to bds1.
9.       //  After editing,
10.  
11.       bds1.SaveToStream(AMemoryStream);
12.       // Again, AMemoryStream must sail over Internet in reality
13.       qr1.LoadFromStream(AMemoryStream);
14.       qr1.ApplyUpdates;
15.  

But at the last ApplyUpdates, there occur exceptions --- it says UpdateSQL or DeleteSQL's fields are not defined, etc.  Should I compose all the SQLs or is there any simpler way?

[af0815]

Code: Pascal  [Select][+][-]

1.       qr1.SQL.text := 'select * from testtable';
2.       qr1.Open;
3.       qr1.SaveToStream(AMemoryStream);
4. .....
5.       qr1.LoadFromStream(AMemoryStream);
6.       qr1.ApplyUpdates;
7.  

With the SaveToStream you serialize the object to extrnal, but the internal states from a TSQLQuery, with the LoadFromStram you get back the states of the TBufDataSet - not the TSQLQuery.
The query can now only rise a exception, because it doesn't know about the used statement. It looks like the internal guessing of SQL-statments is cleared with the loading of the TBufDataset.

[egsuh]

It looks like the internal guessing of SQL-statments is cleared with the loading of the TBufDataset.

SavetoStream seems to save the same content, becuase this is a method of TCustomBufDataSet, which is ancestor of both TSQLQuery and TBufDataset, and I cannot find any overriding methods.

But, when TSQLQuery is edited, the changes seem to be stored in a buffer, and ApplyUpdates use those data. So my approach should not work.

[egsuh]

I found that my approach (using ApplyUpdates with "copied" TSQLDataSet) works very well, at a small sized test.

Insertions do not need any further definitions.

Deletions can be updated with defining following :
     qr1.DeleteSQL.Text := 'delete from testtable where code=:OLD_CODE';

Without this statement, there were run-time error.

Updates are done, just by setting
      qr1.UpdateMode := upWhereChanged;

Should this work? It does, but I'm really not sure even after seeing.