Pending transactions in a session

OswHitti · Post by **OswHitti** » Mon 24 Apr 2006 18:51

Is there a way to know if a session has pending transactions?

I'm working with a TOraQuery with CachedUpdates = False.

At some specific moment, I need to know if there are updates made to a table without being committed.

Is it a way to know if there are uncommitted changes in a session?

Any help will be appreciated,

Thanks,

Oswaldo.

Challenger · Post by **Challenger** » Tue 25 Apr 2006 12:57

For this purposes you can use InTransaction property of TOraSession, but you should excplicitly start transaction using StartTransaction method.

OswHitti · Post by **OswHitti** » Tue 25 Apr 2006 14:23

Thanks, but InTransaction is true after start transaction even if you hadn't done changes to a table.

What I need to know is if I had done an Insert, Delete or Update without a commit.

The only way InTransaction can tell me what I need is if I start transaction just after an Insert, Delete or Update a table. That means that I need to start transaction in a BeforePost and BeforeDelete of every TOraQuery that I have created. Is this the way that I have to do it?

Is this the only way to do it?

thanks,
Oswaldo.

jfudickar · Post by **jfudickar** » Tue 25 Apr 2006 22:38

Is it possible to use dbms_transaction?

I'm trying the following in the moment, but it didn't seem to work. Any idea?

function TOdacOracleSQLUtility.InTransaction: Boolean;
var id: Variant;
begin
if not SessionConnected then
Result := False
else
begin
Result := True;
Session.ExecSQLEx('begin :id :=sys.dbms_transaction.local_transaction_id; end;', ['id', '0']);
id := Session.ParamByName('id').Value;
Result := Not (VarIsEmpty(id) or VarIsNull(id));
end;
end;

Challenger · Post by **Challenger** » Wed 26 Apr 2006 10:36

You can get the ordinal of DML operations made in current transaction using step_id function from
dbms_transaction package. For example

Code: Select all

function GetOperationsOrder: Int64;
begin
  Result := OraSession.ExecSQL(      'begin' +
      '  :result := sys.dbms_transaction.step_id; ' +
      'end;', []);
end;

After you have started transaction using StartTransaction method you should store the result of GetOperationsOrder function in some variable.
After that the difference between the result of GetOperationsOrder and your variable will give the number of changes.

jfudickar · Post by **jfudickar** » Wed 26 Apr 2006 11:24

Does this mean, that i first must call StartTransaction?

I only want to know if there is a current transaction.

And as far as i know, this should be possible using:
sys.dbms_transaction.local_transaction_id;

Greetings
Jens

Challenger · Post by **Challenger** » Wed 26 Apr 2006 11:52

This is one of approachs to determine that any changes were made. You may not call StartTransaction method but then you should set AutoComit property to False to prevent commiting of local transactions. In this case you can use local_transaction_id function to detect if the local transaction is active.

jfudickar · Post by **jfudickar** » Wed 26 Apr 2006 23:07

Thanks.

I've forgotten to change the autocommit

Greetings
Jens