How to tell if I'm in a transaction
Is there a way in an Informix stored procedure to tell whether a session is currently within a transaction or not? The only reason we're doing something wacky like this is that we're porting an application that uses another DBMS to Informix. That application uses some update statements that use the updated table in a subquery. It seems Informix is the only major DBMS that does not allow this. As a result, we're having to create stored procedures to replace those queries. However, a query re-writer is doing the replacement of the original query with the stored procedure call. We don't wish to re-write the code that works against other databases. As it is, those original update statements are sometimes in a transaction, and sometimes singleton SQL statements that are immediately committed. Since the stored procedure has to break the query up into multiple SQL statements, it would have to lock the table briefly while it does this. Unfortunately, we need to know whether or not we're in a transaction as to whether to issue a begin and commit or not. There are, to my knowledge, no nested transactions in Informix either. Any ideas? --John Bejarano.
Hi,
What about calling a procedure like this:
(manage with care - I haven't tried it not its syntax! )
create procedure in_tx() returning int; define in_tx int;
on exception in (-535)
return 1; -- we were inside a tx
end exeception;
begin work;
-- if it fails it's because you're yet in an open tx
commit work; -- we weren't, an so we remain!
return 0;
end procedure;
John Bejarano wrote:
> Is there a way in an Informix stored procedure to tell
> whether a session is currently within a transaction or
> not?
>
> The only reason we're doing something wacky like this
> is that we're porting an application that uses another
> DBMS to Informix. That application uses some update
> statements that use the updated table in a subquery.
> It seems Informix is the only major DBMS that does not
> allow this.
>
> As a result, we're having to create stored procedures
> to replace those queries. However, a query re-writer
> is doing the replacement of the original query with
> the stored procedure call. We don't wish to re-write
> the code that works against other databases.
>
> As it is, those original update statements are
> sometimes in a transaction, and sometimes singleton
> SQL statements that are immediately committed. Since
> the stored procedure has to break the query up into
> multiple SQL statements, it would have to lock the
> table briefly while it does this. Unfortunately, we
> need to know whether or not we're in a transaction as
> to whether to issue a begin and commit or not. There
> are, to my knowledge, no nested transactions in
> Informix either.
>
> Any ideas?
>
> --John Bejarano.
>
>
--
José Luis Matute Martínez
Responsable División Sistemas
Contacto Local del Grupo de Usuarios de Informix en España
D&D Grupo Dydes
Poligono EUROPOLIS Edif. Al Andalus Calle X, nº 4 y 6
28230 Las Rozas (Madrid)
Tf: +34 91 6407080 Fax: +34 91 6373280
Thanks
very much for the suggestions of using the "on
exception" syntax. As it turned out, I'm actually
trapping on a slightly different error when attempting
to get a table lock. It looks like:
create procedure sp_xxx();
define v_proc_xact integer;
on exception in (-524)
let v_proc_xact = 1;
begin work;
lock table xxx in exclusive mode;
end exception with resume;
let v_proc_xact = 0;
lock table xxx in exclusive mode;
...do some stuff to table xxx...
if v_proc_xact = 1
then commit work;
end if;
end procedure;
--- Khaled Bentebal <khaled.bentebal@consult-ix.fr>
wrote:
> Hi,
>
> You could try the following procedure:
>
> CREATE PROCEDURE p_test() RETURNING int;> DEFINE tx_flag int;
>
> ON EXCEPTION
> -- trapping of other errors
> END EXCEPTION
>
> ON EXCEPTION IN (-535)
> LET tx_flag=1;
> END EXCEPTION WITH RESUME
>
> LET tx_flag=0;
> BEGIN WORK; -- if you are in a transaction, this
> statement will trapped by
> the on exception block
> -- and continues since the with
> resume clause is present
> IF tx_flag=1
> THEN -- already in a transaction
> ELSE -- not in a transaction
> END IF
> -- other SPL or SQL statements
>
> RETURN 0;
> END PROCEDURE;
>
> Regards,
>
> Khaled Bentebal
> ConsultiX
> Tél: 33 (0) 1 39 12 18 00
> Fax: 33 (0) 1 39 12 18 18
> Mobile: 33 (0) 6 07 78 41 97
> Email: khaled.bentebal@consult-ix.fr
> Site Web: http://www.consult-ix.fr
>
>
>
>
> ----- Original Message -----
> From: "John Bejarano " <jbejarano@sbcglobal.net>
> To: <ids@iiug.org>
> Sent: Wednesday, July 06, 2005 3:54 AM
> Subject: How to tell if I'm in a transaction [5350]
>
>
> > Is there a way in an Informix stored procedure to
> tell
> > whether a session is currently within a
> transaction or
> > not?
> >
> > The only reason we're doing something wacky like
> this
> > is that we're porting an application that uses
> another
> > DBMS to Informix. That application uses some
> update
> > statements that use the updated table in a
> subquery.
> > It seems Informix is the only major DBMS that does
> not
> > allow this.
> >
> > As a result, we're having to create stored
> procedures
> > to replace those queries. However, a query
> re-writer
> > is doing the replacement of the original query
> with
> > the stored procedure call. We don't wish to
> re-write
> > the code that works against other databases.
> >
> > As it is, those original update statements are
> > sometimes in a transaction, and sometimes
> singleton
> > SQL statements that are immediately committed.
> Since
> > the stored procedure has to break the query up
> into
> > multiple SQL statements, it would have to lock the
> > table briefly while it does this. Unfortunately,
> we
> > need to know whether or not we're in a transaction
> as
> > to whether to issue a begin and commit or not.
> There
> > are, to my knowledge, no nested transactions in
> > Informix either.
> >
> > Any ideas?
> >
> > --John Bejarano.
> >
> >
>
>