check exist table in Informix

Posted on 1998-12-23
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.

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 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