Re: External Stored Procedure Vs. External UDF

["Alfredo Delgado" <adelgado@xxxxxxxxxxxxxxxxx>]

Thanks for the help and offer of help, Peter, Elvis and Sal.

This looks like the way to go:
SELECT LPCRPO81.J5540721Z( CHAR( 'LPCRP', 10 ), CHAR( '59', 12 ), CHAR(
'PRICING', 8 ), CHAR( '      59', 8 ), CHAR( '101443', 26 ), CHAR( '1', 16
), CHAR( ' ', 15 ) )  as FOO FROM SYSIBM.SYSDUMMY1;

VARCHAR isn't supported when calling a CL program.

The errors I'm getting now seem to denote progress. I think I'm going to
have to define my function as a SQL function that calls the external stored
procedure I mentioned. The last parameter is INOUT and it doesn't look like
I can define that with an external function calling a CL program. Any
correction on that would be appreciated.

Regards,
Alfred

On Mon, Apr 7, 2008 at 5:40 PM, Peter Dow (ML) <maillist@xxxxxxxxxxxxxxx>
wrote:

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