The solution I use is for every app to use library functions to BEGIN WORK and to COMMIT or ROLLBACK WORK that check a pair of global variables to see if the database supports transactions and whether it is in ANSI mode or not (they are set after connecting to the database). That BEGIN function sets a global flag indicating that a transaction has begun. The COMMIT and ROLLBACK functions check it to see if a transaction is active. You can do the same.
Another option is for the function to open or maintain a second connection, switch connections, use that second session to check the transaction status of the main session using the query you posted, then switch back to the main session to do what's needed. That will only work if at least one of the sessions is not a shared memory connection type (can't have two shared memory connections in the same process).
Managing multiple connections, at least in ESQL/C, is not difficult code. Dostats, for example, uses two connections when you ask it to process all user databases, one to get the list of databases that also manages a second connection to whichever database it is currently processing to do the actual work. My dbcopy utility uses two connections, one to the source database and a second to the target database to save the overhead of connecting to the target through the source server as a remote connection. If the databases are in separate instances this is much faster.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Fri November 05, 2021 04:37 AM
From: Øyvind Gjerstad
Subject: Detect if we are inside a transaction
We have a library function that needs to run inside a transaction, so we would like to check if we are inside a transaction, and if not, start a transaction, do the work and commit. If there is a transaction already, we don't do anything.
Currently we do a "begin work" and check if that gives -535, but I wonder if there is a more elegant solution.
I searched and came across some old posts on the subject from the newsgroup, for instance one answer which I distilled into this:
select sysmaster:bitval(txptab.flags,"0x2") from sysmaster:systxptab txptab, sysmaster:sysrstcb rstcb where rstcb.address = txptab.owner and sid=DBINFO("sessionid");
But for the current session this always gives 1, I suspect that is because of the implicit transaction that is started for each statement. It works if I check other sessions, but that can't be used for my case.
I'm on 14.10.FC5, if that makes a difference.
------------------------------
Øyvind Gjerstad
Developer/Architect
PostNord AS
------------------------------
#Informix