Re: Temporary tables
Hi Dianne,
Thanks for your message.
I didn't want to do that, coz that pre-supposes knowledge of
the physical setup of the database and if the database setup
changes then my stored procedure might not work.
But, I believe I have found a way around the problem just now.
I can use exceptions to handle the creation the temp table and
ensure that it is a new table every time I access it, instead
of the table lying around for the course of the user session.
create procedure myproc()
begin
on exception in (-206)
-- do nothing
end exception with resume;
drop table u1;
create temp table u1(f1 int);end;
-- perform operations on table here
insert into u1 values(1);
-- drop table just in case...
drop table u1;
end procedure;
This appears to work.
Any comments/suggestions, please let me know. Thanks!
Uday
====
Dianne Pendleton wrote:
>
> First do onstat -d and note which numbers go with your temp dbspaces.
>
> Then run this in the sysmaster database:
>
> select hex(partnum) x, tabname from systabnames
> order by x;
>
> As an example, our temp dbspaces are 7 and 8. When I run the above query,
> I look for x that starts with 0x007 and 0x008 and then I can see what temp
> tables there are in those dbspaces.
>
> Dianne
>
> ______________________________ Reply Separator _________________________________
> Subject: Temporary tables
>
> Author: Uday Shankar <uday@dpg.rnb.com> at INTERNET
> Date: 8/12/97 1:16 PM
>
> 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,