How to drop a temp table using "if exists" logic ?
All 5 messages in topic - view as tree
From: Veeru71 - view profile
Date: Wed, Dec 6 2006 6:18 am
Email: "Veeru71"


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

Reply » Rate this post: Text for clearing space


From: scottishpoet - view profile
Date: Wed, Dec 6 2006 11:18 am
Email: "scottishpoet"

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.



- Hide quoted text -
- Show quoted text -

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


Reply » Rate this post: Text for clearing space


From: Keith Simmons - view profile
Date: Wed, Dec 6 2006 12:49 pm
Email: "Keith Simmons"

On 6 Dec 2006 01:18:15 -0800, scottishpoet wrote:



- Hide quoted text -
- Show quoted text -

> 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-l...@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


Reply » Rate this post: Text for clearing space


From: Doug Lawry - view profile
Date: Wed, Dec 6 2006 1:28 pm
Email: "Doug Lawry"

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...


- Hide quoted text -
- Show quoted text -

> 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


Reply » Rate this post: Text for clearing space


From: bozon - view profile
Date: Wed, Dec 6 2006 4:45 pm
Email: "bozon"


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.



- Hide quoted text -
- Show quoted text -

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-l...@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



Reply » Rate this post: Text for clearing space


End of messages