RE: UDF not found when importing to Excel via Microsoft Query

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

Thanks,
Bill


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, October 12, 2016 10:10 AM
To: Midrange Systems Technical Discussion
Subject: Re: UDF not found when importing to Excel via Microsoft Query

I assume the *SRVPGM is in OELIB?

Is the UDF defined as a call to a procedure in XX0504V?

Can you show your query and the parms defined for procedure and UDF?

I suspect it is a parm mismatch. You might try using CHAR(), DEC(), or
CAST() to explicitly match the param types.

Charles

On Wed, Oct 12, 2016 at 9:45 AM, Bill Reed <breed@xxxxxxxxxxxxxx> wrote:

I have created a UDF to return a scalar function within an SQL Select
statement. The UDF references a procedure within a service program.
The Select works fine when using SQL on the IBM i - Run SQL Scripts
from Navigator, Run SQL Scripts from ACS (is that the same?), and
green-screen STRSQL. But when I try to reference it from within
Excel, using "Get external data" / "From other sources" / "From
Microsoft Query", I get
"SQL0204 - XX0504V in OELIB type *SRVPGM not found" (where XX0504V is
in fact the name of the service program).

I have learned that the SQL0204 "not found" message can really mean
something like parameter mismatch, so I have checked and tinkered with
those to no avail. The connection defined in Excel has the same user
ID and the same library list, plus the UDF name is qualified; I see no
reason that it can't really be found. I suspect either a difference
in syntax between the two SQL's (MS and IBM i) or something else
relating to the connection definition. All other SQL which I have
copied over in this way works fine; it seems to be only the UDF.

(I'm new on this list so forgive me if this has been addressed before;
I was not able to find anything in the history.)

Thanks for any direction.

Bill Reed
--
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.

--
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.