Re: NOOP in SQL procedure? And other questions

Hi Michael

Thanks for this - I didn't think I could use NULL - it is not documented as a statement that I could find, while it is in Oracle and maybe other RDBMS.

Vern

On 4/17/2013 7:45 AM, Michael Schutte wrote:

Vern if you are using DECLARE GLOBAL. Then put a WITH REPLACE at the end
to replace the existing table.

Also for the else. I using put ELSE NULL




On Tue, Apr 16, 2013 at 1:48 PM, Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

Hi Dan

Very nice - this SQL language just gets better and better.

I like being able to scope the handlers - that will be handy to make a
compound statement around a DECLARE GLOBAL TEMPORARY TABLE that will let me
have a handler when the table exists - 42710 or some such SQLSTATE.

BTW, do you have my acoustic coupler at the office? I'm coming over for
the QUSER meeting.

Later
Vern

----- Original Message -----
Vern,

You can use nested compound statements. Here's one I did. Notice the
labeled begin and end of "DELETE_SECTION". I believe in some of the later
versions you can also label the LEAVE and ITERATE. So you can have an
ITERATE within a nested compound that references a higher level compound.

BTW: This one just deletes all the records that match the id in new_row
and replace them with an inserted record built from values in new_row.

Mostly the delete section is made its own compound statement just so I can
have a separate handler declared within the delete section. If there's no
matching records to delete, the process just continues on to the insert.
You'll also see an example of a do-nothing statement in the handler.

-- Generate SQL
-- Version: V5R3M0 040528
-- Generated on: 04/16/13 10:11:10
-- Relational Database: S106E02E
-- Standards Option: DB2 UDB iSeries
CREATE TRIGGER RJSFLOW.WORKPIECECURRENTSTEP
AFTER INSERT ON RJSFLOW.WORKPIECEHISTORY
REFERENCING NEW AS NEW_ROW
FOR EACH ROW
MODE DB2ROW
BEGIN ATOMIC
DECLARE LOCKWAIT_TIMEOUT CONDITION FOR SQLSTATE '57033' ;
DECLARE EXIT HANDLER FOR LOCKWAIT_TIMEOUT INSERT INTO RJSFLOW . TRIGGERLOG
( MESSAGE ) VALUES ( QSYS2 . CONCAT ( 'LOCKWAIT ERROR ' , NEW_ROW . ID ) )
;
DELETE_SECTION :BEGIN
DECLARE AT_END INTEGER DEFAULT 0 ;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ;
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET DELETE_SECTION . AT_END = 1 ;
DELETE FROM RJSFLOW . WORKPIECECURRENTSTEP WHERE RJSFLOW .
WORKPIECECURRENTSTEP . ID = NEW_ROW . ID ;
END DELETE_SECTION
;
INSERT INTO RJSFLOW . WORKPIECECURRENTSTEP ( ID , CREATEDDATE , CREATEDBY
, EVENTINFO , NEXTSTEPID , NEXTSTEPTYPE , NEXTSTEPNAME , PRIORITY )
VALUES ( NEW_ROW . ID , NEW_ROW . CREATEDDATE , NEW_ROW . CREATEDBY ,
NEW_ROW . EVENTINFO , NEW_ROW . NEXTSTEPID , NEW_ROW . NEXTSTEPTYPE ,
NEW_ROW . NEXTSTEPNAME , NEW_ROW . PRIORITY ) ;
END ;

Dan Kimmel
--


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Monday, April 15, 2013 1:19 PM
To: Midrange Systems Technical Discussion
Subject: Re: NOOP in SQL procedure? And other questions

Thanks, Chuck

The point about requiring ELSE supports good practice is well-taken. In
one of my procedures, in fact, I set a "found" variable to 0 in the ELSE -
I'm rotating through 4 different possible locations for a phone number,
creating a SELECT statement string that I use to prepare a statement and
declare a cursor. It is appropriate here to do as you suggest - I even have
a LEAVE in the ELSE.

The other procedure calls QCMDEXC to run a QShell function that returns
Active Directory attributes as name-value pairs. I need to flatten them
into rows. I have asked for exactly 7 attributes, not all are returned for
each AD name, and there is a CN= pair, as well as a blank row between each
group.

