Re: Need MONMSG-thingy for SQL stored procedures

Thanks for the reply, Gary.

In the example I tried, there were four messages in the job log, none were
escape messages:

CPF5009 Diagnostic "Duplicate record key in member CVGDESP."
CPF5034 Sender copy "Duplicate key on access path."
CPF5034 Notify "Duplicate key on access path."
SQL0803 Diagnostic "Duplicate key value specified."

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.

- Dan

On Thu, Apr 11, 2013 at 3:11 PM, Monnier, Gary <Gary.Monnier@xxxxxxxxx>wrote:

You may need to search the program and/or job messages. What is CPF5034
being sent as? Diagnostic? Escape? Is there any message sent after
CPF5034?


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, April 11, 2013 11:23 AM
To: Midrange Systems Technical Discussion
Subject: Need MONMSG-thingy for SQL stored procedures

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;
--
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.

--
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.