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

TIA
Riya

You can avaoid this problem by not using a temp table. Seriously, how difficult to avaid a temp table?

Hello,

You can query the syssessions table in sysmaster db.

Can you please give me more details about syssessions table usage you are referring..

Thanks a lot,
Riya C

Hi Riya,

You can run query :
select distinct DBINFO('sessionid') from systables

to find your session id and then you can name your temp table dynamically.
I hope it helps.

Bye

Nitin

My question is how to create tables with dynamic names appending session id in procedures

TIA
Riya C

As far as I know, dynamic SQL in stored procedures is not supported by IDS.

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);

DEFINE returnValue LVARCHAR ;
DEFINE dynamicQuery LVARCHAR ;
DEFINE v_company CHAR(30) ;
DEFINE aValue INTEGER;

LET aValue=1;
LET dynamicQuery="SELECT * FROM customer INTO temp a" ||
aValue || ";";

CALL Exec(dynamicQuery) RETURNING returnValue ;

FOREACH SELECT company INTO v_company

FROM customer
RETURN v_company WITH RESUME ;
END FOREACH ;

END FUNCTION ;


This will create a temp table called a1 (the result of String Cat above). You could cat together any list of values, up to 128 to create a temp table name.

Hope that helps

Thanks, hoopsm !!! Good work around.

However I cannot install Exec on our unix boxes. I removed all tmp tables in the queries and using cursors for all

~Riya C