New to Informix...
In our application one functionality is..
When user requests some data through web, procedure will be executed backend which selects certain set of rows into one temporary table (say t1) for processing the data further to present to user. Once data is processed temp table will be dropped. However when more than one user requests the same functionality, select into temp table fails for one user with sql code 958.
To avaoid this we are thinking of creating temp table appended with sid. But I do not have idea how to create a table with dynamic name in store procedure.
Any pointers plz..or any other alternate approach to the problem?
technologies are java using websphere and Informix 9.30.FC3X1 on Unix box
Here is a workaround. Create the a table for tempory data storage as raw table (No logging table). Include in the schema, a session_id column. Populate the table instead of creating a temp table including the ssion id ( DBINFO('sessionid')).
Be sure to delete the data for that session after processing. You can optionally include a datetime column to track if the data is old, in case data didn't delete.
Download and install the ExecIt DataBlade which lets you generate dynamic SQL. For example, using the demo stores database you could create this, not overly useful FUNCTION, but it serves to illistrate the point.
CREATE FUNCTION createDynamicTempTable() RETURNING CHAR(30);