At 12:07 AM 29/09/2007, you wrote:

>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; // should be First, not Open

// at this point, SP executes up to first SUSPEND

> while not IB_Cursor1.Eof do
> begin
> IB_Cursor1.Next; // waits for and gets next record from SPs
> output buffer
> end;

// at each turn of this loop, the SP executes one turn of its loop

> Close; // Empties the ib_cursor's row buffer
> IB_Transaction1.Commit; // ends the transaction
> except
> begin
> IB_Transaction1.Rollback; // ends the transaction
> raise; // passes the SP exception up the chain
> 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.

This is the correct thing to do, since a Commit doesn't leave
interesting records behind. Rolled-back records remain interesting
for at least one extra GC cycle.

>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.

A transaction is an interaction between the client and the
server. "Control over the transaction" translates directly to taking
control of the workflow at the client's side. Either it executes
cleanly or it doesn't. If this is a "clean" selectable SP then your
client side procedure only has to commit the transaction ASAP and
clean up the local mess. You want the transaction finished and out
of the way as soon as possible, with minimum impact on garbage, which
is why Commit is right for this and Rollback is not.

An ib_cursor doesn't buffer any rows except the one it has most
recently fetched. You call First to open the row buffer, to tell the
SP to start executing up to the first SUSPEND and to fetch the first
row from the buffer. The server then executes another loop up to
SUSPEND and waits for your procedure to call Next (i.e., Fetch).

When Next is called, the row buffer is empty and the ib_cursor is at
EOF. If a row does not come, the ib_cursor stays at EOF and your
procedure's loop ends. This will be the case, whatever caused the
rows to stop coming, i.e., soon as the SP finishes (or drops out) of
its FOR SELECT...DO...SUSPEND loop. For the ib_cursor, it is
"situation normal", whether the SP ended normally or through an
exception. EOF terminates your client-side loop.

Calling Next clears the row buffer in preparation for the next row
and then calls Fetch. Your code doesn't show where you pass the rows
after a successful Fetch but, presumably, your real code is storing
them somewhere, perhaps in stringlists....such structures are beyond
the control of the ib_cursor, so part of your task when the SP
returns an exception is to clear these structures and, if they are
created for this task, to destroy them as well.

On the other hand, if your SP is executing some DML in addition to
feeding out rows to the output buffer --- a highly NOT RECOMMENDED
thing to do --- then you will have a genuine need to use Rollback to
end the transaction. Under these circumstances, the workflow sampled
here is not safe enough. You need a carefully constructed nest of
handlers inside an outer try..finally loop to ensure that everything
is unwound in proper sequence to return to the original state.

Helen