Re: Handling temporary tables in Informix

Suket wrote: > My Informix 4gl program has functions, which: > 1) Creates a temp table, loads data into it > 2) Access the temporary table > > Now in 2) , there is a line which resets connection to database by : > DATABASE <database name> > Note that this database name is same as used in 1) > > Now the program gives SQL error 206 for table not found at runtime. > > My query is whether this behaviour ( automatically dropping temporary > tables, when database connection is reset) is consistent for Informix > versions 7.2/7.31/9/3. > > Is there is any way to handle this. > > Currently using Informix 9.3 on HP-UX 11. And which version of I4GL? Other people gave you the 'hard luck' story -- I'm going to give you a couple of alternative comments that might help. First of all, in the days of SE's dominance, there was a trick which people pulled with temp tables which did involve closing a database with a temp table and then opening another database and relying on a bug in SE which kept the table around between the two. However, IMNSHO, it was never anything other than a bug and never worked reliably with OnLine or IDS (AFAICR). And it didn't work reliably with SE if you weren't using local databases. Secondly, you probably can connect to the database multiple times, using the CONNECT statement (probably WITH CONCURRENT TRANSACTIONS). However, you cannot access a temp table on one connection inside another connection - but you can pull data out of the temp table on connection1 into memory, and then push it back to the database on connection2. If there's a single database involved as stated, this is pretty pointless -- don't go disconnecting and reconnecting if you want to keep your temp tables around. But there can be circumstances (such as database1 is logged and database2 is unlogged) where you cannot do distributed operations between the two. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/