Solved

check exist table in Informix

Posted on 1998-12-23
11
1,733 Views
Last Modified: 2012-08-13
hi

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
0
Comment
Question by:dimk
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 1

Expert Comment

by:holz_13
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.
0
 

Author Comment

by:dimk
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.


0
 
LVL 1

Expert Comment

by:holz_13
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.

holz_13
0
 

Author Comment

by:dimk
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 ?
0
 
LVL 1

Expert Comment

by:holz_13
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.

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

Expert Comment

by:lgmm
ID: 1026153
dimk

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

Expert Comment

by:lgmm
ID: 1026154
Did you solve your problem dimk????
0
 

Author Comment

by:dimk
ID: 1026155
To lgmm;
 sorry for late reply, because I was outstation past three weeks.
 I use...
        WHENEVER ERROR CONTINUE
        DROP TABLE temptable15
        #this work. how to reactivate errors ?

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


TQ;
dimk
0
 
LVL 1

Accepted Solution

by:
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.
0
 

Author Comment

by:dimk
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now