Re: SQL UDF not found? -- RPG400-L

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.