Re: Need MONMSG-thingy for SQL stored procedures

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;