> I have a IB_Cursor, which selects data from selectable SP. It can
> happen, that the SP raises a exception. In this case I want to rollback
> the transaction. Here's what I want to do:
>
> try
> IB_Cursor1.Open;
> while not IB_Cursor1.Eof do
> begin
> IB_Cursor1.Next;

[JLW] Add this line of code below.

> IB_Transaction1.Activate;


> end;
> Close;
> IB_Transaction1.Commit;
> except
> begin
> IB_Transaction1.Rollback;
> raise;
> end;
> end;
>
> The problem is, that the transaction always commits even when an error
> occurs. I have done some debugging and found the following:
> The procedure TIB_Dataset.SysFetchNext in IB_Components.pas calls
>
> try
> HandleException( Self );
> FINALLY
> DoCloseOffToEof;
> end;
>
> In my case it calls DoCloseOffToEof, which has the code:
>
> procedure DoCloseOffToEof;
> begin
> FCursorEof := true;
> API_CloseCursor;
> IB_Transaction.CheckOAT;
> ARow.ClearBuffers( rsNone );
> SysAfterFetchCursorEof;
> FCallbackInitTick := 0;
> end;
>
> IB_Transaction.CheckOAT finally commits the transaction. To summaries,
> when a IB_Cursor runs into an error, it closes. When it closes, it
> commits automatic.
> Does anyone knows a way, how to get control over the transaction? As I
> mentioned, I want to rollback the transaction when the cursor runs into
> an error.

You have failed to provide IBO the proper indication that there has been DML
executed that awaits a commit or a rollback. IBO doesn't know there are
changes awaiting a commit.

You need to call your IB_Transaction.Activate method after your first fetch
since at that point your select procedure will have executed DML on the
server.

By default IBO does not assume selecting records from a stored procedure
will result in DML being executed and the API does not produce a mechanism
to tell me that the transaction has changed to an active state (meaning
changes are awaiting commit).

HTH,
Jason L Wharton