Filip Sergeys wrote : >Hello,
>I need to use temporary tables I my dbproc due to the need of updates >(updates on cursors are not possible) >Since the application that uses this dbproc uses connection pooling I >cannot be sure that after every execution the session is ended and >temporary table deleted. It is also possible that the dproc is executed >multiple times concurrently. >Thus I need unique temporary tables names and I delete them manually at >the end (or in the catch block) to be sure they are gone. >I therefor created a sequence and wanted to use the sequence number for >the temporary tablename. I fetch that number in a variable an construct >the temporary tablename with that variable. >THis is where it goes wrong. >The dbproc won't compile because at compile time the variable has no >value yet, so the "CREATE TABLE TEMP.:VARIABLE" fails >(General error;-3009 POS(2583) Invalid table name.) >Which is kind of normal. But how do I solve this? I tried to find an >answer in the documentation but could not find something usefull. >Suggestions? (Google wasn't very cooperative either) You have to create your table via dynamic sql. Here is an example : VAR tablename char(32); sqlStmt char(200); tablename = 'TEMP.TEMPTABLE' || CHR(seq.nextval); sqlStmt = 'CREATE TABLE ' || tablename || ' (COL1 INTEGER, COL2 CHAR(10))'; execute sqlStmt; IF $RC = 0 THEN .... Regards, Thomas -- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* * System Engineer, Verzekeringen NV * * www.verzekeringen.be * * Oostkaai 23 B-2170 Merksem * * 03/6416673 - 0477/340942 * *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
