Re: NOOP in SQL procedure? And other questions

You might want to look here for scoping the condition handler(s):
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyredesign.htm

To skip a blank row, why not include a WHERE clause in the SELECT? A simple case of overlooking the obvious? That is:
'select ldapattrs from SESSION/LDAPATTRS where ldapattrs<>'''''

The CL interface to invoke QShell is to effect an OVRDBF STDOUT TOFILE(QTEMP/LDAPATTRS) in order to redirect the standard output within that job, from the QSH CMD('ldap_request'), into the GTT?

I wonder if you could use SQL ARRAY support instead of using a CURSOR with FETCH; load the results of the SELECT directly into an ARRAY.

An example of some actual effects of the LDAP data in the file would be more appropriate for reviewing... as I [nor likely others will] have no ability to effect such a request... and even what request was made of the LDAP was unstated.

Regards, Chuck

On 15 Apr 2013 11:19, Vern Hamberg wrote:

<<SNIP>>

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