InTransaction Work-Around?

[jthelman]

Not a DOA problem, but I wanted to see if anyone had a solution:

Although it doesn't cause an update, A simple select statement on a remote database will cause the OracleSession.InTransaction flag to be true.

We are trying to use InTransaction to cause a 'Save Changes' prompt when exiting our app. Although technically Oracle is in transaction with a select statement for a remote DB, I'd like a better way to find out if there are *updates* pending on the database.

Anyone have suggestions?

 

[MarkR]

Without even accessing Oracle, you should be able to track this within your app. Create a boolean variable on your main form called something like UpdatesPending. Initialize it to False. Since a new transaction will start after a commit or rollback, reset the variable to False after a commit or rollback. After the application successfully runs an update, insert or delete statement, set the variable to True. When you close the app, check the value of this variable, if True, then prompt the user.

I know this solution does not have direct Ora cle interactions to it, but is a quick way to make this determination and you don't even have to make another round network trip to the Oracle server.

 

[jthelman]

Originally posted by MarkR:
Without even accessing Oracle, you should be able to track this within your app. Create a boolean variable on your main form called something like UpdatesPending. Initialize it to False. Since a new transaction will start after a commit or rollback, reset the variable to False after a commit or rollback. After the application successfully runs an update, insert or delete statement, set the variable to True. When you close the app, check the value of this variable, if True, then prompt the user.

I know this solution does not have direct Ora cle interactions to it, but is a quick way to make this determination and you don't even have to make another round network trip to the Oracle server.

Yeah, but a worst case scenario is that the flag gets hosed. You then do not ask to save changes, and all changes are lost.

This becomes especially likely if you have multiple places where you commit and rollback and insert - you will be setting/unsetting the flag all over the GUI. I though about this approach, but it seems like a last resort.

John

 

[Marco Kalter]

Just a thought:

Commit the remote select statement if no transaction was started before this select statement was called:

TransactionStarted := Session.InTransaction;
(execute remote select statement)
if not TransactionStarted then Session.Commit;

------------------
Marco Kalter
Allround Automations

 

[jthelman]

That makes a lot of sense Marco - Thank You.

John