AutoCommit=False not working

[Mark Klink]

Hello,

The dbExpress driver for Oracle will not honour AutoCommit=False in the connection. It will immediately commit changes rather than wait for a commit to be executed.

I looked at the source code for the driver and, without looking to deeply into the problem, got it working by modifying


Could you confirm the original problem and correctness of the modification?

Sample code to reproduce problem
procedure TForm1.Button2Click(Sender: TObject);
var
connRemote: TSQLConnection;
query1: TSqlQuery;
begin
connRemote := TSQLConnection.Create(nil);
connRemote.KeepConnection := True;
connRemote.DriverName := 'Oracle';
connRemote.GetDriverFunc := 'getSQLDriverORA'; // corelabs
connRemote.LibraryName := 'dbexpoda.dll'; // corelabs
connRemote.VendorLib := 'OCI.DLL';
connRemote.LoginPrompt := False;
connRemote.Params.Values['User_Name'] := 'User_Name';
connRemote.Params.Values['Password'] := 'Password';
connRemote.Params.Values['Database'] := 'Database';
connRemote.Params.Values['AutoCommit'] := 'False';
connRemote.Connected := true;
query1 := TSqlQuery.Create(nil);
query1.SQLConnection := connRemote;
query1.sql.add('update ...');
query1.ExecSQL; // commits the change!!
end;


Thanks,
Mark Klink
Spear Technologies

[Paul]

You must use TCRSQLConnection component instead of TSQLConnection if you want to use additional DbxOda options.

[Mark]

Hi Paul,

Using TCRSQLConnection does not help. AutoCommit is not an extended option. I have tested this as well (see below), and it still has the problem. Perhaps you could try it yourself?

Mark.

procedure TForm1.Button2Click(Sender: TObject);
var
connRemote: TCRSQLConnection;
query1: TSqlQuery;
begin
connRemote := TCRSQLConnection.Create(nil);
connRemote.KeepConnection := True;
connRemote.DriverName := 'Oracle';
connRemote.GetDriverFunc := 'getSQLDriverORA'; // corelabs
connRemote.LibraryName := 'dbexpoda.dll'; // corelabs
connRemote.VendorLib := 'OCI.DLL';
connRemote.LoginPrompt := False;
connRemote.Params.Values['User_Name'] := '...';
connRemote.Params.Values['Password'] := '...';
connRemote.Params.Values['Database'] := '...';
connRemote.Params.Values['AutoCommit'] := 'False';
connRemote.Connected := true;
query1 := TSqlQuery.Create(nil);
query1.SQLConnection := connRemote;
query1.sql.add('update ...');
query1.ExecSQL; // commits the change!!
query1.sql.Clear;
query1.sql.add('COMMIT');
query1.ExecSQL; // it's already committed
end;

[Mark]

Stored Procedures also have the same problem.
My driver modification has now become ...
TCRSQLConnection.SetOption ...
case
eConnAutoCommit:
begin //mk
FAutoCommit := LongBool(lValue);
FConnection.SetProp(prAutoCommit,lValue) //mk
end; //mk
which makes sense because starting a transaction has similar code to set the FConnection autocommit.

Mark.

[Paul]

We reproduced your problem and fixed it. This fix will be included to the next DbxOda build

[Mark]

The problem is still there in 2.50.6
(same test code above produces same problem with new driver)
I checked the source code for the new version and it is
eConnAutoCommit: begin
FAutoCommit := LongBool(lValue);
//FConnection.SetProp(prAutoCommit, FAutoCommit);
end;
Looks like it was added, and then commented out?
Please create 2.50.7 with this problem fixed ASAP
or let us know why it is commented out.
Thanks,
Mark.