Re: SQL UDF From RPG Program

I am very familiar with the concept of overloading. In fact, long for the
day that RPG procedures support it (not to spark a debate). I was playing
with the parameter types and interface decleration when my function was not
found because I knew it had to have something to do with the parameters.
In this case, I wasn't aware that SQL converts literals to varchar. Now
everything makes sense and works great.

Thanks again for all the help.



|------------>
| From: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|rob@xxxxxxxxx |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx> |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Date: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|06/30/2009 11:42 AM |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Subject: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Re: SQL UDF From RPG Program |
>--------------------------------------------------------------------------------------------------------------------------------------------------|





The concept is called "overloading". You can have the exact same UDF
name, with different parameters, parameter sizes, even decimal versus
character, etc. Then it could call a different RPG subprocedure based on
that.
CREATE FUNCTION DMIPGMS/GETZIPDIST ( CHAR(5), CHAR(5) )
RETURNS DOUBLE
...
EXTERNAL NAME 'DMIPGMS/SQLGETZIPD';


CREATE FUNCTION DMIPGMS/GETZIPDIST ( CHAR(5), DEC(9 0) )
RETURNS DOUBLE
...
EXTERNAL NAME 'DMIPGMS/SQLGETZCD'; // Notice different?


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
MattLavinder@xxxxxxxxxxxxxxxxxxx
To:
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date:
06/30/2009 11:08 AM
Subject:
Re: SQL UDF From RPG Program
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx

What error are you getting and when are you getting it?

The message was "GETZIPDIST in DMIPGMS type *N not found". I finally
managed to get when the function working via a service program call. My
problem was the literal value I was passing in my call to the function.

The following code doesn't work:

SELECT ZIPCODE FROM ZIPCENTER WHERE GETZIPDIST(ZIPCOD, '12345') <= 25

But this code DOES work:

SELECT ZIPCODE FROM ZIPCENTER WHERE GETZIPDIST(ZIPCOD, CHAR('12345')) <=
25

I haven't had much experience with SQL UDF's on IBM i, so maybe the
external UDFs won't play well with literal values. Using the CHAR
function
isn't a big deal, but out of curiosity, is there a way to define the UDF
so
the literal would be accepted without casting it?