On 17 Apr 2013 11:28, Vern Hamberg wrote:
I'll have to think about the OVRDBF - I've run into issues with
redirecting output from QShell to a PF, but maybe that's not an
issue here - I mean, the PF has to exist already. Well, I've
answered myself, I think, but will look at this for future
reference, not just in an SQL procedure.
My comment was at first directed toward the fact that calling QCMDEXC
from RPG to effect QSH CMD() processing seemed to involve a fair amount
of indirection; perhaps to ease redirection.? Without actually asking,
I was wondering if there might not be an API to effect asking directly
of the LDAP or calling the QSHELL directly, to offer a bit more direct
access to the data? With the former, I would expect that an external
UDTF would be able to be created to expose the LDAP data to and via the
SQL; such a UDTF could be used on the INSERT.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/apis/dirserv2.htm
_i LDAP API Overview i_
"...
Typical API usage
The basic interaction is as follows. A connection is made to an LDAP
server by calling ldap_init (or ldap_ssl_init, which is used to
establish a secure connection over Secure Sockets Layer (SSL)).
An LDAP bind operation is performed by calling ldap_simple_bind or
ldap_sasl_bind. The bind operation is used to authenticate to the
directory server. Note that the LDAP V3 API and protocol permits the
bind to be skipped, in which case the access rights associated with
anonymous access are obtained.
Next, other operations are performed by calling one of the synchronous
or asynchronous routines (that is, ldap_search_s or ldap_search followed
by ldap_result).
Results returned from these routines are interpreted by calling the LDAP
parsing routines, which include operations such as:
* ldap_first_entry, ldap_next_entry
* ldap_get_dn
* ldap_first_attribute, ldap_next_attribute
* ldap_get_values
* ldap_parse_result (new for LDAP V3)
* etc.
..."
But more importantly, I was wondering because the work is spawned. I
was confused about how redirection into a file in QTEMP would function
without an OVRDBF to STDOUT. For example, the following script using an
override to the Standard Output works fine, but redirecting to a
database file in QTEMP [¿or even to any described non-source physical
file?] would fail within the script.?:
declare global temporary table session/ldapattrs
( attr for ldapattrs char(100) not null with default '' )
ovrdbf stdout tofile(qtemp/ldapattrs)
qsh cmd('ldapsearch -e') /* a simple functional request */
--Command ended normally with exit status 0.
runqry *n ldapattrs
Line ....+....1....+....2....+....3....+ <...> 9....+...10
ATTR
000001
000002 SDK Version: 510
000003 Protocol Version: 300
000004 SDK Build Level: V5R3M0
****** ******** End of report ********
The LDAP can look like this - varying number of attributes per
account:
CN=Smith\
sn=Smith
givenName=John
displayName=Smith
employeeNumber=12345
sAMAccountName=SMITHJ
CN=Anderson\
sn=Anderson
givenName=Anders
initials=D
displayName=Anderson
employeeNumber=98765
sAMAccountName=ANDERAD
mail=aanderson@xxxxxxxxxxx
The earlier comment suggesting "I use the CN= as a level-break of
sorts" seems to imply a good possibly that there is a dependency on the
use of the /arrival sequence/ for that data, so the following comments
will include an ORDER BY RRN() to ensure that assumption is met. FWiW
it seems from the LDAP command-line interface, with regard to the
*Output Format* that "Multiple entries are separated with a single blank
line." So I suppose the blank line could be more important than a "CN="
if perhaps a new group might start with something else.?
I've not studied SQL arrays enough to know if they'd help.
Perhaps not, but they offer what seems a nice interface to load the
data without the [CLOSE,]OPEN,FETCH,CLOSE. But that is also available
without using any ARRAY support; see first example loading an ARRAY.
First, create an ARRAY type that the procedure would be able to use.
CREATE TYPE vpArray AS VARCHAR(100) ARRAY[300]
;
In the procedure, something like one of the following probably
suffices to load the array. The first avoids the OPEN\CLOSE of the
cursor, and replaces FETCH with SET statements. Any of these could be
extended to divide the value pairs into both attributeType and
attributeValue with just another array declaration and a SET from a
function invocation that obtains each portion from the field. I have
never actually used the SQL ARRAY support, so I may have some syntax
wrong or be unaware of limitations:
declare vpElem smallint default 0 ;
declare vpList vpArray ;
For vp as ValuePairs cursor for
select strip(ldapattrs) /* or RTRIM */
from SESSION/LDAPATTRS as Attr
where ldapattrs<>''
order by rrn(Attr)
Do
set vpElem = VpElem + 1 ;
set vpList[vpElem] = vp.ldapattrs ;
End For;
declare vpList vpArray ;
set vpList =
(select ARRAY_AGG(strip(ldapattrs) ORDER BY rrn(Attr))
from SESSION/LDAPATTRS as Attr
where ldapattrs<>''
) ;
declare vpList vpArray ;
select ARRAY_AGG(strip(ldapattrs) ORDER BY rrn(Attr))
into vpList
from SESSION/LDAPATTRS as Attr
where ldapattrs<>''
;
This isn't supposed to get too complicated, so I'm happy enough
with what we got. So's my boss, which is the best thing!
FWiW: Seems an odd request to get that data into a database TABLE to
be processed. I would think whatever needs to be done likely has been
done before, in scripting languages and tooling.
As to complicated:
- Why use dynamic SQL for the SELECT?
- There are plenty of other ways than to prime the looping with a
FETCH [outside the loop], to process fetched row data within a loop.
See for example, the FOR loop in the example above; i.e. that example
need not be limited solely for populating an ARRAY.
Interesting idea about filtering against blank lines, but I'd
still need the ELSE, even though it'd never be executed, right?
The ELSE [with a begin\end, a LEAVE, a SET var to self, a GOTO, a
hard SIGNAL, an /assertion/ with SIGNAL only if failed, or ??] or the
monitor, I suppose. Note: the ITERATE could function as well in that
context, if the only FETCH were inside and just after the WHILE;
followed by an IF AT_END=1 THEN LEAVE; END IF;
Without more details, it is difficult to know if there might be
better ways to accomplish what needs to get done. The deliberately
/omitted/ details leave only a minimal explanation of how the CASE is
really being utilized. I infer the effect is to set a specific variable
name to the attributeValue based upon the attributeType for the row; the
INSERT it seems must be one row with a column for each attributeValue
per the description of the WHENs in the CASE, and I suppose that is
consistent with, that for the value pairs, the aim is to "flatten them
into rows". FWiW: A more complete description of what is the DDL of the
TABLEs involved and what needs to be done might allow someone to offer
alternate solutions; e.g. one that could eliminate the CASE statement.
Regards, Chuck
On 17 Apr 2013 10:45, CRPence wrote:
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.
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;
midrange.com
