From ODAC to UniDAC

[ahijazi]

Dear Devart;

1- The following code run perfect in ODAC, but when using the UniDAC (5.3.8 ) after calling the commit method, exception is raised "can not perform operation inactive transaction" ???

Code: Select all

  snMain := TUniConnection.Create(self);
  with snMain do begin
    SpecificOptions.Add('Oracle.Direct=True');
    SpecificOptions.Add('Oracle.ClientIdentifier=Admin');
    Username       := 'dblock';
    Password       := 'dblock';
    Server         := 'serv::db';
    AutoCommit     := False;
    ProviderName   := 'Oracle';
    Connect;

    ExecSQL('create table A (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT A_PK PRIMARY KEY (ID))', []);
    ExecSQL('create table B (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT B_PK PRIMARY KEY (ID))', []);
    ExecSQL('insert into A values(1, ''Atest 1'')', []);
    ExecSQL('insert into B values(1, ''Btest 1'')', []);

    Commit;
  end;

2- A full description of the issue was discussed in previous post in the ODAC form http://forums.devart.com/viewtopic.php?f=5&t=18542 , when try the same code in UniDAC it did not work as expected ????

Code: Select all

    var
      snMain  : TUniConnection;
      qryTest : TUniQuery;
      tblTest : TUniTable;

    begin
      snMain := TUniConnection.Create(self);

      with snMain do begin
        SpecificOptions.Add('Oracle.Direct=True');
        Username    := 'dblock';
        Password    := 'dblock';
        Server      := 'ORA11g64bit::dbx';
        AutoCommit  := False;
        Connect;
        try
          ExecSQL('drop table A', []);
          ExecSQL('drop table B', []);
        except
        end;
       
        ExecSQL('create table A (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT A_PK PRIMARY KEY (ID))', []);
        ExecSQL('create table B (ID INTEGER not null, NAME VARCHAR2(250) not null, CONSTRAINT B_PK PRIMARY KEY (ID))', []);

        ExecSQL('insert into A values(1, ''test 1'')', []);
        ExecSQL('insert into A values(2, ''test 2'')', []);
        ExecSQL('insert into A values(3, ''test 3'')', []);

        ExecSQL('insert into B values(1, ''test 1'')', []);
        ExecSQL('insert into B values(2, ''test 2'')', []);
        ExecSQL('insert into B values(3, ''test 3'')', []);

        snMain.ExecSQL('commit', []); // used instead of commit because it raise an exception of inactive transaction
      end;


      tblTest := TUniTable.Create(self);
      with tblTest do begin
        Connection:= snMain;       
        TableName   := 'A';
        KeyFields   := 'ID';
        Open;

        Insert; FieldByName('ID').AsInteger := 4;   FieldByName('Name').AsString := 'test 4';  Post;

        Locate('ID', 1, []);
        Edit;  FieldByName('Name').AsString := 'new value 1';  Post;

        Locate('ID', 2, []); Delete;
        Next;
      end;

      snMain.ExecSQL('Rollback', []); // used instead of snMain.Rollback because it raise an exception of inactive transaction


      qryTest := TUniQuery.Create(self);
      with qryTest do begin
        Connection:= snMain;        
        SQL.Text := 'select * from B';
        KeyFields   := 'ID';
        Open;

        Insert; FieldByName('ID').AsInteger := 4;   FieldByName('Name').AsString := 'test 4'; Post;

        Locate('ID', 1, []);
        Edit;  FieldByName('Name').AsString := 'new value 1';    Post;

        Locate('ID', 2, []);
        Delete;
        Next;
      end;

       snMain.ExecSQL('Rollback', []); // used instead of snMain.Rollback because it raise an exception of inactive transaction
    end;

What you will find after execute the code, that the behavior of UniTable different than the UniQuery,
table "A" will have the record with ID=4 even if we Rollback the transaction (BUG).
table "B" will NOT have the record with ID=4 and that is a correct behavior.

I believe the must have the same behavior, they have to create a one Transaction for all DML operations, does it ???

Note that I have not use StartTransction methods.


Best Regards,

Ahmed Hijazi P. Eng

[AlexP]

Hello,

Thank you for the information. We have reproduced the problem and will investigate the reasons of such behavior.