life span of temp table

Question

I have the following procedure:

CREATE PROCEDURE foo ()
    SELECT * FROM fooBar INTO TEMP tempTable;

    -- do something with tempTable here

    DROP TABLE tempTable;
END PROCEDURE;

What happens if there is an exception before the DROP TABLE is called? Will tempTable still be around after foo exits?

If so, foo could fail the next time it is called, because tempTable would already exist. How should that be handled.

EDIT: I am using informix 11.5

Answer 1

As others stated, temporary tables last until you drop them explicitly or the session ends.

If the stored procedure fails because the table already exists, SPL generates an exception. You can deal with exceptions by adding an ON EXCEPTION clause -— but you are entering one of the more baroque parts of SPL, Stored Procedure Language.

Here is a mildly modified version of your stored procedure - one that generates a divide by zero exception (SQL -1202):

CREATE PROCEDURE foo ()
    define i integer;
    SELECT * FROM 'informix'.systables INTO TEMP tempTable;

    -- do something with tempTable here
    let i = 1 / 0;

    DROP TABLE tempTable;
END PROCEDURE;

execute procedure foo();
SQL -1202: An attempt was made to divide by zero.

execute procedure foo();
SQL -958: Temp table temptable already exists in session.

This shows that the first time through the code executed the SELECT, creating the table, and then ran foul of the divide by zero. The second time, though, the SELECT failed because the temp table already existed, hence the different error message.

drop procedure foo;
CREATE PROCEDURE foo()
    define i integer;

    BEGIN
        ON EXCEPTION
            DROP TABLE tempTable;
            SELECT * FROM 'informix'.systables INTO TEMP tempTable;
        END EXCEPTION WITH RESUME;
        SELECT * FROM 'informix'.systables INTO TEMP tempTable;
    END;

    -- do something with tempTable here
    let i = 1 / 0;

    DROP TABLE tempTable;
END PROCEDURE;

The BEGIN/END block limits the exception handling to the trapped statement. Without the BEGIN/END, the exception handling covers the entire procedure, reacting to the divide by zero error too (and therefore letting the DROP TABLE work and the procedure seems to run successfully).

Note that temptable still exists at this point:

+ execute procedure foo();
SQL -1202: An attempt was made to divide by zero.
+ execute procedure foo();
SQL -1202: An attempt was made to divide by zero.

This shows that the procedure no longer fails because the temp table is present.

You can limit the ON EXCEPTION block to selected error codes (-958 seems plausible for this one) by:

ON EXCEPTION IN (-958) ...

See the IBM Informix Guide to SQL: Syntax manual, chapter 3 'SPL Statements'.

• For Informix 12.10 SPL Statements
• For Informix 11.70 SPL Statements
• For Informix 11.50 SPL Statements

Note that Informix 11.70 added the 'IF EXISTS' and 'IF NOT EXISTS' clauses to CREATE and DROP statements. Thus, you might use the modified DROP TABLE statement:

DROP TABLE IF EXISTS tempTable;

Thus, with Informix 11.70 or later, the easiest way to write the procedure is:

DROP PROCEDURE IF EXISTS foo;

CREATE PROCEDURE foo()
    define i integer;
    DROP TABLE IF EXISTS tempTable;

    SELECT * FROM 'informix'.systables INTO TEMP tempTable;

    -- do something with tempTable here
    let i = 1 / 0;

    DROP TABLE tempTable;  -- Still a good idea
END PROCEDURE;

You could also use this, but then you get the previous definition of the procedure, whatever it was, and it might not be what you expected.

CREATE PROCEDURE IF NOT EXISTS foo()
    define i integer;
    DROP TABLE IF EXISTS tempTable;

    SELECT * FROM 'informix'.systables INTO TEMP tempTable;

    -- do something with tempTable here
    let i = 1 / 0;

    DROP TABLE tempTable;  -- Still a good idea
END PROCEDURE;