Re: External SQL procedure must be ILE?

On 15 May 2012 08:10, Michael Schutte wrote:

Here's the source that produces that message.

CREATE PROCEDURE &LIB/MATCHANITEMCODE (
IN @STORER DECIMAL(4, 0) ,
IN @SUFFIX CHAR(2) ,
INOUT @ITEM CHAR(20) ,
OUT @LOT1 CHAR(20) ,
OUT @LOT2 CHAR(10) ,
OUT @LOT3 CHAR(10) ,
OUT @QUANTITY DECIMAL(7, 0) )
LANGUAGE RPG
SPECIFIC MATCHANITEMCODE
NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
FENCED
EXTERNAL NAME '*LIBL/ITP20R'
PARAMETER STYLE GENERAL ;

As noted was determined from the message, presumably SQL0113, that the syntax does not allow for a library special value; i.e. "Name *LIBL not allowed." Although the syntax diagram and supporting text in the documentation [examples] may be limited in this regard, the external object name when not delimited as a string, is searched just as might be expected; i.e. via *LIBL. Thus the resolution is to omit the apostroshe characters as delimiters, in addition to removing the *LIBL.

Remove *LIBL, then RUNSQLSTM fails with this message.
SQL0449 30 4 Position 74 External program name for routine
MATCHANITEMCODE in Z_PD3464 not valid.

That error remains when what, that just 'ITP20R' [per "Remove *LIBL"], was specified on EXTERNAL NAME? That is because only a specification in the of 'library-name/program-name' is allowed per SQL0449 RC1. The additional support for use of an undelimited non-string-form of a name is not described in that message text; i.e. specification of just program-name can be used to imply the effect of *LIBL/program-name

Of course the syntax does not allow for "&LIB" as the library qualifier for the procedure name either, so the given source does not reflect what was really processed; i.e. a source pre-processor presumably replaced the first line with?:
CREATE PROCEDURE Z_PD3464/MATCHANITEMCODE (

On Tue, May 15, 2012 at 11:08 AM, Michael Schutte wrote:

I was wrong, it was reason code 1. Program not found. *LIBL is not
valid on EXTERNAL NAME. However, check out reason code 3. It says
"The external program was not an ILE *PGM or *SRVPGM"

Hence me asking the question.

What is described in an unrelated reason code is not a good basis for inference of general applicability. That reason code could be specific to some other keywords\clauses for the option-list which imply ILE as a requirement; e.g. a routine as a FUNCTION instead of a PROCEDURE when the PARAMETER STYLE is not compatible. Oddly IMO, the error does not occur for the CALLED ON NULL INPUT since the chosen parameter style has not ability to deal with NULL values; I would expect RETURNS NULL ON NULL INPUT.

Message . . . . : Routine MATCHANITEMCODE was created, but cannot
be saved and restored.

The message identifier for that message is presumably SQL7909. That is a completion with a minor-level diagnostic warning issued for association with all many program types because the HLL does not have SQL pre-compiler support, and the SQL does not update the associated space of programs objects which can not be a SQLhll source-type.

Since this was created, what was different than the original scenario?

Cause . . . . . : The routine MATCHANITEMCODE was created
successfully in MSCHUTTE with a specific name of MATCHANITEMCODE,
but the routine's attributes could not be saved in the associated
program or service program object. If the *PGM or *SRVPGM object
is saved and then restored, the SQL catalogs will not be updated
with the attributes for this routine. Reason code is 1. Reason
codes and their meanings are: 1 -- The external program did not
exist when the CREATE statement was issued. 2 -- The external
program schema is QSYS. 3 -- The external program was not an ILE
*PGM or *SRVPGM. <<SNIP 4-8>>

Recovery . . . : Do one of the following based on the reason
code: 1 -- Ensure that the external program exists when the CREATE
statement is issued. 2 -- Ensure that the external program schema
is not QSYS. 3 -- Ensure that the external program is an ILE *PGM
or *SRVPGM. <<SNIP 4-8>>

This isn't my issue, but the question still remains.

Hopefully my opening remarks clarify; if not, then the additional commentary.

Regards, Chuck