views:

513

answers:

5

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

+1  A: 

According to the documentation, temporary tables are dropped when the session ends.

Jordan Ryan Moore
I guess, my concern is that the session ends when the connection is closed (right?). So, if foo is called multiple times before the connection is closed, and the first invocation to foo threw an exception and did not clean up the temp table, then the next invocation of foo will fail, because the temp table already exists.What is the best practice to code to protect from this. Do we need to check if the temp table exists and drop it?
prmatta
I entered "check if table exists" code for checking before create and drop. Does Informix have a temp table structure whereby a table can only be seen by the specific session? SQL Server has this functionality.
adamcodes
A: 
SELECT count(*) 
INTO w_count 
FROM sysmaster:systabnames s,sysmaster:systabinfo i
WHERE i.ti_partnum = s.partnum
AND sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
AND s.tabname = 'tempTable' ;

If w_count is 1, delete table before SELECT ... INTO. Same with DROP TABLE.

adamcodes
+1  A: 

Yes, the temp table will still exist. Temp tables by definition have a lifetime of the session that created them, unless explicitly dropped.

The temp table can only be seen by the session that created it, and there is no impediment to the same procedure being run in parallel by multiple users. Adam's answer to test for the existence of the temp table will return a non-zero result if any user is running the procedure. You need to test that the session that owns the temp table is the current session as well. Given that this question is within the scope of a stored procedure, it might be simpler to add an explicit DROP, wrapped in some exception handling.

RET
I assume you mean something like this:CREATE PROCEDURE droptempTtable()ON EXCEPTION IN (-206) END EXCEPTION;DROP TABLE tempTable;END PROCEDURE;Is there any way that you know of to specify the table name, to make this procedure generic?
prmatta
+1  A: 

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'.

Jonathan Leffler
@Jonathan I used a variation of what you suggested up here. I submitted what I used as an answer. Let me know if you have any comments on that spl/sql.
prmatta
+2  A: 

I finally used a variation of Jonathan's and RET's solution:

CREATE PROCEDURE foo ()
    ON EXCEPTION IN (-206)
    END EXCEPTION WITH RESUME;

    DROP TABLE tempTable;    

    SELECT * FROM fooBar INTO TEMP tempTable;

    -- do something with tempTable here

    DROP TABLE tempTable;
END PROCEDURE;
prmatta
What you've got here - drop the table but ignore the error if it doesn't exist - is a good way to go. Under ordinary circumstances, the temp table is missing both before it runs and after it completes. It would be possible to tweak the scope of the exception, but that should work fine.
Jonathan Leffler