RE: Temporary tables

Hi, You can use {on exception and raise exception} clauses to check /drop /re-create the temporary tables... the skeleton code may look like this, ..... on exception (table exists error code) drop table .... create table ..... end exception insert into table check the corresponding table exists error if that's the error, raise exception end if ....... Or instead of dropping and recreating the table, you can just delete the contents of the table and insert new rows. Hope this helps Srini. mlsrini@infogain.com In article <5sqldu$60l@cssun.mathcs.emory.edu>, spal@scotch.den.csci.csc.com (Sujit Pal) wrote: > > Uday > > TEMP tables are created and will persist for the duration of the = > session. If a SP is called multiple times per session, then TEMP tables = > will persist across the SP's life. > > IMHO, I dont think you can DROP a TEMP table short of closing the = > session. An easier way would be to CLOSE DATABASE and reopen the = > database after each call to the SP, if your SP uses TEMP tables. It may = > have a performance impact, though. > > In order to detect that your TEMP tables exist, a better way may be to = > set a flag after the first invocation of the SP (which creates the TEMP = > tables) and then passing the flag to the SP to use the existing TEMP = > tables (for example, DELETE instead of DROP) for subsequent invocations. = > Again it depends on the size of your TEMP tables. > > Note that different users can create TEMP tables with the same name (the = > creator name and the table name need to be unique) so the flag setting = > solution would be applicable for multiple users as well. > > HTH > > Sujit Pal > ---------- > From: Uday Shankar[SMTP:uday@dpg.rnb.com] > Sent: Tuesday, August 12, 1997 11:16 AM > To: informix-list@rmy.emory.edu > Subject: Temporary tables > > Hi, > > I am looking for a way to detect if a temporary table > already exists. I am creating temp tables in stored > procedures and the informix manuals tell me that the > tables will exist past the life of the stored procedure's > execution. > > SO, I would like to find a way to detect if the > temp table already exists and drop the old table > if it does. > > It looks like the temporary table names are not registered > in the system catalog table systables, so there doesn't > appear to be any obvious way of checking to see if it > exists or not. > > I checked the manuals and the IIUG FAQ and search engine > but didn't find any mention of that. > > Thanks, > > Uday > -- > Uday Shankar > Derivative Products Group > Republic National Bank of New York > uday@dpg.rnb.com > (212) 525-8000 -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet