Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


check exist table in Informix

Posted on 1998-12-23
Medium Priority
Last Modified: 2012-08-13

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
Question by:dimk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1

Expert Comment

ID: 1026147
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.

Author Comment

ID: 1026148
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.


Expert Comment

ID: 1026149
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.

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 1026150
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 ?

Expert Comment

ID: 1026151
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.


Author Comment

ID: 1026152
Did the points enough ?
What information you can give me ?

Expert Comment

ID: 1026153

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

Expert Comment

ID: 1026154
Did you solve your problem dimk????

Author Comment

ID: 1026155
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


Accepted Solution

geowilli earned 200 total points
ID: 1026156
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.

Author Comment

ID: 1026157
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question