Forum > Databases
Saving Master/Detail
kapibara:
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
mangakissa:
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?
valdir.marcos:
--- Quote from: kapibara 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?
--- End quote ---
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:
@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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---qryTest.Open;qryTest.Insert;qryTest.FieldByName('demo_name').AsString:='A demo name';qryTest.ApplyUpdates;ShowMessage(qryTest.FieldByName('demo_id').AsString);TX.Commit;
@valdir.marcos Thanks, will play around with that approach too.
GAN:
--- Quote from: kapibara 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---qryTest.Open;qryTest.Insert;qryTest.FieldByName('demo_name').AsString:='A demo name';qryTest.ApplyUpdates;ShowMessage(qryTest.FieldByName('demo_id').AsString);TX.Commit;
@valdir.marcos Thanks, will play around with that approach too.
--- End quote ---
If demo_id is primary key autoincrement then the value is asigned after commit.
Navigation
[0] Message Index
[#] Next page