check exist table in Informix


In informix coding,
while online program ruuning,
How to check whether a temporary table exist in database,
so that it can be drop before create another temporary with the same table name, to avoid duplicate.

end hi
Who is Participating?

Improve company productivity with a Business Account.Sign Up

geowilliConnect With a Mentor Commented:
If the table is truly a temproray table, there is no need for you to be concerned about dropping it at all.  Informix automatically takes care of the house keeping for temporary tables. As a matter of fact, the temporary tables do not even show in the table listings.

To confirm this, perform the folling test:
1. develop a query that selects information into a temporary table and then uses that temporary table in a subsequent select statement.
   eg. select customer.* from customer
           where customer_number = 121
           into temp customer_temp;

       select customer_temp.*, sales.*
           from customer_temp, sales
           where customer_temp.customer_number = sales.customer_number

You will note that the table customer_temp will be nowhere in the table listing.  You will also note that several users can run the very same query using a different customer number and there will be no problems even though they are all creating a tempory table with the same name as part of the running the query.
The only way to access a temporary table is from the same session it was created in. So if you are doing all of this from one database session you must drop the temporary table before you can use it again.

Once that session is finished then the temporary table is gone. If you start a different session and use the same temporary table name there will not be a problem because one session cannot see the other session's temporary tables.
dimkAuthor Commented:
hi, holz_13;

I understand what u meant. But, what if the transaction in the same database session.
I should drop the table before I create another table with the same table name, before I drop the table, I should known whether the table is exist in the database.
The question is here ...
How to check whether a temporary table exist in database ?
so that it can be drop before create another temporary with the same table name,
to avoid duplicate.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

hi dimk

If you are doing everything in the same session the the only way is to issue a drop table command before you try to create another temp table with the same name. You are not allowed to use the same temp table name in the same session, so it must be explicitly dropped before you can re-use the name during the same database session.

dimkAuthor Commented:
ya, I can issue a drop table command.
but, what if the temporary table is not exist. the drop non-exist table command may cause program crash.
That's the reason I would like to know ...
How to check whether a temporary table exist in database ?
I can't spend anymore time on this for the small amount of points that you have given it.

So, to be perfectly clear, the is no way to tell from inside a program if a temp table exists or not. If you want more of an explanation you need to increase the points.

dimkAuthor Commented:
Did the points enough ?
What information you can give me ?

i use inforix for a while (1 year aprox) and the only way to do that is with a command that desactivate the crash program

the command is whenever error

you can desactivate the crash
drop your table
and reactivate your errors

if the table dont exists
the functions returns you a parameter indicates that the table dont exists, i use this command to verify existings files to load and it works

waiting for points!!!!
Did you solve your problem dimk????
dimkAuthor Commented:
To lgmm;
 sorry for late reply, because I was outstation past three weeks.
 I use...
        DROP TABLE temptable15
        #this work. how to reactivate errors ?

To holz_13;
 If no comment then
     I'll reject your amswer
     pls comment
 end if

dimkAuthor Commented:
Informix will run house keeping on temporary table after program terminated.
In my case, the same name of temporary table needed to be re-create within program single run.  I believe Igmm have hint the right, can you commend further.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.