Help with using TMyCommand with transaction please?

[docH]

I'm using a TMyCommand component in a Delphi application communicating with a remote MySQL database to execute a transaction with several statements. Any one of these statements might cause an error and if it does I want to roll back all of the statements, ie the whole transaction.

I am using the code below but I'm not convinced it is correctly detecting an error and doing the rollback every time as the error message isn't shown. Please can somebody take a look at it and tell me if it should work OK and if not, how I should alter it to do what I want?

Code: Select all

sql := ''
+ 'START TRANSACTION; '
+  'Insert into ....'       // lots of statements that might cause an error
+  'Delete from ...  '  
etc
+ ' COMMIT; '  ;

try
 MyCommand1.SQL.Clear;
 MyCommand1.SQL.Add(sql);
 MyCommand1.Execute;

 //if we get here then the sql went OK 

except    //if we get here then the sql caused an error somewhere
     on E : Exception do
         begin
         showmessage('Error in transaction '); 
         MyCommand1..SQL.Clear ;
         MyCommand1..SQL.Add('ROLLBACK; ');
         MyCommand1..Execute;
         end; //on E : Exception
end; //try  - except

[ViktorV]

To work with MySQL transactions, we recommend using the StartTransaction, Commit, and Rollback methods of the TMyConnection component. Here is a code example:

Code: Select all

sql := ''
  +  'Insert into ....'       // lots of statements that might cause an error
  +  'Delete from ...  '  
  etc
if not MyCommand1.Connection.InTransaction then
  MyCommand1.Connection.StartTransaction;
try
  MyCommand1.SQL.Clear;
  MyCommand1.SQL.Add(sql);
  MyCommand1.Execute;
  MyCommand1.Connection.Commit;

//if we get here then the SQL went OK 

except    //if we get here then the SQL caused an error somewhere
  on E : Exception do
  begin
    Showmessage('Error in transaction '); 
    MyCommand1.Connection.Rollback;
  end; //on E : Exception
end; //try  - except

[docH]

Ah, I see the difference, thank you. That looks more reliable.

[ViktorV]

Feel free to contact us if you have any further questions about MyDAC.