Re: sql continue handler

I think someone wrote on this list that they always use RPG to create
their procedures. I'm not a big fan of that. I'd put my procedure code in
their own member and then just use RUNSQLSTM.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay
Vaughn
Sent: Wednesday, August 24, 2022 9:33 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sql continue handler

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


However my new issue is...

When I issue my create trigger statement in strsql (with Robs suggestion),
it creates the trigger pgm fine and it works correctly when invoked.

However, when that same (and I promise you the exact same) sql statement is
built dynamically in an sqlrpgle pgm, it will not create and I get a syntax
error...
I have copy pasted to a text comparison tool and both statements match
exactly...

why would the sqlrplge treat it differently than strsql (realize the rules
db2/sql could make a difference but those are the same)...

the sqlrpgle error when trying to create the trigger is...

Message ID . . . . . . : SQL0199 Severity . . . . . . . : 30

Message type . . . . . : Diagnostic

Date sent . . . . . . : 08/24/22 Time sent . . . . . . :
09:40:53


Message . . . . : Keyword END not expected. Valid tokens: ;.

Cause . . . . . : The keyword END was not expected here. A syntax error
was
detected at keyword END. The partial list of valid tokens is ;. This
list
assumes that the statement is correct up to the unexpected keyword. The

error may be earlier in the statement but the syntax of the statement
seems
to be valid up to this point.

Recovery . . . : Examine the SQL statement in the area of the specified

keyword. A colon or SQL delimiter may be missing. SQL requires reserved

words to be delimited when they are used as a name. Correct the SQL

statement and try the request again.



However that same END is in the statement that works in STRSQL.

Jay

On Wed, Aug 24, 2022 at 9:21 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

I tried this structure already - did not work...

sidenote... Why did you use a case/when instead of a if/then for 1
conditional check?

Jay

On Wed, Aug 24, 2022 at 9:18 AM Rob Berendt <rob@xxxxxxxxx> wrote:

Maybe what it should be formatted to is:

Myproc: BEGIN

DECLARE Already_Mylib INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '38501'
Set Already_Mylib = 1);

CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');

-- Do your processing here

CASE
WHEN Already_Mylib = 0 THEN
CALL QSYS2.QCMDEXC('RMVLIBLE LIB(myLib)');
END CASE;

END Myproc;

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Rob Berendt
Sent: Wednesday, August 24, 2022 9:05 AM
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: RE: sql continue handler

What is the goal? To ensure that not only is MYLIB in the library list,
but it's the last entry in the library list?
If it's already in the library list remove it and ensure that it's at

the

end?

Normally the logic in an all CL situation is I just want to be able to
use the objects in the library so I:
Try to add the library to the library list
Do my process
If I added the library to the library list just to do my process then
remove it now. If it was already there then leave it there.

RMVLIBLE may not always remove a library from your library list. For
example if it is your CURRENT library.

But let's talk about your continue handler. I'm a newbie but I think
it's all wrong.
BEGIN is used at the beginning of the stored procedure. Not at the
beginning of a continue handler.

So I find this confusing:

BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '38501'
CALL QSYS2.QCMDEXC('RMVLIBLE LIB(myLib)');
END;
CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');

So let's reformat it to:
Myproc: BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE '38501'
CALL QSYS2.QCMDEXC('RMVLIBLE LIB(myLib)');

CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');

END Myproc;

Now what this is saying is Don't execute the RMVLIBLE unless the

addlible

fails, and ONLY if the addlible fails.
So if MYLIB was already in your library list at the beginning it will no
longer be.
If MYLIB was not in your library list at the beginning it will now be.

See an example of a continue handler below

Sample program:
CREATE PROCEDURE Change_IFS_Owner
(IN CURRENT_OWNER VARCHAR(10),
IN NEW_OWNER VARCHAR(10),
IN START_PATH VARCHAR(50) )
LANGUAGE SQL MODIFIES SQL DATA
SET OPTION DATFMT = *ISO
P1: BEGIN
DECLARE WORK_PATH_NAME VARCHAR(500);
DECLARE COMMAND CHAR(500); -- Ensure this is large enough.
DECLARE END_TABLE INT DEFAULT 0;
DECLARE C1 CURSOR FOR
select path_name
-- 'CHGOWN OBJ(''' concat path_name concat ''')

NEWOWN('

concat new_owner concat ')'
--, QCMDEXC('CHGOWN OBJ(''' concat path_name concat
''') NEWOWN(' concat new_owner concat ')')
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME =>
START_PATH,
SUBTREE_DIRECTORIES
=> 'YES'))
WHERE object_owner=CURRENT_OWNER
and path_name not like '%' concat x'7D' concat '%';

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = 1;
-- DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION
-- SET DEPT_SALARY = NULL;
OPEN C1;
FETCH C1 INTO WORK_PATH_NAME;
WHILE END_TABLE = 0 DO
SET COMMAND = 'CHGOWN OBJ(''' concat work_path_name concat
''') NEWOWN(' concat new_owner concat ')';
CALL QSYS2.QCMDEXC(COMMAND);
FETCH C1 INTO WORK_PATH_NAME;
END WHILE;
CLOSE C1;
END P1


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jay Vaughn
Sent: Wednesday, August 24, 2022 8:42 AM
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: Re: sql continue handler

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


sure thanks - thats one good option... any others inline with the

continue

handler logic?

Jay

On Wed, Aug 24, 2022 at 8:37 AM Rob Berendt <rob@xxxxxxxxx> wrote:

Why not check first?
Select count(*)
from qsys2.library_list_info
where schema_name = 'MYLIB'
;
https://www.ibm.com/support/pages/node/1119123



Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

Jay

Vaughn
Sent: Wednesday, August 24, 2022 8:32 AM
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: sql continue handler

CAUTION: This email originated from outside of the organization. Do

not

click links or open attachments unless you recognize the sender and

know

the content is safe.


In an sql trigger, I'm trying to implement the ability to add a

library

list entry...

I have the below code...

On the first invocation everything works fine and myLIB gets added to

the

libl.
But on the second invocation, it still fails with SQLSTATE '38501'

because

myLib is in the library list...

I would think when SQLSTATE 38501 is detected it would not fail but
CONTINUE with what is in the BEGIN/END... no? What am I missing?

BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '38501'
CALL QSYS2.QCMDEXC('RMVLIBLE LIB(myLib)');
END;
CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');


tia
Jay
--
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@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.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@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: https://amazon.midrange.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@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com