Using Transactions with MySQL and Delphi

[jamesgjatkin]

Hi all,

I have just downloaded the evaluation of MyDAC with the aim to upgrade a Delphi application from Interbase to MySQL.

The method in which Transactions are used appears to differ though. Based on the existing code, the translation should be:

Code: Select all

  xTransaction := TMyTransaction.Create(nil);
  xMyCommand := TMyCommand.Create(nil);
  xMyCommand.Connection := MyConnection1;
  xMyCommand.Transaction := xTransaction;
  xTransaction.StartTransaction();
  xMyCommand.SQL.Add('INSERT INTO TBL (A,B) VALUES ("1", "2")');
  xMyCommand.Prepare();
  xMyCommand.Execute();
  xTransaction.Commit();

As TMyCommand does not appear to have "Transaction" available, how can ths be done?

Thanks,

James

[AndreyZ]

Hello,

The point is that transaction support in MySQL and InterBase differs greatly. To work with MySQL transactions, you should use the StartTransaction, Commit, and Rollback methods of the TMyConnection component. Here is a code example:

Code: Select all

xMyCommand := TMyCommand.Create(nil);   
xMyCommand.Connection := MyConnection1; 
xMyCommand.Connection.Open;
xMyCommand.Connection.StartTransaction;
xMyCommand.SQL.Add('INSERT INTO TBL (A,B) VALUES ("1", "2")');
xMyCommand.Prepare;
xMyCommand.Execute;
xMyCommand.Connection.Commit;

[jamesgjatkin]

Thanks for the really quick response…

Ok, that all looks good…

As the Transactions look like they are tied to the Connection, what happens in the event that you have multiple threads within the same application, using the same connection each starting their own transactions at the same time – will this work ok, or do the transactions have to be ‘queued’?

[AndreyZ]

The point is that you should have a separate connection per each thread. In this case your application will be thread-safe and you will not have the problem with transactions.