Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2022-09-28 10:25

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
qr1.SQL.text := 'select * from testtable';       qr1.Open;      
qr1.SaveToStream(AMemoryStream);       // As you know, AMemoryStream
must sail over Internet in real world     
bds1.LoadFromStream(AMemoryStream);       qr1.Close;        //
 database editings are done here --- datasource is linked to bds1.   
  //  After editing,        bds1.SaveToStream(AMemoryStream);       //
Again, AMemoryStream must sail over Internet in reality     
qr1.LoadFromStream(AMemoryStream);       qr1.ApplyUpdates;  

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 · 2022-09-28 11:18

Quote from: egsuh on September 28, 2022, 12:25:18 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
qr1.SQL.text := 'select * from testtable';       qr1.Open;      
qr1.SaveToStream(AMemoryStream); .....     
qr1.LoadFromStream(AMemoryStream);       qr1.ApplyUpdates;  
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 · 2022-09-29 03:22

Quote 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 · 2022-09-29 03:41

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.Upda]"]>Blockedde := upWhereChanged;

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