Re: Need MONMSG-thingy for SQL stored procedures

[CRPence <CRPbottle@xxxxxxxxx>]

http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyinvokeproc.htm
IBM i 7.1 Information Center -> Database -> Programming -> SQL 
programming -> Routines -> Stored procedures
_i Calling a stored procedure i_
"The SQL CALL statement calls a stored procedure.
 ...
Although procedures are system program objects, using the CALL CL 
command will not typically work to call a procedure. The CALL CL command 
does not use the procedure definition to map the input and output 
parameters, nor does it pass parameters to the program using the 
procedure's parameter style.
 ..."

  An additional consideration is that SQL works on a return-code basis, 
not messaging, so the information about the failures are all contained 
in the SQL routine; unavailable directly to the CL CALL as requester. 
What messages the routine logs can be retrieved by the CL after the CALL 
[with the means to get messages no longer on an active stack], but that 
is of little help and is really a poor idea.  Normally the routines will 
want to declare handler(s) themselves, but since the caller is CL, that 
still is of no help, because there is no return parameter nor return 
value that is set that can be directly received by the CLP; of course 
int the handler in the SQL routine, something could be done to make data 
available to the invoking CL, but the better option is to just use an 
SQL CALL.

  A generic external procedure could be written as embedded SQL [or the 
SQL CLI, which can be invoked from a CLLE] to issue the SQL CALL.  The 
procedure name could be a parameter, and the embedded SQL could be an 
EXECUTE IMMMEDIATE of a string composed by the expression [in pseudo-code]:
    StmtToExecute = 'CALL ' concat ParmAsProcName

  That program, having used the SQL CALL instead of the CL CALL, can 
then get the diagnostic information [e.g. just look at SQLCODE and 
SQLSTATE; or actual GET DIAGNOSTICS] about the CALL and feedback that to 
the CLP by sending a message to the CLP.

  Also, FWiW, the REXX has an SQL statement environment and a CL 
command environment.  The CL could be replaced with REXX and the CL CALL 
replaced with a SQL CALL, or the CL CALL could be replaced with a 
STRREXPRC which is defined to perform the SQL call [again probably by a 
parameter naming the procedure] and then "SNDPGMMSG msgdta(&sqlerrmc)" 
of the appropriate SQL#### or SQ##### message identifier as an escape 
message to the CLP.

Regards, Chuck

On 11 Apr 2013 11:22, Dan wrote:
> In our shop, we use SQL stored procedures to update our control tables at
> each step in development, QA, system testing, and production deployment.
> (FWIW, our control tables do most of the "driving" in our application and,
> as a result, some of our projects will have anywhere from a few hundred to
> a few thousand records added to our control tables.)
>
> We use separate SQL stored procedures for each table, and the collection of
> these for a given project is called from a CL program.  (Code for a very
> simple project is shown below.)  We would like an exception error thrown in
> the CL program when any statement in a stored procedure fails.  I tried
> MONMSG CPF0000 and MONMSG SQL0000 in the CL program, but apparently none of
> the errors (i.e. CPF5034 "Duplicate key on access path") are "returned" to
> the CL program.    I've found the "SQL control statements" chapter in the
> SQL reference, but still can't make heads or tails on how to do this.  IF
> POSSIBLE, I would prefer a "global" at-beginning-of-source MONMSG-thingy; I
> would like to avoid having to add code to every statement that tests the
> results of a statement.
>
> Member      Type        Text
> RUNDTASCR   CLLE        Data Deployment Script
> @CATUPD     SQLPRC      SQL Data UPDATE Script - CATMSTP
> @CVGINS     SQLPRC      SQL Data Insert Script - CVGDESP
>
> CL program RUNDTASCR:
>     Pgm
>       Call @CATUPD
>       Call @CVGINS
>       Commit
>     EndPgm
>
> SQL proc @CATUPD:
> CREATE PROCEDURE @CATUPD
> LANGUAGE SQL BEGIN
>     UPDATE CATMSTP
>        Set CVCGDESC = 'UNDERINSURED MOTORIST BODILY INJURY'
>        where CVCGCVGGRP = 'UNDERMBI' ;
> END;
>
> CREATE PROCEDURE @CVGINS
> LANGUAGE SQL BEGIN
> --
>     DELETE FROM CVGDESP
>      WHERE (CDST, CDCO, CDCVGCD, CDCVGLC, CDCVGDEDC) =
>            ('OH',24,'BI','100/300','');
>     INSERT INTO CVGDESP VALUES
>     ('OH',24,'BI','100/300','',
>       'BODILY INJURY', 'LIABILITY', '$100,000 EA PERSON', '$300,000 EA
> ACCIDENT';
> --
>     DELETE FROM CVGDESP
>      WHERE (CDST, CDCO, CDCVGCD, CDCVGLC, CDCVGDEDC) =
>            ('OH',24,'BI','50/100','');
>     INSERT INTO CVGDESP VALUES
>     ('OH',24,'BI','50/100','',
>       'BODILY INJURY', 'LIABILITY', '$50,000 EA PERSON', '$100,000 EA
> ACCIDENT';
> --
> END;