Re: UDF error: Routine x in xxxLIB not found with specified parameters. -- MIDRANGE-L

Hi Jerry,

This is where reading the manual really helps. Check the SQL Reference manual under the CREATE FUNCTION statement where it talks about PARAMETER STYLE. You've selected DB2SQL which is a synonym for style SQL. To condense it, the parameters passed to your procedure are

* The first n parameters defined in the function.
* The result parm.
* n parms for the indicator variables for the input parms (these are defined as 5i 0)
* An indicator parm for the result parm.
* A char(5) parm for the SQLSTATE.
* A varchar(517) parm for the fully qualified function name
* A varchar(28) parm for the specific name
* A varchar(70) parm for the message text.
* and 0-3 optional parms.

You didn't show the parms for GPKLIB/B, but I'm guessing they don't match the above.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

GKern@xxxxxxxxxxxxxxxx wrote:

Last week the fine folks here helped me to create my first UDF. I was able to create the UDF via CREATE FUNCTION and here is the generated SQL for that function:

CREATE FUNCTION GPKLIB.B ( NEW_PARAMETER CHAR(8) CCSID 37 ) RETURNS CHAR(8) CCSID 37 CAST FROM CHAR(8) CCSID 37 LANGUAGE RPGLE SPECIFIC GPKLIB.BSPECIFIC NOT DETERMINISTIC READS SQL DATA CALLED ON NULL INPUT DISALLOW PARALLEL EXTERNAL NAME 'GPKLIB/B' PARAMETER STYLE DB2SQL ;

Each time I try to use it it I receive the following error message: Routine B in GPKLIB not found with specified parameters.

I've deleted and recreated this UDF multiple times (playing with the parms) all with no luck at getting the UDF to return a value. Here is the code I'm trying to execute.
Pgm C calls the UDF (Pgm B).
Pgm B calls Pgm A which calls a SQLRPGLE procedure to return a date. My 'proof of concept' goal is to see the return value from pgm A in the *entry parm of PGM B at its return statement.

I've been following the other recent thread and changed the GetOIBPeriodBE procedure to specify CONST on the parameters. If I directly run the RPG pgm B in debug it works as expected and returns the proper value.
PGM C is my interpretation of what I see in the example at the beginning of chapter 3.4.4 of the Stored Procedure, Triggers, UDF redbook (the call proc1 (totsales, totcust) statement.

---------------------PGM C-------------------------------------------------
C/EXEC SQL CALL GPKLIB/B ('00000000') C/END-EXEC C Eval *INLR = *ON C Return

-------------------------------PGM B----------------------------------------
C Call 'A' C Parm Bparm 8 C Eval *INLR = *On C Return
------------------------------PGM A----------------------------------------
DA PR Extpgm('A') D Date 8 DA PI D Date 8 D DS D RetValue_N 1 8 0 D RetValue_A 1 8 D GetOibPeriodBE PR 8 0 D LOC#_PR 1 CONST D CLNO_PR 3 0 CONST D PERIOD_PR 6 0 CONST D BEGOREND_PR 1 CONST C Eval RetValue_N = GetOIBPeriodBE('0':0:200801:'B') C Eval Date = RetValue_A C Eval *INLR = *On C Return
Apparently I'm missing something here. Is what I'm trying to do practical or am I missing the point of what UDF's and (SQL/external) stored procedures are used for? I thought a UDF was a way for me to access existing (RPG) code from an SQL interface. I've been reading the Redbook too but I can't seem to get past this error.

Thanks!

Regards, Jerry

Gerald Kern - MIS Project Leader
Lotus Notes/Domino Administrator
IBM Certified RPG IV Developer
The Toledo Clinic, Inc.
4235 Secor Road
Toledo, OH 43623-4299
Phone 419-479-5535
gkern@xxxxxxxxxxxxxxxx

This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized use, disclosure or distribution is prohibited. If you are not the intended recipient, please inform the sender by reply e-mail and destroy this and all copies of this message.