Recent

Author Topic: Saving Master/Detail  (Read 3487 times)

kapibara

  • Hero Member
  • *****
  • Posts: 610
Saving Master/Detail
« on: March 22, 2018, 09:13:20 pm »
How do I save both master and details in a transaction so all or nothing is saved? I cant get the masters id to use as foreign key in detail before committing master data and then its too late to rollback if somehting goes wrong with saving the details?

In this test-program, add a new master (Country), then use the DBGrid to fill out the details.

The details are descriptions of predefined ItemTypes. (Capital and Language). The itemtypes are shown in the left column and supposed make it easy to understand what you are expected to fill out in the right column.

Demo creates a small SQLite database in the program dir
« Last Edit: March 22, 2018, 09:42:26 pm by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Saving Master/Detail
« Reply #1 on: March 22, 2018, 09:44:02 pm »
Are you sure?
If the master is saved in transaction a transaction file it should create a master_id virtual. Then you can call the saved record for master_id (or the insert is returning a primary key) for saving the detail. The transaction has collect them all in virtual and when you commit the changes are finally.

Did you tested it with one table?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Saving Master/Detail
« Reply #2 on: March 22, 2018, 09:50:58 pm »
How do I save both master and details in a transaction so all or nothing is saved?

You can use one TSQLite3Connection, one TSQLTransaction and two TSQLQuery components from the 'SQLdb tab' in the component palette.
http://wiki.freepascal.org/SQLdb_Package
http://wiki.freepascal.org/SQLite
http://wiki.freepascal.org/SQLdb_Tutorial1

Use the events OnAfterInsert and OnBeforePost of both queries to synchronize the information that ties both tables, then Commit or Rollback they together in the only one TSQLTransaction you used for both TSQLQuery.

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: Saving Master/Detail
« Reply #3 on: March 24, 2018, 02:45:15 am »
@mangakissa Ah.. With SQLite, the master id CAN be read after a call to ApplyUpdates.

My real app uses PostGres and then the value is not available for some reason, field "demo_id" is empty. Bug, or just me?

Code: Pascal  [Select][+][-]
  1. qryTest.Open;
  2. qryTest.Insert;
  3. qryTest.FieldByName('demo_name').AsString:='A demo name';
  4. qryTest.ApplyUpdates;
  5. ShowMessage(qryTest.FieldByName('demo_id').AsString);
  6. TX.Commit;
  7.  

@valdir.marcos Thanks, will play around with that approach too.
« Last Edit: March 24, 2018, 04:28:07 am by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

GAN

  • Sr. Member
  • ****
  • Posts: 370
Re: Saving Master/Detail
« Reply #4 on: March 24, 2018, 05:34:40 am »
@mangakissa Ah.. With SQLite, the master id CAN be read after a call to ApplyUpdates.

My real app uses PostGres and then the value is not available for some reason, field "demo_id" is empty. Bug, or just me?

Code: Pascal  [Select][+][-]
  1. qryTest.Open;
  2. qryTest.Insert;
  3. qryTest.FieldByName('demo_name').AsString:='A demo name';
  4. qryTest.ApplyUpdates;
  5. ShowMessage(qryTest.FieldByName('demo_id').AsString);
  6. TX.Commit;
  7.  

@valdir.marcos Thanks, will play around with that approach too.

If demo_id is primary key autoincrement then the value is asigned after commit.
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: Saving Master/Detail
« Reply #5 on: March 24, 2018, 04:44:10 pm »

If demo_id is primary key autoincrement then the value is asigned after commit.

Yes, demo_id is primary key autoincrement. So then I guess INSERT RETURNING should be used for PostGres. Wonder why ApplyUpdates is enough to get demo_id with SQLite.
« Last Edit: March 24, 2018, 07:38:29 pm by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

 

TinyPortal © 2005-2018