Re: RDS 4.14/SE 5.06 Error dropping TEMP tables

bashir Akram wrote: > > > I had problems with dropping a temp table, > We were getting similar errors, -242, and also table not found. > It seemed strange, moving from SE 4.x to SE 5.x would cause this. > > Does anybody know why this happens? whats changed? > > To fix this we had to rewrite the code, was there an easier work > around? I managed to find the reason for my problem. It appears that SE 5.x is a lot more *fussy* about the use of cursors than SE 4.x My problem was caused because, after using SELECT ... INTO TEMP tmp_table I was declaring a cursor to select rows from tmp_table but the cursor was not getting CLOSE'd before I tried to DROP TABLE tmp_table. Issuing a CLOSE CURSOR cursor_name prior to my DROP TABLE command cured the problem. Without this statement, the DROP TABLE was looking for the table name with a user name prefix so was returning TABLE NOT IN DATABASE because the SELECT ... INTO TEMP .. assigns the simple name of the table specified (no user prefix). Therefore, a subsequent SELECT .. INTO TEMP statement gave error TABLE ALREADY EXISTS because the DROP TABLE had failed. I hope this helps :-> Regards, Keith. compsys.demon.co.uk