* * *

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

HatForCat

  • Sr. Member
  • ****
  • Posts: 260
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: 668
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: 260
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: 441
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: 529
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 ?

PatBayford

  • New member
  • *
  • Posts: 17
Re: Post works with Insert, but not update
« Reply #5 on: May 05, 2017, 04:44:34 am »
HatForCat : Does your table have a primary key? If not then update will not work in any case.

 

Recent

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