Re: SPL

[rob@xxxxxxxxx]

Stored procedures aren't really an object.  There may be API's to check 
for the existence of a stored procedure, but none jumped out and bit me. 
The list of stored procedures exists in the database cross reference 
files:
runqry qryfile(qsys2/sysprocs)
You can write your stored procedure that deletes/recreates this stored 
procedure in a HLL like RPG.  You could access that file to see if the 
stored procedure exists.  Then you can execute the DROP.  Or skip running 
the check and just run the DROP.  Unlike RUNSQLSTM you have more control 
over your stored procedure.  Check sql state if you care.  And then run 
your CREATE.

But, if you up the severity level on RUNSQLSTM, will that work?  RUNSQLSTM 
ERRLVL(40)?  But you might miss errors on the CREATE that way.  Or you 
could bust your RUNSQLSTM into 2 RUNSQLSTM's.


Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




Tom Wheeler <Tom.Wheeler@xxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/13/2003 09:21 AM
Please respond to Midrange Systems Technical Discussion
 
        To:     "'midrange-l@xxxxxxxxxxxx'" <midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        SPL


I call the RUNSQLSTM and the SPL drops the procedure but fails to create 
the
SPL because there is some syntax problem. 
The next time I run RUNSQLSTM I have to comment out the --DROP. 
 
Is there a way to condition the drop statement in a stored procedure? 
 
Thank you for any help with this.
 
***************************************************
DROP PROCEDURE HRCUST/LOAD_TR; 
 
--  GENERATE SQL 
--  VERSION:                    V5R2M0 020719 
--  GENERATED ON:               04/10/03 10:46:12 
--  RELATIONAL DATABASE:        MYSYSTEM 
--  STANDARDS OPTION:           DB2 UDB AS/400 
 
CREATE PROCEDURE HRCUST/LOAD_TR ( 
 OUT TR_CNT CHAR(10) ) 
 -- CALLED ON NULL INPUT 
 EXTERNAL NAME HRCUST/CUGSTGPTR 
 LANGUAGE RPGLE GENERAL 
 
 
Tom Wheeler @ ECS
 
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.