The only one that doesn't matter to me is the blank row - I use the CN= as
a level-break of sorts. I do have an else here, which really handles the
blank row. I suppose ITERATE could take care of me here. But I think not -
I have a FETCH right after the CASE, within a WHILE loop, and that would be
skipped with an ITERATE.

Putting the CASE in a compound statement - that would work nicely, too. It
fits my structure nicely, I think.

Maybe a hint at the flow will help - some declarations & other details are
omitted

/* Variables */
DECLARE AT_END INT DEFAULT 0;

/* Cursors */
/* Get Active Directory attributes */
DECLARE C1 CURSOR FOR S1;

/* Handlers */
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET AT_END = 1;

/* Global temporary table for LDAP attributes */
DECLARE GLOBAL TEMPORARY TABLE SESSION/LDAPATTRS
(ldapattrs CHAR(100));

/* Pull attributes into temp table */
set sqlStmt = 'select * from qtemp/ldapattrs';
prepare s1 from sqlStmt;
OPEN C1;
FETCH C1 INTO ldaptext;

/* Walk through specialist IDs */
WHILE AT_END = 0 DO

case
-- several WHENs that each set a variable from a name-value pair
-- the variable is used later in an INSERT when a CN= is encountered
else
set at_end = at_end; /* Dummy operation */
end case;

/* Get next ID */
FETCH C1 INTO ldaptext;
END WHILE;



----- Original Message -----
On 14 Apr 2013 16:05, Vernon Hamberg wrote:

<<SNIP>>

1. When using the CASE control statement, I find that it requires
an ELSE in this context. Now many times I don't need the ELSE - all
conditions are covered in the WHEN clauses, and I don't want some
"just anything" bit.

The docs have this information -

"If none of the conditions specified in the WHEN are true, and an
ELSE clause is not specified, an error is issued at runtime, and
the execution of the CASE statement is terminated (SQLSTATE 20000)."

So any suggestions for a NOOP? I've used a SET of a variable to
itself. I suppose I could create a CONTINUE HANDLER.
<<SNIP>>

IMO a requirement for an ELSE enforces a good programming habit; i.e.
any failed assumptions should always be handled or notified. If the
assumption is that every known condition would be handled by the WHEN
clauses coded prior, then the ELSE should be coded with something like
the following statement, to inform that the assumption was incorrect;
such is often referred to in computing as an "assertion":
SIGNAL sqlstate value 'UC001' set message_text='Unexpected case';

Instead of SIGNAL, a CALL could be made to a routine that dumps
information about the failed assumption; probably passed as a literal
string specified in the source code; e.g.:
CALL ASSUME ('stCount value should only =1 or =2');

However if choosing to use SET, then rather than set any variable to
itself, I would declare a variable, perhaps named NOOP, and set that
variable to any compatible value whenever\wherever I wanted the
null\empty statement. This might be what was described as "just
anything", but I could make no sense of what was meant. e.g.:

case stCount
when 1 then set wc = 'One' ;
when 0 then set wc = 'Two' ;
else set NOOP = 0 ; /* trivial work; token NOOP appears */
end case ;

Otherwise there are GOTO, LEAVE, and possibly ITERATE or RETURN
control statements, depending on the context.

To use a GOTO to get after the CASE, requires that some other
statement follows the label; e.g.:

case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else goto stCaseEnd ; /* effectively a NOOP */
end case ;
stCaseEnd:
/* some statement is required here; i.e. can not be an "end" */

If necessary, the CASE may be set within its own compound statement
to accomplish a LEAVE to just after the CASE, if leaving the routine is
not desirable by LEAVE procname or RETURN integer; e.g.:

stCase:
begin
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else LEAVE stCase ; /* effectively a NOOP */
end case ;
end stCase ; /* LEAVE jumps to here */

2. Speaking of which - it seems that a HANDLER also requires a
procedure statement - a NOOP would be nice sometimes - this would
give a way to ignore certain "errors", like table already exists
on a CREATE TABLE <<SNIP>>

The SQL procedure statement noted in the syntax diagram for the
handler can be a compound statement [as later comments acknowledge;
though they are snipped]. In my experience the compound statement can
be /empty/ such that there is just a BEGIN and END.

Thinking on that... I tried the same for the ELSE, and the same form
of an /empty/ request functioned for the ELSE as well; just as it does
for a condition handler. Maybe not as nice as a NOOP or a just a
semicolon:

case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else begin end ; /* empty request */
end case ;

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.