Re: Need MONMSG-thingy for SQL stored procedures

On 11 Apr 2013 12:22, Dan wrote:

BTW, I think I found something in the SQL Reference:
CREATE PROCEDURE UPDATE_SALARY_IF
(IN employee_number CHAR(6), INOUT rating SMALLINT)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SET rating = -1;

However, I would hate to think that I would have to define all of
the possible error conditions to use this. I'm still researching
whether there's any "global" error condition that can be utilized.

There is a global handler for SQLEXCEPTION. But as I alluded in a prior message, such monitors in the routine will do little good for the described scenario of a CLP invoking the routine with a CL CALL. There is no direct way to return the value of "rating" [from that example] to the CL, nor any messaging [and my experience trying, at least in old releases, had the SQL choking horribly; IIRC, sending a message to the program before QSQCALL on the stack]. Nor is that [returning parameter values] possible in a REXX SQL CALL, unfortunately :-( But an embedded SQL CALL [or SQLCLI] can get the diagnostics and\or INOUT or OUT return values. So if a level of indirection by adding an embedded SQL to perform the proper\expected SQL CALL to the stored procedure [routine] is acceptable, or using a CLLE vs CLP and SQL CLI to perform the SQL CALL is acceptable, then those interfaces should be able to get the SQLCODE and SQLSTATE [and more], and return some feedback to the CL being used to drive\control the activity.