On 14-Dec-2011 15:09 , James Lampert wrote:
Rob Berendt wrote:
http://archive.midrange.com/midrange-l/200701/msg00803.html
FWiW: That link describes a LANGUAGE SQL procedure that can be used
to DROP a routine if the routine exists. Oddly by first determining
whether the routine is a FUNCTION or a PROCEDURE, then dynamically
issuing the DROP, by both having used the SPECIFIC NAMEs for a lookup
[that forces an unnecessary table-scan] and then failing to include the
SPECIFIC clause in the DROP syntax, by not just use the generic ROUTINE
instead of FUNCTION or PROCEDURE, and by preparing without error
handling and then executing rather than just EXECUTE IMMEDIATE.?
Yes, but it appears that this only works in a Stored Procedure, which
would have to be created, which returns us to the original problem of
creating something unconditionally, whether it already exists or not
The same SQL requests could be performed outside of an SQL Procedure.
The effectively identical [or better, an improved version of that]
code could be performed directly in an SQLRPGLE program.
And Charles Wilt wrote:
Query SYSFUNCTIONS to see if the function is defined...
and
SYSIBM/SYSFUNCTIONS
or
QSYS2/SYSFUNCS
which contain LOBs, creating another catch-22 situation.
While the LOB columns may prevent DCLF or specification on an F-spec,
means exist to work around the limitations to native I\O. Most notably,
use SQL to query the catalog instead. Regardless, native I\O is not
desirable. The SQL VIEW is a non-keyed file, so lookup without a query
is not desirable. An OPNQRYF can do that easy enough, and a simple
FORMAT() along with a couple overrides can suffice for a simple
existence check.
I have one idea involving a good old-fashioned DDS LF, but
While a DDS LF created [e.g. in QTEMP] over the SYSROUTINE file is
possible to effect, there is [though exists only per implementation
details; i.e. subject to change\elimination] already a keyed logical
file named QASQRESL in QSYS2 which implements the "routine resolution"
[aka "function resolution"]. To verify the source for the record format
on any particular release, Per: DSPOBJD QSYS2/QASQRESL *FILE *SERVICE,
see: DSPPFM QSYS2/QASQSRC QASQRESL
The SQL catalog TABLE SYSROUTINES has the primary key SPECSCHEMA,
SPECNAME such that the SPECIFIC NAME could be used by any native request
which ignores or allows LOB columns. The CPYF utility has the ability
to copy and find a key value from that file with LOBs. For example the
completion that one row was copied provides a result that can be
verified as either a FUNCTION or PROCEDURE using:
crtpf qtemp/RTNTYPE rcdlen(9)
CPYF FROMFILE(SYSROUTINE) TOFILE(qtemp/RTNTYPE ) crtfile(*no)
print(*none) fmtopt(*map *drop) mbropt(*add)
FROMKEY(*BLDKEY (&RL &RN)) TOKEY(*BLDKEY (&RL &RN))
while I pursue that, is there any sort of variation on Rob's
solution, that doesn't involve a stored procedure?
OPNQRYF could be used to create a shared ODP referenced by an RPG
program compiled against a file which is the same as that used on the
FORMAT() of the Open Query File request; the file for the record format
would of course necessarily omit any LOB columns like the CRTPF noted
above. The selection could be generic to include all functions,
procedures, or routines from a library, or specific to just one for
which the READ would fail if the specific routine was not included in
the QRYSLT selection.
Regards, Chuck
midrange.com
