temp table existence
tomcaml asked how to check whether a temp table exists before dropping it. Doug Lawry posted a sysmaster query using systabinfo flags to list temp tables, but suggested simply dropping and ignoring the error. Jonathan Leffler agreed, noting a check can race with someone creating a real table of that name. bozon asked how to tell which session owns a temp table; no answer.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Does anyone know of a command in Informix to check for the existence of a temp table before you attempt to drop it? Thanks!
If you search for "temp table list" at
http://groups.google.com/group/comp.databases.informix
you'll find that Gerd Kaluzinski posted this on July 3 2002:
database sysmaster;
set isolation to dirty read;
select dbsname dbname,tabname,c.owner,name dbspace,ti_nrows,ti_rowsize
from sysdbspaces a, systabinfo b, systabnames c
where a.dbsnum = trunc(b.ti_partnum / 1048576)
and b.ti_partnum = c.partnum
and (bitval(ti_flags,'0x0020') = 1 or bitval(ti_flags,'0x0040') = 1)
You should be able to amend this for your purposes. However, can't you just drop
your temp table anyway and ignore any errors? What client language/environment
are you running?
--
Regards,
Doug Lawry
www.douglawry.webhop.org
<tomcaml@yahoo.com> wrote in message
news:1131459293.909803.6560@g49g2000cwa.googlegroups.com...
> Does anyone know of a command in Informix to check for the existence
> of a temp table before you attempt to drop it?
>
> Thanks!
tomcaml@yahoo.com wrote: > Does anyone know of a command in Informix to check for the existence > of a temp table before you attempt to drop it? As Doug said, much the simplest way to deal with it is by dropping the table and ignoring the "it wasn't there" error. Given that it is a temp, no-one else can have interfered with you. However, if you check for the existence of the table, you probably have to prepare a SELECT of some sort - either against sysmaster or against the temp table. In between the time when that check is made and the statement that creates the temp table is executed, someone else could create a non-temporary table that has the right (wrong?) name. So, you still have to handle errors in the statement that creates the temp table for you. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Is there a way to tell which session owns it?