I have a problem with SELECT from temp table in informix (from PHP script, connect by ODBC). Stored function creates temp table (tmp_table) and after that i try select from temp table, but there is an error: [Informix][Informix ODBC Driver][Informix]The specified table (tmp_table) is not in the database.

Execution of function and SELECT both are in the same session.

If i do it by Aqua Studio, it's all OK.

Can somebody help me with this problem?

Temporary tables reside on a single process id / session id. What's probably happening is that you're either using some kind of connection pooling or disconnecting and reconnecting the connection when trying to select from that temporary table (and because your session id has changed, the table no longer exists for you). Probably the simplest solution is not to use a temporary table (use a permanent one), because it could take a while to figure out what's going on!

or just solve the problem with the connection...

I don't recommend create a permanent table, because this can overhead your database, generating consequences for your backup and database performance.

In the worst situation, if at the moment there is no other way to solve the connection problem, create the permanent table as RAW table , this way you not will log this tables unnecessarily ...

bigcalm, ceinma, thank you for your answers.

Unfortunately, i haven't got permission to modify stored procedure and create new tables in database...

May be some other way exists?

Hi lexacccp,

Did you find a solution? I'm having the same issue. I upgraded to Informix Client SDK 3.50.TC4DE and got the same message.

Thanks

David

I accept with information: In the worst situation, if at the moment there is no other way to solve the connection problem, create the permanent table as RAW table , this way you not will log this tables unnecessarily .

DavidDavis, hi!

The problem was in option "Cursor Behavior" (Configure -> Environment).
Change it from "0-Close" to "1-Preserve".