IDS Forum

Re: Random name for temp table

Posted By: Marcus Haarmann
Date: Thursday, 20 March 2014, at 3:29 a.m.

In Response To: Random name for temp table (VIKAS HIVARKAR)

Hi,

maybe you can make a sequence, which you should increment at the start of your routine (remember the value) and then
build a dynamic sql which creates your table, the counter appended to the table name.
Remember, you could get out of temp dbspace depending on how many calls you make.

In case you need the table only inside the stored procedure, you could just add an internal column where you save the sequence
value in each insert from the actual call and then use it in any query which reads the data as filter.

Or (depends on how often the procedure is used) drop it at the beginning of your procedure.
The next call could drop tab results of the call before in this case, and a non-finished call
could fail, which is not desirable.
You could also try to synchronize the calls (the second one has to wait until the first one is finished),
using a separate table and place an explicit lock there for your transaction. (create (non-temp) table if not existing,
insert a dummy record, update the dummy record to have a lock (hope you have lock mod wait)).

But this is already dangerous when parallel users work on a single JDBC connection, this is mostly not thread safe
and you can get very interesting effects in the application.

Hope this helps,

Marcus Haarmann

----- Ursprüngliche Mail -----

Von: "VIKAS HIVARKAR" <vikas.hivarkar@gmail.com>
An: ids@iiug.org
Gesendet: Donnerstag, 20. März 2014 07:41:09
Betreff: Random name for temp table [32752]

Hi All,

Informix 12.10.FC2 on HP Unix 11.31

I have a stored procedure running file for 2 years which creates a temp table
with no log

CREATE TEMP TABLE vikas(
v_sr_no SMALLINT,
v_label VARCHAR(200),
v_text LVARCHAR(2000) default null) WITH NO LOG;

Now One of our developers have written a java code for GUI based application
that calls this procedure and for some reasons he has used a single session
polling login that opens a single session to the database and all the users
use this session.

There starts the trouble as the temp table is session based it cannot be used
by mutiple users and the funtion fails

Until the connection logic is fixed, Is there any way I can modify the stored
procedure to create a temp table with random name instead of the hard coded
"vikas" name

I was looking for the funtion DBMS_RANDOM_RANDOM but not sure how to use it
also there is "IF NOT EXISTS" but I dont know if it has any relevance with my
issue.

Regards,

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Random name for temp table
  VIKAS HIVARKAR -- Thursday, 20 March 2014, at 2:41 a.m.
  • Re: Random name for temp table
    Marcus Haarmann -- Thursday, 20 March 2014, at 3:29 a.m.
  • Re: Random name for temp table
    Art Kagel -- Thursday, 20 March 2014, at 7:23 a.m.
    • Re: Random name for temp table
      VIKAS HIVARKAR -- Thursday, 20 March 2014, at 2:16 p.m.
      • Re: Random name for temp table
        Marcus Haarmann -- Thursday, 20 March 2014, at 2:25 p.m.
      • Re: Random name for temp table
        Art Kagel -- Thursday, 20 March 2014, at 2:30 p.m.
        • Re: Random name for temp table
          VIKAS HIVARKAR -- Thursday, 20 March 2014, at 4:03 p.m.
          • Re: Random name for temp table
            Art Kagel -- Thursday, 20 March 2014, at 4:29 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.