Re: Error 242

Sylvain Cantara wrote: > > We have an SQL script generating an error message when executed > on an Informix-SQL 2.10.03K version. The same script executes > very well on a database with engine version 4.10. > > The script uses "into temp" to work with temp table. We use > the transaction loging. The first logical transaction goes well, > the error only happens within the second logical transaction. > The temp tables of the first transaction are all dropped before > the "commit work". We tried using the same names or different > names for the temp tables in each transaction, but that doesn't > change anything. > > The error message is: > > 242: Could not open database table (tseq3).> > 2: No such file or directory > Error in line 856> Near character position 0 > > 377: Must terminate transaction before closing database.> > The section of code creating that error looks like this: > > . > . > . > select something from some_table > where something = "aaa" > into temp tseq3> ; > > update some_table > set some_field = "1" > where something = "aaa" > and 1 = ( select count(*) from tseq) > ; > > ... > > This is not the real code but a simplified one to illustrate the problem. > The script is rather complex and lenghty (it is generated with ACE). > We suspect this is a bug with the older version. > > Any idea ? > Thanks. I have, in the dim and distant past, successfully dropped and recreated temp tables with 2.10. One problem I remember is that the newly recreated table came into existence already containing the data from its past life! I had to DELETE from it before dropping it. Perhaps you could try this. I rather think I must have used an explicit CREATE rather than an implicit one. Again, perhaps you could try that. Ian