transactionsinformix

within a procedure how to know if i am in a transaction


In Informix, from within a procedure, how do I know if the caller is calling me from within a transaction.


Solution

  • There isn't any particularly simple way to do it. Essentially, you have to try to start a transaction and see whether you get an error.

    For example, this stored procedure determines the transaction state. Remember that Informix supports unlogged databases where transactions are not available.

    CREATE PROCEDURE tx_state() RETURNING VARCHAR(14);
        DEFINE errcode INTEGER;
        ON EXCEPTION IN (-256, -535) SET errcode
            IF errcode = -256 THEN
                RETURN "TX-Unavailable";
            ELIF errcode = -535 THEN
                RETURN "In-TX";
            END IF;
        END EXCEPTION
        BEGIN WORK;
        ROLLBACK WORK;
        RETURN "No-TX";
    END PROCEDURE;
    

    If you are inside a transaction and want to make changes but with an option to rollback just your changes, you can use a SAVEPOINT and later release it, or rollback to a savepoint.