Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As others stated, temporary tables last until you drop them explicitly or the session ends.</p> <p>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.</p> <p>Here is a mildly modified version of your stored procedure - one that generates a divide by zero exception (SQL -1202):</p> <pre><code>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. </code></pre> <p>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.</p> <pre><code>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; </code></pre> <p>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).</p> <p>Note that temptable still exists at this point:</p> <pre><code>+ 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. </code></pre> <p>This shows that the procedure no longer fails because the temp table is present.</p> <p>You can limit the ON EXCEPTION block to selected error codes (-958 seems plausible for this one) by:</p> <pre><code>ON EXCEPTION IN (-958) ... </code></pre> <p>See the IBM Informix Guide to SQL: Syntax manual, chapter 3 'SPL Statements'.</p> <ul> <li>For Informix 12.10 <a href="https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1301.htm" rel="nofollow noreferrer">SPL Statements</a></li> <li>For Informix 11.70 <a href="https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_1301.htm" rel="nofollow noreferrer">SPL Statements</a></li> <li>For Informix 11.50 <a href="https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/ids_sqs_1301.htm" rel="nofollow noreferrer">SPL Statements</a></li> </ul> <p>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 <a href="https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0736.htm" rel="nofollow noreferrer">DROP TABLE</a> statement:</p> <pre><code>DROP TABLE IF EXISTS tempTable; </code></pre> <p>Thus, with Informix 11.70 or later, the easiest way to write the procedure is:</p> <pre><code>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; </code></pre> <p>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.</p> <pre><code>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; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POlife span of temp table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJonathan Leffler
    1. USJonathan Leffler
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO@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.
      singulars
      1. PO
      1. USrouble
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload