Thanks, Chuck, for your very thorough reply.
Your first suggestion, of checking the job log for the QZDASOINIT job, solved the problem. (I thought I had done that , but that must have been on an earlier issue.)
The log showed that it could not resolve to a utility used elsewhere in the service program (but not in this particular procedure), which has a separate library and binding directory. Adding that library to the library list of the connection definition was all it took to make this work properly.
Your other suggestions regarding the QSYS2 files are good ones, and while not needed this time, will be kept for future reference.
Thanks again!
Bill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, October 12, 2016 12:28 PM
To: midrange-l@xxxxxxxxxxxx
Subject: [BULK] - Re: UDF not found when importing to Excel via Microsoft Query
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).
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
