Re: Stored procedure

QCMDEXC is probably a bad example on my part, since there have been items added to QSYSPROCS for it. One is listed as EXTERNAL, the other (QCMDEXC1), as SQL type. Not sure how long these have been around.

My point was that ANY program on the i can be called from SQL - one might have to put values in using hexadecimal, or as Chuck did, with all the digits for a packed 15,5 entered, we often have to do that with CL calls. And one might run into issues with character literals into parameters longer than 32 characters.

So to prove my point - I have a program named TEST - all it does is some CHGVAR commands on dates - I went into STRSQL and entered this -

call dp2test/test

It ran successfully - it is definitely not in the SYSPROCS table.

Cheers
Vern

On 6/10/2015 11:31 AM, CRPence wrote:

On 09-Jun-2015 13:06 -0600, Vernon Hamberg wrote:

As an aside to the OP - you can call ANY IBM i programs from
something like Crystal Reports - every program can be considered a
stored procedure.

Having said that, you don't WANT to do this with every possible
program. Maybe not even at all. And you will not get back things like
return codes or output parameters and that kind of thing.

But you can use the SQL CALL to call QCMDEXC with 2 parameters and
have it done. Like WRKSYSSTS will get you a spooled file on the i,
IIRC. That would be something like this in SQL -

call qcmdexc('WRKSYSSTS', 9)

call qcmdexc('WRKSYSSTS OUTPUT(*PRINT)', 24)

If I run those in STRSQL I get the WRKSYSSTS screen for the first
one and a message that the call completed for the second - and a
spooled file. In batch (as it'd be from Crystal), you get the spooled
file.

FWiW, those examples using unqualified naming and a decimal integer constant\literal as the second argument have unstated pre-requisites; they are too presumptive of both the SQL Naming setting for the likely defaulted PATH [e.g. path=*LIBL for *SYS naming] and the existence of an IBM-supplied [or a user-supplied] Stored Procedure definition with the name QCMDEXC in QSYS or QSYS2. Essentially, those two examples are not written such that the sole dependency is on the existence of the external [OS-supplied] program named QCMDEXC in QSYS. The PATH is likely to be a non-issue, generally, because as I recall both QSYS and QSYS2 are implicitly included irrespective of SET PATH.

Thus IMO the examples might be better stated as the following [or that the assumptions explicitly noted], for which the pre-requisite is either the support is available for *SQL naming despite *SYS naming being in effect or that the *SQL naming is in effect:

call qsys.qcmdexc ('WRKSYSSTS', 0000000009.00000)

call qsys.qcmdexc ('WRKSYSSTS OUTPUT(*PRINT)', 0000000024.00000)

Given the PATH always includes QSYS, unless there is a program named QCMDEXC in a library preceding QSYS, then the 'qsys.' can be omitted in both examples, such that the invocations are unqualified. Yet, if a ROUTINE of type PROCEDURE is defined with that name in QSYS or found elsewhere in the PATH, then IIRC that takes precedence in routine resolution.

And as Birgitta alluded [though suggesting overloading vs what I believe is instead the effect of an optional parameter], the registered PROCEDURE by that name is system-supplied and the original two examples would function even without the length being specified; although since I always have had my own private EXECCMD and CMDEXEC routines [the latter a UDF] since long before optional\default parameters were allowed, I have never seen or used what the OS eventually provided as a PROCEDURE by that name.