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
Problem with BeginTransaction and StartTransaction
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: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
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;
http://dev.mysql.com/doc/refman/5.0/en/ ... autocommit
http://dev.mysql.com/doc/refman/5.0/en/commit.html