Re: sql continue handler

yeah the continue handler I presented is definitely wrong and that is what
I'm trying to get help on :)

Your suggestion of checking the qsys2.library_list_info did work for me
just fine.

thanks

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

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