× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Alfredo,

SQL looks at the size and type of parameters as well as the name when trying to locate a stored procedure. And the trick is that character constants are actually passed as VARCHAR, not CHAR.

You have two choices: 1) change your stored procedure to define its parameters as VARCHAR, or 2) cast the constants you are passing to the procedure, e.g. CHAR('myconstant',10).

hth,

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

Alfredo Delgado wrote:
There's an RPG pricing program I need to call to get item prices when
building an HTML catalog. At present, this RPG program is being called by a
CL program that sets the correct library list depending on the environment
I'm working in.

If I define an external stored procedure over the CL program I can call the
stored procedure and things work as expected. e.g.
CALL ALFREDLIB.SP_CL_J5540721Z('LPCRP', '59', 'PRICING', ' 15432',
'101443', '1', ' ' )

Return Code = 0

Output Parameter #7 = 48.2961

Statement ran successfully (62 ms)


However, what I really need to do is call the pricing program as I select
items from a table. So far I have not had any luck defining an external
function to accomplish this.

I can create the function with the following statement.
CREATE FUNCTION LPCRPO81.J5540721Z( Library CHAR(10), PSMCU CHAR( 12 ),
PSASN CHAR( 8 ), PSAN8 CHAR( 8 ), PSUITM CHAR( 26 ), PSUORG CHAR( 16 ),
PSPRIC CHAR( 15 ) )
RETURNS CHAR(15)
EXTERNAL NAME LPCRPO81.J5540721Z
LANGUAGE CL
PARAMETER STYLE SQL;

When I try to access the function with either of these statements I get the
same failure.
SELECT LPCRPO81.J5540721Z('LPCRP', '59', 'PRICING', ' 59', '101443',
'1', ' ' ) as FOO FROM SYSIBM.SYSDUMMY1
SELECT LPCRPO81.J5540721Z( 'LPCRP ', ' 59', 'PRICING ',
' 59', ' 101443', ' 1',
' ' ) as FOO FROM SYSIBM.SYSDUMMY1

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] J5540721Z in LPCRPO81 type *N not found. Cause . . . . .
: J5540721Z in LPCRPO81 type *N was not found. If the member name is *ALL,
the table is not partitioned. If this is an ALTER TABLE statement and the
type is *N, a constraint or partition was not found. If this is not an ALTER
TABLE statement and the type is *N, a function, procedure, trigger or
sequence object was not found.
If a function was not found, J5540721Z is the service program that contains
the function. The function will not be found unless the external name and
usage name match exactly. Examine the job log for a message that gives more
details on which function name is being searched for and the name that did
not match.

Recovery . . . : Change the name and try the request again. If the
object is a node group, ensure that the DB2 Multisystem product is installed
on your system and create a nodegroup with the CRTNODGRP CL command. If an
external function was not found, be sure that the case of the EXTERNAL NAME
on the CREATE FUNCTION statement exactly matches the case of the name
exported by the service program.

I have confirmed that LPCRPO81 is in my library list. I'd appreciate any
help in figuring out where it's going wrong.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.