I'm trying to do a nested transaction using dbExpress in Delphi XE3 that is connected to MySQL 5.6.13.
Here is my example code to do a Nested transaction:
...
dbxTransaction := _connection.BeginTransaction(TDBXIsolations.ReadCommitted);
// just to check if nested is suported
supportsNestedBol := dbxTransaction.Connection.DatabaseMetaData.SupportsNestedTransactions;
try
_sqlQuery.Close;
_sqlQuery.SQL.Clear;
_sqlQuery.SQL.Add('INSERT INTO test(cod, name) VALUES(:cod, :name)');
_sqlQuery.ParamByName('cod').AsInteger := Test.Cod;
_sqlQuery.ParamByName('name').AsAnsiString := Test.Name;
_sqlQuery.ExecSQL(False);
//calls a nested function that has another transaction
Employee.Save();
_connection.CommitFreeAndNil(dbxTransaction);
Result := True;
except
on Exc:Exception do
begin
_connection.RollBackFreeAndNil(dbxTransaction);
raise Exc;
Result := False;
end;
end;
...
function Employee.Save():Boolean;
begin
...
dbxTransaction02 := _connection.BeginTransaction(TDBXIsolations.ReadCommitted);
try
_sqlQuery.Close;
_sqlQuery.SQL.Clear;
_sqlQuery.SQL.Add('INSERT INTO employee(cod, name) VALUES(:cod, :name)');
_sqlQuery.ParamByName('cod').AsInteger := Employee.Cod;
_sqlQuery.ParamByName('name').AsAnsiString := Employee.Name;
_sqlQuery.ExecSQL(False);
_connection.CommitFreeAndNil(dbxTransaction02);
Result := True;
except
on Exc:Exception do
begin
_connection.RollBackFreeAndNil(dbxTransaction02);
raise Exc;
Result := False;
end;
end;
end;
...
If I put a break point and check the variable supportsNestedBol the value is False.
So, I'm not sure if is the connector "libmysql.dll" that I'm using that not support nested transaction or if is the way that I'm trying to do it.
Some help?