Recent

Author Topic: Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate  (Read 348 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1019
Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate
« on: September 28, 2022, 12:25:18 pm »
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

  • Hero Member
  • *****
  • Posts: 1036
Re: Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate
« Reply #1 on: September 28, 2022, 01:18:31 pm »
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.


regards
Andreas

egsuh

  • Hero Member
  • *****
  • Posts: 1019
Re: Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate
« Reply #2 on: September 29, 2022, 05:22:58 am »
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

  • Hero Member
  • *****
  • Posts: 1019
Re: Defining UpdateSQL etc. for TSQLQuery.ApplyUpdate
« Reply #3 on: September 29, 2022, 05:41:16 am »
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.
« Last Edit: September 29, 2022, 06:04:35 am by egsuh »

 

TinyPortal © 2005-2018