Re: Need MONMSG-thingy for SQL stored procedures

[Dan <dan27649@xxxxxxxxx>]

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.