On 23-Oct-2015 17:12 -0500, Vernon Hamberg wrote:
On 10/23/2015 4:34 PM, Buck Calabro wrote:
On 10/23/2015 4:34 PM, Justin Dearing wrote:
I discovered that SELECT * FROM QSQLSRC gives me a rowid, what
looks like a SEU line number, and a row of text, but no way to
tie a line of source code to a source member. Is there another
stored procedure for that?
Yeah, so SQL doesn't understand file members. Ish. SQL will always
use the *FIRST member (which is the first one added. ish.)
Great job intuiting the perspective of the inquirer. I was going to
reply with a big "Huh?"; I held off asking, to avoid a drawn out
discussion trying [to gain that perspective] to better understand what
was being asked.
There's an IBM i SQL extension to CREATE ALIAS that let's SQL
access a specific member though.
CREATE ALIAS library.srcmbr for library.qsqlsrc(srcmbr)
Then SELECT * FROM library.srcmbr will operate on the source member
specified in the ALIAS. DROP ALIAS when done with it...
Thus of course, following from the response to first paragraph, that
means the member name already must be known in advance; i.e. there would
be no reason to inquire the name of the member, because that was
effectively supplied as input.
Is there a stored procedure for ADDPFM? I'm thinking I'm going to
write a stored procedure that does the GENERATE_SQL, ADDPFM, etc
so I can write DDL in sql workbench, but have it stored where the
greenscreeners like it.
You can construct an ADDPFM command as a character string and
submit it to the stored procedure QCMDEXC, so something like
call qcmdexc('addpfm buck/qsqlsrc justin')
where you'd generate the quotes and everything in between.
Buck, you need the length in the call to QCMDEXC, as well.
Regarding the need to specify the length... Well, probably not
[anymore], at least not for the *unqualified* CALL as in the example
above. Similarly, also probably not if the CALL had explicitly
specified the library-qualifier of QSYS2 [probably best to do so anyhow,
if that is the intended invocation]; i.e. for whomever the prior
unqualified CALL functions, very likely so too with this:
call qsys2.qcmdexc('dsplibl') -- vs: call qcmdexc('dsplibl')
If you've not tried either of those invocations in your default SQL
environment(s), now might be the time... to ditch the second argument :-)
In fact, given the description in the prior reply in this thread,
even that /example/ of "CALL QCMDEXC ('ADDPFM' some-number...)" almost
surely is representative of an invocation that is not calling the
program in QSYS directly. That is because the command string length
would need to have been specified as a DEC(15, 5) to allow the direct
invocation of the program in QSYS, yet the "some-number..." made no
explicit mention of casting or other means to effect that data typing:
[
http://archive.midrange.com/midrange-l/201510/msg00749.html]
And if so, then already that request is presumably being intercepted
by a QSYS2.QCMDEXC procedure, but the two-parameter variant that is
defined to accept an INTEGER as a second argument.
So while true what had been noted in that prior reply, that the
system program QSYS/QCMDEXC need not be _registered_ as a stored
procedure to enable the SQL CALL to invoke that program QSYS/QCMDEXC
directly [though necessarily only using input-only parameters], that
does not imply also that a CALL of the unqualified name QCMDEXC will
necessarily always find and invoke that system program QSYS/QCMDEXC
directly merely because for example that QSYS is first in the library
list and path.
The implied "need" for the command-string-length specification as the
second parameter overlooks both:
• that the Database\SQL feature has added a QSYS2.QCMDEXC stored
procedure
• and that the SQL routine resolution [aka *function resolution*]
will generally locate that routine in QSYS2 via the default PATH with
the unqualified reference.
The procedure in QSYS2 will be found despite QSYS being first in both
*LIBL using System Naming and in the list "QSYS","QSYS2","AutId" using
SQL Naming for their respective default PATH, because the *one* argument
specified, matches the parameter specifications of just one VARCHAR
parameter; i.e. the registered stored procedure QSYS2.QCMDEXC is defined
as accepting only one argument, such that the /compatible/ invocation is
chosen before the undefined\unregistered program from QSYS.
Thus, ever since the following support was added [see link below],
the above unqualified invocation should work [despite not being
library-qualified, at least with the likely defaulted PATH irrespective
of the NAMING OPTION] with the specification of just one
character-string parameter [just as Buck's example shows]:
[
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.QCMDEXC%28%29%20procedure%20no%20longer%20requires%20a%20command%20length]
I have long had my ExecCmd stored procedure to do what is probably
the same thing as what QSYS2.QCMDEXC provides; just never made sense for
the requester to determine the length of their command-string when the
SQL could so easily do the calculation for the user [without their
having to specify the literal string expression in a first argument and
then again wrapped by a LENGTH scalar wrapped in a DECIMAL casting
scalar as the second argument... which seems almost sillier than just
counting the characters oneself ;-)].
midrange.com
