CREATE TABLE even if tables already exists

[gonen]

Hi

I need to create a table, that might already exist.

I would like to have a call to procedure , with the table name, so that procedure will DROP the table (exists or not) and let me continue with the CREATE

I worte a code likes this:

CREATE PROCEDURE DRPTBLRI (P_TBL_NAME CHAR(10))       
                                                      
LANGUAGE SQL                                          
                                                      
BEGIN                                                 
DECLARE M_SQL_STM  CHAR(512)   NOT NULL  DEFAULT  ''; 
DECLARE AT_END     SMALLINT    NOT NULL  DEFAULT  0 ; 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION              
SET AT_END = 1;                                        
                                                       
SET M_SQL_STM = 'DROP TABLE ' CONCAT P_TBL_NAME;       
EXECUTE IMMEDIATE M_SQL_STM;                           
                                                       
END

the procedure is called like this:

    CALL DRPTBLRI('MSERNTOT');                              
    CREATE TABLE MSERNTOT                                            
     ( .....

When I run it foreground - it works perfect. (if the table exists, it drops it and continue with the create - if it does not, it ignores the *FILE not found error and continue with the create

When I run it in job (with same libl/jobd etc.) - the process work fine only if the table already exists. If it does not, SQL error - *FILE NOT FOUND is received.

my question:

1. why good in foreground and bad in batch
2. can I have a sample other procedure to drop a table regarding its existance and let the create continue


thanks !!!

[littlepd]

Re: CREATE TABLE even if tables already exists

Could you include a SELECT statement that queries the system catalog for the existence of the file before you attempt to perform the DROP? Then, if the file is there, drop it. If not, skip to the CREATE.

Just a thought.
Michael

[Mercury]

Re: CREATE TABLE even if tables already exists

I'd create an SQL function instead to avoid return errors concerning the stored procedure.

DROP FUNCTION DRPTBLRI;

CREATE FUNCTION DRPTBLRI (P_TBL_NAME VARCHAR(10))
   RETURNS INT
   LANGUAGE SQL
   MODIFIES SQL DATA

BEGIN
  DECLARE M_SQL_STM  CHAR(512)   NOT NULL  DEFAULT  '';
  DECLARE table_not_found CONDITION FOR SQLSTATE '42704';
  DECLARE EXIT HANDLER FOR table_not_found
      RETURN -1;

  SET M_SQL_STM = 'DROP TABLE ' CONCAT P_TBL_NAME;
  EXECUTE IMMEDIATE M_SQL_STM;
  RETURN 0;

END 

I'd run it in a program with SQL embedded as

d RC              s              5i 0 inz
Exec SQL SET :RC = DRPTBLRI([I]TableToDrop[/I]);

Then I'd check optionally the RC value
0 table existed in the LIBL
-1 table doesn't exist in the LIBL

[gonen]

Re: CREATE TABLE even if tables already exists

thanks for the tip.

this solved the problem:

CALL DRPTBLRI('KUKU2');            
   CREATE TABLE KUKU2                        
    ( .....

the DRPTBLRI procedure

CREATE PROCEDURE DRPTBLRI  (P_TBL_NAME CHAR(10))    
                                                    
LANGUAGE SQL                                        
                                                    
BEGIN                                               
DECLARE DRPRC   SMALLINT    NOT NULL  DEFAULT  0 ;  
SET DRPRC = DRPTBLRIF (P_TBL_NAME);                 
END

and the function:

CREATE FUNCTION DRPTBLRIF (P_TBL_NAME VARCHAR(10))          
   RETURNS SMALLINT                                         
   LANGUAGE SQL                                             
   MODIFIES SQL DATA                                        
BEGIN                                                       
DECLARE M_SQL_STM  CHAR(512)   NOT NULL  DEFAULT  '';       
DECLARE AT_END     SMALLINT    NOT NULL  DEFAULT  0 ;       
                                                            
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION                   
SET AT_END = 1;                                             
                                                            
SET M_SQL_STM = 'DROP TABLE ' CONCAT P_TBL_NAME;            
EXECUTE IMMEDIATE M_SQL_STM;                                
RETURN 0;                                                   
END

thanks !!