|
midrange.com
I figured it out. Rob, you sent me on the right path. I did change the
input parameter of AlphaItem in my RPGLE module from 10p 0 down to 6p0 to
match the length of s20prd. But the kicker was that I needed to register
my function in SQL with the parm as DEC(6,0) instead of INT.
CREATE FUNCTION ALPHAITEM(DEC(6,0)) RETURNS CHAR(15)
LANGUAGE RPGLE
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION ALLOW PARALLEL
SIMPLE CALL
EXTERNAL NAME 'ISSBDP/SQLUDF(ALPHAITEM)'
For the record, I first tried changing AlphaItem to ALPHAITEM in my RPGLE
and it didn't have any effect. I agree function overloading would be very
useful like you can do in other programming languages. I wonder also if
adding PARAMETER STYLE to the CREATE FUNCTION statement would have helped.
Thanks for you help!
Ben Pforsich
Programmer Analyst
Bob Evans Farms, Inc.
I/S Department
Columbus, Ohio
Ben_Pforsich@xxxxxxxxxxxx
rob@xxxxxxxxx
Sent by:
rpg400-l-bounces@ To
midrange.com RPG programming on the AS400 /
iSeries <rpg400-l@xxxxxxxxxxxx>
cc
10/14/2005 10:29
AM Subject
Re: SQL UDF not found?
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
1 - This is one of those times that capitalization may matter. Change all
your D and P specs to refer to ALPHAITEM not AlphaItem.
2 - s20prd had better be 10p0. SQL UDF's support "Overloading". You
could have multiple ALPHAITEM's registered with different parameter sizes
and actually have it call different service programs, etc. To be able to
do this in plain RPG would be a programmers dream. For example, if you
had a ConvertDate subprocedure and it would automatically figure out if it
needed to convert from alpha, numeric, etc.
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
Ben_Pforsich@xxxxxxxxxxxx
Sent by: rpg400-l-bounces+rob=dekko.com@xxxxxxxxxxxx
10/14/2005 09:07 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To
rpg400-l@xxxxxxxxxxxx
cc
Fax to
Subject
SQL UDF not found?
I am trying to write a very simple SQL UDF from an RPGLE module in a
SRVPGM. I've registered the function ALPHAITEM which is same name as the
subprocedure exported in the module, but I am consistently getting the
error message "ALPHAITEM in *LIBL type *N not found." when I try to
execute
the following SQL from STRSQL and from an SQLRPGLE program:
select ALPHAITEM(s20prd)
from rsp200 where s20cmp = 'B1'
Below is the code with the steps I followed to create the service program
and register the SQL function. Any help would be greatly appreciated.
BTW, I have read the following article which was helpful in getting me
started:
http://faq.midrange.com/data/cache/185.html
<code>
h nomain
h debug
h option(*nodebugio : *nosrcstmt)
*************************************************************************
* Compile Instructions
*------------------------------------------------------------------------
* 1. Create RPGLE module from this source member:
* CRTRPGMOD MODULE(ISSBDP/ALPHAITEM) SRCFILE(ISSBDP/QRPGLESRC)
* SRCMBR(ALPHAITEM)
*
* 2. Create/update SQLUDF service program in ISSBDP with this
module:
* CRTSRVPGM SRVPGM(ISSBDP/SQLUDF) MODULE(ISSBDP/ALPHAITEM)
* EXPORT(*ALL)
*
* 3. Register this function by executing the following in STRSQL:
* CREATE FUNCTION ISSBDP/ALPHAITEM(INT) RETURNS CHAR(15)
* LANGUAGE RPGLE
* DETERMINISTIC
* NO SQL
* RETURNS NULL ON NULL INPUT
* NO EXTERNAL ACTION ALLOW PARALLEL
* SIMPLE CALL
* EXTERNAL NAME 'ISSBDP/SQLUDF(ALPHAITEM)'
*************************************************************************
* Procedure prototypes
*------------------------------------------------------------------------
* AlphaItem function
* returns the alphanumeric representation of a numeric item so
that
it
* contains at least 4-digits (ex: 23 -> '0023', 5603 -> '5603')
d AlphaItem pr 15a
d numItem 10p 0
*************************************************************************
* Procedure interfaces
*------------------------------------------------------------------------
p AlphaItem b export
*------------------------------------------------------------------------
d AlphaItem pi 15a
d numItem 10p 0
*------------------------------------------------------------------------
d Result s 15a
/free
select;
when numItem < 10;
Result = '000' + %char(numItem);
when numItem < 100;
Result = '00' + %char(numItem);
when numItem < 1000;
Result = '0' + %char(numItem);
other;
Result = %char(numItem);
endsl;
return Result;
/end-free
p AlphaItem e
</code>
Ben Pforsich
Programmer Analyst
Bob Evans Farms, Inc.
I/S Department
Columbus, Ohio
Ben_Pforsich@xxxxxxxxxxxx
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
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.
