September 30th, 2012, 09:12 PM
Multiple Sql commands within a Trasaction.
I'm new and still learning in Delphi. Can anybody help me regarding the correct syntax on executing multiple Sql commands(e.g. Insert) using Transaction? And if any statement fails, it will automatically rollback the transaction.
Any insights will greatly appreciated.
Thanks a lot for your time.
October 1st, 2012, 08:26 AM
I usually use the following pattern:
This way committing the transaction will only occur if all the sql commands have been executed properly and no exception raised within the block.
raise; // re-raise the exception to make it available to interested company
October 1st, 2012, 11:07 AM
I would just add that, while Luthfi's technique is correct, the actual syntax will vary depending upon the database involved and the components used.
Some do not require an explicit start statement.
Some just require commit or rollback.
Firebird also has a CommitRetaining option, so just check your particular syntax.
October 1st, 2012, 09:18 PM
Hi Luthfi and Clive,
So glad to hear from you guys, and I think I'm almost there.
I use MySql as my DB, and this are my components (TZMySqlDatabase, TZMySqlTransact, TZMySqlQuery).
Here's my code when I click Save button.
TZMySqlQuery1.Sql.Add('insert into practiceDB.tbl_Header (field1,field2,field3) values (value1,value2,value3) ');
TZMySqlQuery2.Sql.Add('insert into practiceDB.tbl_Details (field1,field2,field3) values (value1,value2,value3) ');
What's wrong here guys is, when I intentionally put an error on second query, the first query will be successfully saved. What do you think did I miss on this code guys?
October 2nd, 2012, 01:12 AM
Perhaps TZMySqlQuery1 and TZMySqlQuery2 did not use the same connection? Transaction only applies to a single session/connection.