Fw: Error trapping in RUNSQLSTM

I received this reply off list, other than one small bug introduced when 
the code was cleaned up for my perusal, it looks pretty good.
To fix the bug change
     and trim(SPECIFIC_NAME)=trim(code);
to
     and trim(SPECIFIC_NAME)=trim(name);

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)

----- Forwarded by Rob Berendt/DEKKO on 01/13/2004 04:53 PM -----

"Himes, Jay" <jehimes@xxxxxxxxxxx> 
01/13/2004 02:54 PM

To
"'rob@xxxxxxxxx'" <rob@xxxxxxxxx>
cc

Fax to

Subject
RE: Error trapping in RUNSQLSTM






I did indeed replay to the list - but I have had posting problems in the
past with my replys disappearing into thin air; which is why I copied you
directly. You are welcome to post the information to the list if it does 
not
show up.

________________________________

From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, January 13, 2004 2:44 PM
To: Himes, Jay
Subject: RE: Error trapping in RUNSQLSTM



Excellent, please reply via the list.  I'd like to share that with others. 

(You probably already replied via the list and I am just waiting for the
turnaround.) 

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




"Himes, Jay" <jehimes@xxxxxxxxxxx> 

01/13/2004 02:37 PM 

 
To
                 'Midrange Systems Technical Discussion' 
<midrange-l@xxxxxxxxxxxx> 
cc
                 "'rob@xxxxxxxxx'" <rob@xxxxxxxxx> 
Fax to
 
Subject
                 RE: Error trapping in RUNSQLSTM

 




I have had the same problem - I created a stored proc (code below) for
dropping items which I call at the beginning of each SQL member.

Start of a member for creation:
                CALL LBUCMUSR/DRPCODE('LBUCMUSR', 'GETRELSEQ', 'F'); 
                 CREATE FUNCTION lbucmusr/GETRELSEQ(ID1 INT)  
                   RETURNS Numeric(5,0)  
                  LANGUAGE SQL READS SQL DATA  
                  S: BEGIN 
                BLAH BLAH 
 
The stored procedure:
create procedure lbucmusr/DRPCODE ( in lib char(10), in name char(10), in
type char(1) )

LANGUAGE SQL MODIFIES SQL DATA

S: BEGIN

  Declare ct      Int Default 0;

  Declare tsql     char(45);

  select count(*) into ct from sysibm/ROUTINES

   where trim(SPECIFIC_SCHEMA)=trim(lib)

     and trim(SPECIFIC_NAME)=trim(code);

  IF (ct > 0 and type = 'P') then

    set tsql = 'drop Procedure ' || trim(lib) || '/' || trim(name);

  END IF;

  IF (ct > 0 and type = 'F') then

    set tsql = 'drop Function ' || trim(lib) || '/' || trim(name);

  END IF;

  IF (ct > 0) then

    PREPARE drp from tsql;

    EXECUTE drp; 
 END IF; 
END S;


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, January 13, 2004 12:02 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: Error trapping in RUNSQLSTM

I have a source member that I want to use in RUNSQLSTM that looks like:
DROP FUNCTION ROUTINES/STRIPEMAIL;

CREATE FUNCTION ROUTINES/STRIPEMAIL (CHAR (512)) RETURNS CHAR (100)
DETERMINISTIC NO SQL RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION ALLOW
PARALLEL SIMPLE CALL EXTERNAL NAME 'ROUTINES/SRVEMAIL(STRIPEMAIL)';

However the function may or may not already exist.  If it does not exist 
the
RUNSQLSTM aborts.  How does one do error trapping in RUNSQLSTM?  Keep in
mind the following:
DSPMSGD RANGE(SQL0084) MSGF(QSQLMSG)

Rob Berendt
--
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)
_______________________________________________
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.