Re: NOOP in SQL procedure? And other questions

[CRPence <CRPbottle@xxxxxxxxx>]

  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;