On 12-Oct-2016 09:28 -0500, Bill Reed wrote:
Yes, OELIB is the service program's library.
(The following is a simplified example, but with the same results.
The actual function, procedure, etc., is obviously more involved.)
This example takes the name of a printer device (10 chars) and
returns a 30-character description from a table.
Function name: fnGetPrintDsc
Procedure name: prGetPrintDsc2 in service program XX0504V in library
OELIB.
Function generated thus:
CREATE OR REPLACE FUNCTION OELIB/FNGETPRINTDSC (
PRINTER VARCHAR(10) )
RETURNS CHAR(30)
LANGUAGE RPGLE
SPECIFIC OELIB/FNGETPRINTDSC
NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
EXTERNAL NAME 'OELIB/XX0504V(PRGETPRINTDSC2)'
PARAMETER STYLE GENERAL ;
RPG code for the procedure: (which calls another procedure but done
this way to allow the Varying keyword)
P prGetPrintDsc2 B Export
D prGetPrintDsc2 PI 30a
D pvPrintId 10a Const Varying
/free
Return prGetPrintDsc(pvPrintId); // another procedure
// (note absence of '2' on end) which
// returns 30 character text
/end-free
P prGetPrintDsc2 E
SQL statement in the Excel connection properties:
select OELIB.FNGETPRINTDSC('PRT03') from SYSIBM.SYSDUMMY1
This will not even let me save and close the connection properties
window without the "service program not found" error. But the same
select statement pasted into Run SQL scripts returns the text for
PRT03 just fine.
I realize I could CAST the input as character instead of using
Varying, but at least I know the above works in the IBM i context.
Levels:
IBM i: 7.1
Excel 2010
I suppose DROP FUNCTION followed by a new CREATE FUNCTION of the
original SQL ROUTINE is moot; i.e. the above expresses an actual
re-create example, from scratch?
With both the above and the Service Program information given in
another reply, I see nothing specifically wrong. Perhaps try to get a
spooled debug joblog of the server job that services the failing request
to see if there is anything peculiar in the joblog other than the -204
[msg SQL0204]; also get a DSPJOB of that job, which should show the file
QASQRESL as open, though for not finding the routine, so I expect no RRN
position to show in the Open Files information.
FWiW, a variation of the following query is what I have sometimes
used to perform a quick sanity check on some definitional details for a
FUNCTION routine that has a particular external procedure name expected
for a specific library name. The query would expose [by close/careful
review] if there are possibly multiple definitions for the FUNCTION due
to either possible overloading or incorrect character-casing on the
procedure name [which, as I recall, is a common origin for the noted
error]; my /shortened/ values may not be appropriate quite generally,
but they work for me, mostly, until I have to bump them up a litle ;-)
SELECT varchar(routine_schema, 10) as lib
, varchar(routine_name , 18) as name
, varchar(specific_schema, 10) as speclib
, varchar(specific_name , 18) as specname
, varchar(external_name, 36) as extname
, varchar(char(routine_created), 16) as crt
, routine_body as body, EXTERNAL_LANGUAGE as lang
, parameter_style as style, is_deterministic as det
, varchar(sql_path, 36) as path
, number_of_results as rtn
, out_parms as out, in_parms as in
, varchar( SOURCE_SPECIFIC_SCHEMA, 10) as srclib
, varchar( SOURCE_SPECIFIC_NAME , 18) as srcnam
FROM table
( select *
from qsys2.sysroutine
where ROUTINE_TYPE='FUNCTION' ) as n
WHERE ( routine_schema= 'OELIB'
and upper(routine_name) like '%FNGETPRINTDSC%' )
or ( specific_schema='OELIB'
and upper(specific_name) like '%FNGETPRINTDSC%' )
or upper(external_name) like upper('%prGetPrintDsc2%')
ORDER BY name, lib
If nothing conspicuous found with that, then I would review output
from DSPDBR QSYS2/SYSROUTINE to ensure that the file QASQRESL in QSYS2
is shown; again with DSPDBR QSYS2/SYSROUTINE MBR(SYSROUTINE). Then I
would review the definitions of both of those files to ensure there is
just one member and that the access path is valid for the QASQRESL file
as INDEX. If nothing still, then probably I would make a backup of the
files SYSROUTINE and SYSPARMS along with any LF or INDEX created over
them, using ACCPTH(*YES), and then I would RGZPFM QSYS2/SYSROUTINE
ALWCANCEL(*NO).
midrange.com
