Re: Temp Table not clear after the informix session has terminated

Mark D. Stock wrote: > > jh wrote: > > > > Hi, > > > > I've a store procedure that will be called by an application. The store > > procedure when invoked will create temp table 'with no log' option. After > > the procedure has run by the application, I make a select on the systabnames > > and found that the temp table is present there. My question is how can I > > clear the 'temp' table when a normal drop table command is unable to perform > > the task. In the first place, why is the temp table not cleared after the > > session has been terminated ?? > > > > I'm using ODS 7.22.uc2 and Solaris 2.5.1 > > The temp table will be dropped when you close the database connection. > You can also explicitly drop a temp table if you wish. > > Has your 'session' terminated? Or has your SP terminated? Two different > things. Close the database connection, and open it again. Is the table > still there? If the presence of the temp table causes problems for subsequent executions of the procedure add a 'DROP TABLE tempname' command to the beginning of the stored procedure and add exception handling to ignore the -206 and ISAM -111 errors that will result the first time in a session that the procedure is invoked. If the procedure does not return more than one row (ie no WITH RESUME clause) you can just explicitely drop the temp table at the end of the procedure. This will not work consistently if the procedure contains a FOREACH...WITH RESUME if the procedure is called using a cursor and all rows are not fetched and so you will have to use the first method I detailed. Art S. Kagel