InTransaction does not turn True when Insert

[ahijazi]

Dear DevArt;

Case 1 :
after open the OraQuery, and begin modify the records or delete any of the fetched records, the InTransaction is normally turn to True ( as expected ),
but when load the form again and call the OraQuery open method and begin to insert new records, the InTransactoin does NOT turn true ?????!!! Why

Case 2:
1- If I insert a record first.
2- Modifiy an alreay existed record
3- call Rollback and close the connection
what result is: the inserted record is commit and save to the database (I think its a bug), but the edited on is not ?? !!!! Why again ????

I hope to clearify this cases to me and what to do to correct this >>>
thanks in advance ...

Ahmed Hijazi P. Eng.

[bork]

Hello

Case 1:
The InTransaction property can be False if you finished transaction on first closing the form or between closing previous form and opening a new one. Transaction can be finished by the Commit or Rollback methods. If you called any from this method then please call the StartTransaction method before opening the form again.

Case 2:
I executed the following code:

Code: Select all

var
  i: integer;
  MySession: TOraSession;
  MyQuery: TOraQuery;
begin
  MySession := TOraSession.Create(self);
  MySession.Server := 'ORA1110';
  MySession.Password := 'tiger';
  MySession.Username := 'scott';

  try
    MySession.ExecSQL('drop table test_table', []);
  except
  end;

  MySession.ExecSQL('create table test_table ' + #13 +
                    '( ' + #13 +
                    'ID INTEGER not null, ' + #13 +
                    'Name VARCHAR2(250) not null, ' + #13 +
                    'CONSTRAINT PK_test_table PRIMARY KEY (ID) ' + #13 +
                    ')', []);

  MySession.ExecSQL('insert into test_table(ID, NAME) ' + #13 +
                    'values(1, ''test 1'')', []);
  MySession.ExecSQL('insert into test_table(ID, NAME) ' + #13 +
                    'values(2, ''test 2'')', []);
  MySession.ExecSQL('insert into test_table(ID, NAME) ' + #13 +
                    'values(3, ''test 3'')', []);

  MyQuery := TOraQuery.Create(self);
  MyQuery.Session := MySession;
  MyQuery.SQL.Text := 'select * from test_table';
  MyQuery.Open;

  MySession.StartTransaction;

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

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

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

  MySession.Rollback;
end;

The result is: all modifications were canceled.

Try to create a new application and execute the same code. Please provide me the result of executing this code. If this code is working correctly then please modify this code to reproduce your issue.

Also please provide us the exact ODAC version that you are using (for example 6.90.0.59).

[ahijazi]

Dear Devart;

below is a code that confim what I'm think about

Code: Select all

var
  snMain  : TOraSession;
  qryTest : TOraQuery;
  tblTest : TOraTable;

begin
  snMain := TOraSession.Create(self);

  with snMain do begin
    Options.Direct := True;
    Username    := 'dblock';
    Password    := 'dblock';
    Server      := 'ORA11g64bit';
    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'')', []);

    commit;
  end;


  tblTest := TOraTable.Create(self);
  with tblTest do begin
    Session     := snMain;
    AutoCommit  := False;
    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.Rollback;


  qryTest := TOraQuery.Create(self);
  with qryTest do begin
    Session := snMain;
    AutoCommit := False;
    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.Rollback;
end;

What you will find after execute the code, that the behavior of OraTable different than the OraQuery,
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.

ODAC version : 6.50.0.34 Pro Edition.
Delphi 2007.

thanks in advance ...

Ahmed Hijazi P. Eng.

[bork]

Hello

I reproduced this issue with ODAC version 6.50.0.34. But I run the same code with the last ODAC version (version 6.90.0.59) and all works correctly. So please download the latest ODAC version to resolve this issue.

[ahijazi]

Dear DevArt;

So I can assume that all DML operation will be in one Transaction, without the need of call Start Transaction explicitly in the new version. (need your confirm)??

Note that I use the following code to get if there is any changes

