* * *

Author Topic: Post works with Insert, but not update  (Read 414 times)

HatForCat

  • Sr. Member
  • ****
  • Posts: 251
Post works with Insert, but not update
« on: April 13, 2017, 11:58:52 pm »
EDIT: Just noticed that the Post does save an Edit, but only for the life of the program. If I shut down and restart it, it has rolled back.

Hi, using SQLdb and Data-Aware TDBEdit. I have a bunch on the Form and when I run db. Insert, fill them in and then run db.Post all is good.

But if I use db.Edit and make a few TDBEdit changes, nothing is saved it just rolls back. I have an After Post (I handle any errors in the calling function)

What do I need to do to make this Save the Editing? Do I have to write an SQL "UPDATE"?
I am seriously hoping not, as there are 52-Columns and pretty much any of then could be changed. Makes for a huge SQL.Text as I will not know which TDBEdits were changed.

Code: Pascal  [Select]
  1. begin
  2. procedure Add;
  3. begin
  4.   db.Insert;
  5.   ...
  6.   db.Post; // all good
  7.   DBSaveData(db);
  8. end;
  9.  
  10. procedure Change;
  11. begin
  12.   db.Edit;
  13.   ...
  14.   db.Post; // all rolled back and no errors reported
  15.   DBSaveData(db);
  16. end;
  17.  
  18. function Tdm.DBSaveData(const aQry: TSQLQuery) : Boolean;
  19. begin
  20.   Result:=False;
  21.   try
  22.     if trnMain.Active then
  23.     begin
  24.       aQry.ApplyUpdates;
  25.       trnMain.CommitRetaining;
  26.       Result:=True;
  27.     end;
  28.   except
  29.     on E: EDatabaseError do
  30.     begin
  31.       Result:=False;
  32.       trnMain.Rollback;
  33.     end;
  34.   end;
  35. end;
  36.  
« Last Edit: April 16, 2017, 01:05:31 am by HatForCat »
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

mangakissa

  • Hero Member
  • *****
  • Posts: 653
Re: Post works with Insert, but not update
« Reply #1 on: April 14, 2017, 08:11:29 am »
SQLdb doesn't rollback automatically. You have to apply the update tho the the provider.
Code: Pascal  [Select]
  1. edit;
  2. ....
  3. post;
  4. applyupdates;
  5. commit(retaining);
  6.  
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

HatForCat

  • Sr. Member
  • ****
  • Posts: 251
Re: Post works with Insert, but not update
« Reply #2 on: April 16, 2017, 01:05:06 am »
SQLdb doesn't rollback automatically. You have to apply the update tho the the provider.

Thanks, but I am already doing that in the "DBSaveData(..." after the Post.

Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Unity Desktop, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my own use for Ubuntu.

kapibara

  • Sr. Member
  • ****
  • Posts: 436
Re: Post works with Insert, but not update
« Reply #3 on: April 16, 2017, 06:47:17 am »
I guess you need code in your Query's UpdateSQL.

Example:

Code: SQL  [Select]
  1. UPDATE items
  2.    SET item_name=:item_name
  3.  WHERE item_id=:item_id
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

LacaK

  • Hero Member
  • *****
  • Posts: 528
Re: Post works with Insert, but not update
« Reply #4 on: April 16, 2017, 09:27:15 am »
Can you show aQry.SQL.Text ? Is it select from single table ?
What is aQry.UpdateMode (upWhereKeyOnly?)?
Can you show SQL create table ? does table has primary key defined ?

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus