Problem with temp table

[la46]

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]

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]

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]

Hi,

i do this with an exception handler like this:

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]

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.