title: Multiple parameterized Delphi SQL updates within a transaction
body: <p>I am trying to update two different SQL tables in the same loop using parameterized queries in Delphi XE8. I also want to wrap the whole thing in a transaction, so that if anything in the loop fails, neither table gets updated.</p>
  
  <p>I don't really know what I'm doing, would appreciate some help. </p>
  
  <p>The code below is a simplified version of what I'm trying to achieve, and my best guess as to how to go about it. But I'm not really sure of it at all, particularly the use of two datasets connected to the 'SQL connection' component.  </p>
  
  <pre><code>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:={ some parameterized query updating table A }
  
    SQL_dataset2.CommandType:=ctQuery;
    SQL_dataset2.CommandText:={ some parameterized query updating table B }
  
    { Populate parameters and execute }
  
    For I:=0 to whatever do
    begin
      SQL_dataset1.ParamByName('Table A 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;
  </code></pre>
  
  <p>I am using Delphi XE8, and the database can be either SQL server or SQLite.</p>