Devart Forums

Posted: **Mon 21 Nov 2011 16:14**

I am having a problem with BeginTransaction:

Using:
SQLConn1: TSQLConnection;
SQLConn2: TSQLConnection;
qu1 :TSQLQuery;
qu2 :TSQLQuery;

qu1 is linked to SQLConn1 and
qu2 is linked to SQLConn2

String Connection:
hostname : 127.0.0.1:3308
database: test
user_name: root
password: 1

//------------ The code is simplified only to test
This is on Delphi 2010 (DBX4) with driver from Borland:

SQLConn1.BeginTransaction;
qu1.SQL.Text := 'show variables like ''autocommit'';';
qu1.Open;
ShowMessage(qu1.Fields[1].AsString);
qu1.Close;
==> Shows 'OFF' ==> works OK

//------------
This is on Delphi 2010 (DBX4) with driver from Devart (5.0.1):

SQLConn2.BeginTransaction;
qu2.SQL.Text := 'show variables like ''autocommit'';';
qu2.Open;
ShowMessage(qu2.Fields[1].AsString);
qu2.Close;
==> Shows 'ON' ==> Must be 'OFF'. What Happened ???
So I can't continue with rollbacks...

//------------
Tests whith MySQL Community 5.1.42 and 5.5.18

Roberto

Posted: **Wed 23 Nov 2011 12:25**

Hello,

MySQL runs with autocommit mode enabled by default. Also note that the autocommit MySQL variable doesn't influence transactions. For example, the next two pieces of code have the same functionality:

Code: Select all

procedure TForm1.BitBtn1Click(Sender: TObject);
var
  tr: TDBXTransaction;
begin
  // first
  SQLConnection1.Open;
  SQLConnection1.BeginTransaction;
  SQLQuery1.SQL.Text := 'your update sql statement';
  SQLQuery1.ExecSQL;
  SQLConnection1.CommitFreeAndNil(tr);
  SQLConnection1.Close;

  //second
  SQLConnection1.Open;
  SQLConnection1.Execute('SET AUTOCOMMIT=0', nil);
  SQLQuery1.SQL.Text := 'your update sql statement';
  SQLQuery1.ExecSQL;
  SQLConnection1.Execute('COMMIT', nil);
  SQLConnection1.Close;
end;

You can find more information about the autocommit MySQL variable here:
http://dev.mysql.com/doc/refman/5.0/en/ ... autocommit
http://dev.mysql.com/doc/refman/5.0/en/commit.html

Devart Forums

Problem with BeginTransaction and StartTransaction

Problem with BeginTransaction and StartTransaction