Subject Re: [IBO] How to detect a read only transaction in SP?
Author Helen Borrie
At 11:26 AM 24/04/2005 +0200, you wrote:

> I'm to sure wether this is a firebird or a IBO question. But I
> think Helen know the answer and shes on both lists :-)
> I'm using the read only property of TIB_transaction.
> The help file says only:
> "Makes this transaction ReadOnly at the API level."
> My Question: How can I detect in an SP (or Trigger) that the
> transaction is ReadOnly? I only have the current_transaction
> in FB 1.5 but I can't find a way to see whether its a
> ReadOnly transaction or not.
> My idea is to have a code snippet in the log sub sp like
> if (current_transaction IS NOT readonly) then
> <Insert into Log-Table>

This isn't possible in Fb 1.5.x and it doesn't make sense, anyway. If you
have a SP that performs DML then you should use the client code to avoid
calling it from a read-only transaction.

If it happens that you are trying to "kill two birds with one stone" and
use a single SP to BOTH perform DML and to return a dataset, ABANDON IT
NOW. Just because you *can* do this dual stuff does not mean that you

The problem is that the output set under these conditions arrives at the
client as uncommitted data. Now, how are you going to organise it so that
the user understands that the data she is looking at doesn't actually exist
yet? That's apart from the other problem....the PSQL engine releases
output from selectable SPs one row at a time. Any unhandled exception
during the course of execution - including an attempt by the dataset to
"update itself" will cause the SP to terminate. The user -- and the
application, unless you are very, very smart -- will be left not knowing
which way is up.

If this is your situation, break the SP up into one selectable SP that a
read-only transaction is allowed to call and one (or more) executable SPs
that your application will allow to be called only from a read-write


> Bjoern
>SOFTbaer --- reimer@... ---
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>Yahoo! Groups Links