Re: External Stored Procedure Vs. External UDF

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

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