How do I prevent TOraSession from implicit commit?

William · Post by **William** » Tue 30 Aug 2011 14:31

If an application is stopped or crashes while TOraSession is in transaction
DML changes are saved anyway.Is there a way to prevent TOraSession
from committing these changes ? Both TOraSession and TOraTable AutoCommit properties are set to false.
I can see the same topic, but can't see the answer.

AlexP · Post by **AlexP** » Wed 31 Aug 2011 08:56

Hello,

You should use the TOraTransaction component to solve this problem, and you should set it its DefaultCloseAction property to taRollback (it's default value is taCommit by default).

AGOSSELIN · Post by **AGOSSELIN** » Wed 31 Aug 2011 13:34

Hi,

In your last answer in the following link you seem to have an other solution ?

http://www.devart.com/forums/viewtopic. ... t=implicit

Thanks,

lior · Post by **lior** » Wed 31 Aug 2011 14:32

Hello,
At the time I asked AlexP about it, he wasn't aware of the problem.
I hope that in future release TOraSession won't implicitly commit changes.

AGOSSELIN · Post by **AGOSSELIN** » Wed 31 Aug 2011 14:40

Hi Lior,

Did you add TOraTransaction everywhere to fix the problem or you find an easier solution ?

Like you, we have a lot of place to change and we still hope to find an other way.

Thanks,

lior · Post by **lior** » Wed 31 Aug 2011 14:48

Hello AGOSSELIN,
Unfortunately I will have to add TOraTransaction everywhere.

AlexP · Post by **AlexP** » Thu 01 Sep 2011 12:04

Hello,

We cannot change the default value to taRollback, because many users got used to such behaviour. One of the ways to solve this problem is to create your own class that is a descendant of TOraSession and change the DefaultTransaction.DefaultCloseAction property to taRollback.

lior · Post by **lior** » Thu 01 Sep 2011 14:16

Hello Alexp,
I have no problem with TOraTransaction.DefaultCloseAction defaults to taRollback.
I think that if TOraSession.AutoCommit=False and it manages the transaction (TOraSession.StartTransaction without TOraTransaction) it should not save changes to the database before TOraSession.Commit command.

AlexP · Post by **AlexP** » Mon 05 Sep 2011 12:32

Hello,

The AutoCommit property is responsible for the “immediate” commit of data into a table, i.e. if this property is on, it will be impossible to perform rollback after executing the following code:

Code: Select all

   OraSQL.SQL := 'DELETE FROM Dept'; 
   OraSession.AutoCommit := True;
   OraSQL.AutoCommit := True;
   OraSQL.SQL := 'DELETE FROM Dept'; 
   OraSQL.Execute;         // delete all records, commit is performed
   OraSession.Rollback; // couldn't restore deleted records

If transaction is started explicitly

Code: Select all

OraSession.StartTransaction

the AutoCommit property does not influence saving data, and data is saved to the server only on the explicit calling of

Code: Select all

OraSession.Commit

, on closing a session with an active transaction, or on destroying a component (in two latter cases the TOraTransaction.DefaultCloseAction property should be set to taCommit).

lior · Post by **lior** » Mon 05 Sep 2011 13:01

ODAC equivalent to TDatabase is TOraSession. They both can start,commit or rollback a transaction.
However, If I want to make sure crashed application (Alt+Ctr+Del etc) don't save transaction changes, I have to add and connect TOraTransaction to each TOraSession. This complicates the conversion.

AlexP · Post by **AlexP** » Tue 06 Sep 2011 12:49

Hello,

By using the (Ctr+Alt+Del) you can end a task in the following two ways:

1- End Task
2- End Process

In the first case, the application will close on getting the WM_CLOSE, and its data will be committed. The second option is equivalent to application crash, data will not be committed.

In order not to add TOraTransaction to each TOraSession component, you can use your own component inherited from ToraSession, as I wrote earlier.

lior · Post by **lior** » Tue 06 Sep 2011 13:21

Hello,
You wrote earlier "Create a descendant of TOraSession and change the DefaultTransaction.DefaultCloseAction property to taRollback".
I don't have the source yet.Do you say thay I should create a TOraTransaction component inside the TOraSession descendant or is there a built in privete/protected DefaultTransaction property?

AlexP · Post by **AlexP** » Tue 06 Sep 2011 14:57

Hello,

To prevent data from being automatically committed, you need to override the TOraSession constructor by setting the DefaultTransaction.DefaultCloseAction property to taRollback. The component's code is provided below:

Code: Select all

unit RollbackOraSession;

interface

uses
  SysUtils, Classes, DB, DBAccess, Ora, CRAccess;

type
  TRollbackOraSession = class(TOraSession)
  public
    { Public declarations }
    constructor create(Owner: TComponent);override;
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('Oracle Access', [TRollbackOraSession]);
end;

{ TRollbackOraSession }

constructor TRollbackOraSession.create(Owner: TComponent);
begin
  inherited create(Owner);
  DefaultTransaction.DefaultCloseAction := taRollback;
end;

end.