Problem with temp table

la46 · Jun 1, 2009

HI ALL,
I'm using a temp table in a fucntion to store temporary results, but when calling the same function i've a problem saing that the temporary table already exist. What can be the cause and what is the solution?
I use informix Online edition 11. in an AIX serever.

the function begins with
whenevere error continue
DROP TABLE temp_table1
DROP TABLE temp_table2
whenevere error stop

the second cause the problem.
Any Help?

olded · Jun 1, 2009

I don't mean to elaborate on the obvious, but years ago, I saw a similar problem in Informix stored procedures. The work around was I had to drop the temp tables before exiting the procedure.

la46 · Jun 4, 2009

Hi olded,
i've tried to drop the two temp tables before exiting the function with the same portion of code, but this works only the first time and i get the same problem after with also the second table, the first do not cause any problem!
Anyway Thank you.

mberni · Jun 12, 2009

Hi,

i do this with an exception handler like this:

Code:

create procedure mb_1();

ON EXCEPTION IN (-958)
drop table tmp_t1;
END EXCEPTION WITH RESUME;

create temp table tmp_t1 (field1 integer);
create temp table tmp_t1 (field1 integer);

end procedure;

The second create table fires error 958 which results in dropping the temp table.

la46 · Jun 18, 2009

Hi mberni,
i've used prepared statement when creating temp tables and i've no problem now. I did not tested yet your solution, but i will try it as soon as possible.
Thanks mberni.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem with temp table

la46

Programmer

olded

Programmer

la46

Programmer

mberni

IS-IT--Management

la46

Programmer

Similar threads

Part and Inventory Search

Sponsor