Xavier Camier wrote : > >Hello, > >I attempt to create a stored proc which : > >1) SELECT some datas and put the to a cursor > >2) CREATES a temp table from the cursor >eg : CREATE TABLE TEMP.TOTO AS SELECT * FROM MYCURSOR > >3) Works on the TEMP.TOTO table. > > >My problems occurs when I try to launch the DBProc more than once. The >TOTO table already exists. I know that it's because the TEMP table is >only released at the end of the session. It means I have to drop it >either at the beginning other at the end of my stored proc > >I looked for a post to learn how to do but I found no answer. >That post >explains most of the things I was wondering : >http://lists.mysql.com/maxdb/20546. There's even an answer to my >question but I dislike the solution provided (creating a cursors which >reads the temp table and then simply dropping the table with the drop >statement). I also tried that query to tell the dbproc to decide if it >has to create the temp table or not : > >SELECT TABLENAME FROM DOMAIN.TABLES where owner = USER AND >tablename = 'TOTO'; >[http://lists.mysql.com/maxdb/21152] > >... But as the temp table is "session only", it does not >appear into the >domain.tables table. I couldn't locate it anywhere else (but there are >so many tables that I could have missed it). > >Could someone help me and explain me the best way to verify if a temp >table exists (so that the stored proc know if it has to create >it before >filling it) ? > >Best regards, Xavier > >
SELECT TABLENAME FROM DOMAIN.TABLES where owner = 'TEMP' AND tablename = 'TOTO'; will return a result, if the temp table exists. However, I would recommend just to try to drop the temporay table and to ignore the error -4004, if the table does not exist. Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
