sql server - Multiple parameterized Delphi SQL updates within a transaction -

i trying update 2 different sql tables in same loop using parameterized queries in delphi xe8. want wrap whole thing in transaction, if in loop fails, neither table gets updated.

i don't know i'm doing, appreciate help.

the code below simplified version of i'm trying achieve, , best guess how go it. i'm not sure of @ all, particularly use of 2 datasets connected 'sql connection' component.

sql_transaction.transactionid :=1; sql_transaction.isolationlevel:=xilreadcommitted; sql_connection.begintransaction; try   { create connections }    sql_dataset1              :=tsqldataset.create(nil);     sql_dataset1.sqlconnection:=sql_connection;    sql_dataset2              :=tsqldataset.create(nil);     sql_dataset2.sqlconnection:=sql_connection;    { create queries }    sql_dataset1.commandtype:=ctquery;   sql_dataset1.commandtext:={ parameterized query updating table }    sql_dataset2.commandtype:=ctquery;   sql_dataset2.commandtext:={ parameterized query updating table b }    { populate parameters , execute }    i:=0 whatever   begin     sql_dataset1.parambyname('table field 1').asstring:='value';     sql_dataset1.execsql;       sql_dataset2.parambyname('table b field 1').asstring:='value';     sql_dataset2.execsql;    end;    sql_connection.commit(sql_transaction); except   sql_connection.rollback(sql_transaction);   end; 

i using delphi xe8, , database can either sql server or sqlite.

the logic of transaction handling correct (except missing exception re-raise mentioned @whosrdaddy). wrong missing try..finally blocks dataset instances. except should stop using tsqlconnection deprecated methods using ttransactindesc record (always check compiler warnings when you're building app.). , can switch tsqlquery component. try instead:

var   i: integer;   query1: tsqlquery;   query2: tsqlquery;   connection: tsqlconnection;   transaction: tdbxtransaction; begin   ...     query1 := tsqlquery.create(nil);   try     query1.sqlconnection := connection;     query1.sql.text := '...';      query2 := tsqlquery.create(nil);     try       query2.sqlconnection := connection;       query2.sql.text := '...';        transaction := connection.begintransaction;       try         // fill params here , execute commands         := 0 42         begin           query1.execsql;           query2.execsql;         end;         // commit if went right         connection.commitfreeandnil(transaction);       except         // rollback @ failure, , re-raise exception         connection.rollbackfreeandnil(transaction);         raise;       end;           query2.free;     end;       query1.free;   end; end;