How to drop a temp table using "if exists" logic ?

Could you expand on why do you need to test if it exists before you
drop it?

If you just drop it and it doesn't exist the drop will have no effect
other than that a status other than 0 will be returned from the drop
command. If you don;t test that status there will be no problem.


Veeru71 wrote:
> Hi
> Is there a way to drop a temp table in Informix using some kind of "if
> exists" logic ?
> In oter words I need to test for the existence of the temp table
> before issuing "drop table" command.
> Thanks a lot.
> Murty

On 6 Dec 2006 01:18:15 -0800, scottishpoet wrote:
> Could you expand on why do you need to test if it exists before you
> drop it?
>
> If you just drop it and it doesn't exist the drop will have no effect
> other than that a status other than 0 will be returned from the drop
> command. If you don;t test that status there will be no problem.
>
>
> Veeru71 wrote:
> > Hi
> > Is there a way to drop a temp table in Informix using some kind of "if
> > exists" logic ?
> > In oter words I need to test for the existence of the temp table
> > before issuing "drop table" command.
> > Thanks a lot.
> > Murty
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

Temp tables disappear at the end of the session anyway, so if you have
created it in this session it will exist, if you haven't created it
then it will not exist !!

Keith

You can attempt to drop the table in a stored procedure unconditionally and
ignore the specific error condition if it does not exist:

CREATE PROCEDURE drop_temp_table()
ON EXCEPTION IN (-206) END EXCEPTION;
DROP TABLE temp_table;
END PROCEDURE;

You can't soft-code the table name without using a Datablade. See also the
newsgroup thread entitled "Creating generic routine to drop procedure" started
two days ago.

--
Regards,
Doug Lawry
www.douglawry.webhop.org


"Veeru71" wrote in message
news:1165378712.116761.199740@j72g2000cwa.googlegroups.com...
> Hi
> Is there a way to drop a temp table in Informix using some kind of "if
> exists" logic ?
> In oter words I need to test for the existence of the temp table
> before issuing "drop table" command.
> Thanks a lot.
> Murty

This is a perfectly logical think to want to do. So, don't be so harsh
with Keith. I think I have asked this question several times in
different ways to try to trick the correct answer out of someone.

Here is why this is important to have. We use connection pooling so you
may get a connection that has been used by someone else recently. The
problem is that we have code that has bugs where they don't drop their
temp tables or they may have errored out before dropping their temp
tables. So we end up leaking temp tables sometimes.

It would be nice to have a query that could give you a list of the
temporary tables for your session.

select * from syssesiontemp;

might be a nice system table for example.

This way the pooling code could clean up the session for the next user.
Nothing like a dirty session in the pool to mess things up.

Keith Simmons wrote:
> On 6 Dec 2006 01:18:15 -0800, scottishpoet wrote:
> > Could you expand on why do you need to test if it exists before you
> > drop it?
> >
> > If you just drop it and it doesn't exist the drop will have no effect
> > other than that a status other than 0 will be returned from the drop
> > command. If you don;t test that status there will be no problem.
> >
> >
> > Veeru71 wrote:
> > > Hi
> > > Is there a way to drop a temp table in Informix using some kind of "if
> > > exists" logic ?
> > > In oter words I need to test for the existence of the temp table
> > > before issuing "drop table" command.
> > > Thanks a lot.
> > > Murty
> >
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
> >
>
> Temp tables disappear at the end of the session anyway, so if you have
> created it in this session it will exist, if you haven't created it
> then it will not exist !!
>
> Keith

Thanks Bozon for articulating so clearly the need for the existence of
temp tables.
Somehow I feel that it is always much cleaner to check for existence
and drop it if exists
EXPLICITY, rather than firing the 'drop table' command and ignoring
the warning.
I could not find a solution though :)
- Murty

"Veeru71" wrote in message
news:1165830720.623257.143700@j72g2000cwa.googlegroups.com...
> Thanks Bozon for articulating so clearly the need for the existence of
> temp tables.
> Somehow I feel that it is always much cleaner to check for existence
> and drop it if exists
> EXPLICITY, rather than firing the 'drop table' command and ignoring
> the warning.
> I could not find a solution though :)
> - Murty
>

This is the query to find out whether temp table already exists
in your session.
SELECT count(*)
into w_count
from sysmaster:systabnames s,sysmaster:systabinfo i
where i.ti_partnum = s.partnum
and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
and s.tabname = 'your_tmp_table_name' ;


The query will return 1 if the temp table exists.

Veeru71 wrote:
> Thanks Bozon for articulating so clearly the need for the existence of
> temp tables.
> Somehow I feel that it is always much cleaner to check for existence
> and drop it if exists
> EXPLICITY, rather than firing the 'drop table' command and ignoring
> the warning.
> I could not find a solution though :)
> - Murty
>
Do you also test for existence of a data row before you attempt to
update it?
Makes for a good buffer pool hit ratio if nothing else...

I actually presented on that very topic at WAIUG last week.
FWIW, in a language that has condition handlers the code to drop a table
is a lot cleaner that that spattered with error-tests.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html