× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. August 2022

Re: sql continue handler

sorry it is the code between

ADDLIBLE BEGIN
END ADDLIBLE;

also...

I tried this with Rob's "continue handler" redesign and it won't work in
strsql... -438
if I substitute what is in red in the source with below...

Myproc: BEGIN

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

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

END Myproc;

Jay


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

Colors do not appear on the list.

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 10:03 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.


Here is the statement...

the red is what i added...
if you take that out, this statement generates the trigger pgm fine in the
sqlrpgle pgm and strsql.
if you leave it in, it generates fine in strsql but no sqlrpgle...???
any ideas why??

CREATE TRIGGER "JVAUGHN"/DEFERPF_CARD_INSERT
BEFORE INSERT ON "JVAUGHN"/"DEFERPF"
REFERENCING NEW AS NEW_ROW FOR
EACH ROW MODE DB2ROW

BEGIN
DECLARE TRGCODE CHAR(20);
DECLARE EVENT CHAR(1);
DECLARE HLDCHR1 CHAR(32624);
DECLARE HLDCHR2 CHAR(32624);
DECLARE MSGID CHAR(7);
DECLARE MSGTEXT CHAR(80);
DECLARE ERRORS CHAR(1);
SET EVENT = 'I';
SET HLDCHR1 = ' ';
SET HLDCHR2 = ' ';
SET MSGID = ' ';
SET MSGTEXT = ' ';
SET ERRORS = ' ';

ADDLIBLE BEGIN
select 'Y' into ERRORS
from qsys2.library_list_info
where schema_name = 'myLib'
fetch first 1 row only;
IF ERRORS <> 'Y'
THEN CALL QSYS2.QCMDEXC('ADDLIBLE LIB(myLib) POSITION(*LAST)');
SET ERRORS = ' '
END IF;
END ADDLIBLE;

IF NEW_ROW."CARD" <> ' '
THEN CALL "myLib"/PRCTRGENCCHR(TRGCODE, 'JVAUGHN_DEFERPF_CARD', EVENT,
HLDCHR1, NEW_ROW."CARD", HLDCHR2, MSGID, MSGTEXT, ERRORS);
IF ERRORS <> 'Y'
THEN SET NEW_ROW."CARD" = HLDCHR2;
end if;
END IF;

IF ERRORS = 'Y'
THEN Signal SqlState Value 'UT099';
end if;
END


the error in sqlrpgle in debug indicates this when it tries to create it...
the begin/end structures look fine to me...??

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.

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

I've never had that situation.
I guess if you're determining something on the fly, like what library to
create the procedure in, and then you're using that to create it, I
guess.
But as far as host variables on WHERE or ORDER I'd find some way to pass
those in as parameters to the stored procedure.

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: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.


it all depends on what needs to be accomplished... What if you have many
host variables that come into play in the procedure creation?

Jay

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

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

--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.