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