AutoCommit=False not working

AutoCommit=False not working

Postby Mark Klink » Tue 30 Aug 2005 23:28

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
Mark Klink
 

Postby Paul » Wed 31 Aug 2005 06:33

You must use TCRSQLConnection component instead of TSQLConnection if you want to use additional DbxOda options.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Re: AutoCommit=False not working

Postby Mark » Wed 31 Aug 2005 16:14

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
 
Posts: 3
Joined: Tue 30 Aug 2005 22:56
Location: CA, USA

Re: AutoCommit=False not working

Postby Mark » Fri 02 Sep 2005 19:35

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.
Mark
 
Posts: 3
Joined: Tue 30 Aug 2005 22:56
Location: CA, USA

Postby Paul » Sun 02 Oct 2005 18:01

We reproduced your problem and fixed it. This fix will be included to the next DbxOda build
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Mark » Thu 17 Nov 2005 18:03

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.
Mark
 
Posts: 3
Joined: Tue 30 Aug 2005 22:56
Location: CA, USA


Return to dbExpress driver for Oracle