RE: SPL question about error handling. -- MIDRANGE-L

I would start by not handing errors to help sort out those I might want to trap.

I might start with a statement like this
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
Then add the specific error state encountered, it should be in the job log as long as the job has not ended. You can name the handler or leave it generic like the line above. I have not done this before but I believe you can also have more than one handler defined, its first come first serve I believe. If you define a named handler with no action code I believe its like MONMSG CPF0000 or MONITOR with no error code defined.

Search for " handler-declaration" in SQL Reference (for me is page 1617 in the 7.3 PDF reference).

Here is a function I put together with help from others a while ago. Error handling should be the same.

CREATE FUNCTION YYMDTODATE ( DATENUM DECIMAL(8, 0) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC DATEUTLF10
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
DATFMT = *ISO,
COMMIT = *NONE ,
DBGVIEW = *SOURCE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX

/* Begin the ParmCheck group and validate the Date. */
DATEUTLF10: BEGIN
/* note: Variables inside Taged "Begin" statements can
be debuged using "Tag.VarName".
So you can debug using field EVAL DATEUTLF10.Varname
*/

/* Declare work date to validate the passed character value. */
DECLARE YYMDDATE DATE DEFAULT '0001-01-01';
DECLARE CHARDATENUM CHAR ( 8 ) ;
DECLARE MSGERROR CHAR(70);

BEGIN
/* Setup a condition handler to deal with the date error
* we may get from the SET statement.
*/

DECLARE DATE_ERROR CONDITION FOR SQLSTATE '22007';
DECLARE EXIT HANDLER FOR DATE_ERROR
BEGIN
SET MSGERROR = 'YYMDTODATE input in error: ' || DIGITS(DATENUM);
SIGNAL SQLSTATE '01H99' SET MESSAGE_TEXT = MSGERROR;

SET YYMDDATE = '0001-01-01';
RETURN NULL;

END;

/* Use a set statement to cause SQL to validate the date for us.
* This returns SQLSTATE '22007' for an invalid date.
*/
SET CHARDATENUM = DIGITS ( DATENUM ) ;
SET YYMDDATE = DATE ( SUBSTRING ( CHARDATENUM , 1 , 4 ) CONCAT '-' CONCAT
SUBSTRING ( CHARDATENUM , 5 , 2 ) CONCAT '-' CONCAT
SUBSTRING ( CHARDATENUM , 7 , 2 ) ) ;
RETURN YYMDDATE ;
END;
END DATEUTLF10;

COMMENT ON SPECIFIC FUNCTION &LIB/DATEUTLF10
IS 'YYMDTODATE - convert a numeric date to real date ';

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, February 28, 2019 5:04 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

How would I trap the insert failure?
I appreciate you coming back to trying to help me with my basic grasping of error handling in SPL instead of only going with the single statement to do all processing.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Tyler, Matt
Sent: Thursday, February 28, 2019 6:50 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

But my response was not what you were asking for. I believe the condition handler you wrote is only effected by the fetch statement. The insert is thronging something else.

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Tyler, Matt
Sent: Thursday, February 28, 2019 4:38 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SPL question about error handling.

Rob, If you want to skip the main table data then INNER JOIN the yetanothertable and you omit inserting when not match in yetanothertable.

-Matt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, February 28, 2019 4:36 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SPL question about error handling.

Rob,

Why do you need a cursor for this?

instead of
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from YetAnotherTable));

does this work?
Insert into AnotherTable
values (fetchColumn1, fetchColumn2, (select * from YetAnotherTable));

Charles

On Thu, Feb 28, 2019 at 4:23 PM Rob Berendt <rob@xxxxxxxxx> wrote:

Newbie to SPL.
Really trying to get the basics of error handling.

Basically I have a loop
Declare c1 cursor for select ...
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET ROW_NOT_FOUND = 1;
Open c1;
Fetch c1 into fetchColumn1, fetchColumn2...
SET END_CURSOR = ROW_NOT_FOUND;
WHILE END_CURSOR = 0 DO
Insert into AnotherTable (
Select fetchColumn1, fetchColumn2, (select * from
YetAnotherTable));
Note: rows are often not found in YetAnotherTable. In that case I
just want to skip this insert and fetch the next row from the cursor.
Set row_not_found = 0;
Fetch c1 into fetchColumn1, fetchColumn2...
Set END_CURSOR = row_not_found;
End while;
Close C1;
End p1;

The problem is that when it hits the missing row in YetAnotherTable,
it sets SQLCODE, SQLSTATE, ROW_NOT_FOUND, exits the loop and the procedure.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com&d=Dw
ICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2
RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhj
gw&s=qGags8G3G70hJOc_2F9pBtVwcQuLm45V8h6RkCrEjUU&e=

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFg
BU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=b826DjC04pvfzpMiLZNSx1uEuXz7L
xPxhynsnucfoGA&e= or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre97RdfUhjgw&s=9L_APWNJZMl_AlBQWNsMCHo1UqQwEaK3e60A_9EwxBM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.c
om&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8
HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=tVqFgBU4Iom-g-HKXo4g0eXzCxJtJRRre9
7RdfUhjgw&s=UpTDzrbNLtQmcFmtf2XeZyzNkxLa11pLIRjt222c8FE&e=