Code: Select all

  Result := snMain.ExecSQL('begin   :result := sys.dbms_transaction.step_id; end;', []);

I will try it and told you what will happen with me.

thanks in advance ...

Ahmed Hijazi P. Eng.

[bork]

Transaction will be finished after calling the Commit or Rollback methods. Any actions before calling the Commit or Rollback methods will be executed in the same transaction. If you set TOraSession.AutoCommit to False, then it is possible not to call StartTransaction explicitly.

[ahijazi]

Dear DevArt;
After upgrade to ODAC 6.90.0.57 edition, a new issue raised in my applications, where I use LocalMasterDetail option I found the following:
1- Insert an new record in the Master table, without post;
2- Insert record in the Detail table, the record is inserted, but after post the detail record it become hidden ???
3- Post the mater record it will become visible again ????!!!

below a sample that demonstrate the issue, note that in ODAC 6.50.0.34 the issue are not exists ??!!!!!
Thanks in Advanced
Ahmed Hijazi P.Eng.

Code: Select all

var
  snConnection      : TOraSession;
  tblDepts, tblEmps : TOraTable;
  dsDepts           : TOraDataSource;

begin
  snConnection  := TOraSession.Create(self);
  tblDepts      := TOraTable.Create(self);
  tblEmps       := TOraTable.Create(self);
  dsDepts       := TOraDataSource.Create(self);



  with snConnection do begin
    Options.Direct := True;
    Username       := 'dblock';
    Password       := 'dblock';
    Server         := 'gisserver::gisdb';
    AutoCommit     := False;
    Connect;
    try
      ExecSQL('drop table Emps', []);
      ExecSQL('drop table Depts', []);
    except
    end;

    ExecSQL('create table DEPTS(OID NUMBER(8) not null, NAME VARCHAR2(50) not null, constraint TBLMASTER_PK primary key (OID))', []);
    ExecSQL('create table EMPS(OID NUMBER(8) not null, NAME VARCHAR2(50),DEPT_OID  NUMBER(8),constraint EMPS_PK primary key (OID), constraint EMPS_FK foreign key (DEPT_OID) references DEPTS (OID))', []);
  end;

  with tblDepts do begin
    TableName     := 'DEPTS';
    KeyFields     := 'OID';
    Session       := snConnection;
    CachedUpdates := True;
  end;

  dsDepts.DataSet := tblDepts;

  with tblEmps do begin
    TableName     := 'EMPS';
    MasterFields  := 'OID';
    DetailFields  := 'DEPT_OID';
    MasterSource  := dsDepts;
    KeyFields     := 'OID';
    Session       := snConnection;
    CachedUpdates := True;
    Options.LocalMasterDetail := True;
  end;


  tblDepts.open;
  tblEmps.Open;


  tblDepts.Insert;
  tblDepts.FieldByName('OID').Value  := 1;
  tblDepts.FieldByName('Name').Value := 'Dept A';

  // Note that I have not Post, in my case i need the Detail recod to be inserted first then Post the Master Record.

  with tblEmps do begin
    Append;
    FieldByName('OID').Value       := 1;
    FieldByName('Name').Value      := 'Emp A';
    FieldByName('Dept_OID').Value  := 1;
    Post;

    Append;
    FieldByName('OID').Value       := 2;
    FieldByName('Name').Value      := 'Emp B';
    FieldByName('Dept_OID').Value  := 1;
    Post;


    // Values will not appears ??, in the ODAC 6.50.0.34, it work fine, but in ODAC6.90.0.57 it's not !!!!!
    First;
    ShowMessage(FieldByName('Name').AsString); // Empty string displayed
    Next;
    ShowMessage(FieldByName('Name').AsString); // Empty string displayed


    // But after I execute the post of the master table is shown normaly !!!!! 
    tblDepts.Post;

    First;
    ShowMessage(FieldByName('Name').AsString); // "Emp A" displayed
    Next;
    ShowMessage(FieldByName('Name').AsString); // "Emp B" displayed
  end;