Creating an SQL procedure seems to check whether the statements w ill work

["McCallion, Martin" <martin.mccallion@xxxxxxxxx>]

Hi list, 

I'm trying to create an upgrade function using SQL.  I want it to be
re-runnable, such that if a client has run it over their database, they can
run it again without harm, and without it failing.

I need to add columns to a table, so I want to do an ALTER TABLE ... ADD
COLUMN.  But first I want to check whether the column already exists on the
table.  The only way I've found to do that is to interrogate the SYSCOLUMNS
view.  Now you can't so that in a script run with RUNSQLSTM, but you can do
it in a stored procedure.

So my upgrade strategy involves a CL which uses RUNSQLSTM to create a
procedure, then calling that procedure via RPG and embedded SQL (this last
twist is to allow me to pass parameters to the stored procedure, which you
can't do from CL).

It works OK when the new columns don't exist.  But when I try running it
again, I get an error on creating the stored procedure.  Not running it:
creating it.  And the creation fails because the new column already exists.

Which strikes me as bizarre.  It seems that the SQL compiler actually tries
to run the statements that comprise the script, not just syntax check them.
Is this normal behaviour, and can anyone think of a way round it?

Here's a cut-down version of the script that creates the procedure; note
that I've tried various ways of testing for the (non-) existence of the
column, including using the EXISTS and IN predicates.  They all give the
same error, which is also shown below.  We're at V5R4.


    CREATE PROCEDURE damm4db.UPGRADEPRC (
        IN DB CHAR(10), 
        IN ARC CHAR(10)
    )
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    
    /* Only add these columns if they're not there... */
    
    DECLARE COLNAME CHAR(130);
    
    SELECT 
        COLUMN_NAME 
    INTO
        COLNAME
    FROM 
        damm4db.SYSCOLUMNS
    WHERE
        TABLE_NAME = 'MESSAGES' AND COLUMN_NAME = 'ELECTRONICBROKERID';
        
    IF
        COLNAME IS NULL OR COLNAME = ''
    THEN
        ALTER TABLE 
            damm4db.MESSAGES
        ADD 
            ELECTRONICBROKERID
        FOR COLUMN ELECBROKID VARCHAR (32) ;
    END IF;
    
    END 



> From the end of the generated listing:

        SQL0612  30      66  Position 9 ELECTRONICBROKERID is a duplicate
column name.

Cheers and TIA,

Martin.