Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2005
    Location
    Hottest place on the earth
    Posts
    13

    Unanswered: Dynamic temp table name in store proc

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    102
    You can avaoid this problem by not using a temp table. Seriously, how difficult to avaid a temp table?

  3. #3
    Join Date
    Nov 2004
    Posts
    143
    Hello,

    You can query the syssessions table in sysmaster db.

  4. #4
    Join Date
    Jun 2005
    Location
    Hottest place on the earth
    Posts
    13
    Can you please give me more details about syssessions table usage you are referring..

    Thanks a lot,
    Riya C

  5. #5
    Join Date
    Nov 2004
    Posts
    143
    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

  6. #6
    Join Date
    Jun 2005
    Location
    Hottest place on the earth
    Posts
    13
    My question is how to create tables with dynamic names appending session id in procedures

    TIA
    Riya C

  7. #7
    Join Date
    Feb 2005
    Posts
    43
    As far as I know, dynamic SQL in stored procedures is not supported by IDS.

  8. #8
    Join Date
    Sep 2002
    Posts
    102
    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.

  9. #9
    Join Date
    Oct 2005
    Posts
    6
    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

  10. #10
    Join Date
    Jun 2005
    Location
    Hottest place on the earth
    Posts
    13
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •