Just define SQLSTATE (Char(5) ) or SQLCODE (SmallInt) within the stored
procedure and then use them like the SQLCODE or SQLSTATE variables within
embedded SQL?
i.e.
DECLARE SQLSTATE Char(5) Default '00000';
If SQLSTATE = '02000' Then Leave YourLoop;
End If;
Or
DECLARE SQLCODE SmallInt Default 0;
IF SQLCODE = 100 Then Leave YourLoop;
End If;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Freitag, 1. März 2019 00:24
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SPL question about error handling.
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
http://www.dekko.com
--
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://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
