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