Re: SQL UDF From RPG Program

Rob,

RPG doesn't support returned values from a PROGRAM.

The given PARAMETER STYLE SQL uses an output parameter as a pseudo
returned value.

ExternalUDF(
IN parameter (repeated),
OUT result,
IN parameter null indicator (repeated),
OUT result null indicator,
OUT sqlstate,
IN function name,
IN specific name,
OUT diagnostic message)

HTH,
Charles

On Tue, Jun 30, 2009 at 10:57 AM, <rob@xxxxxxxxx> wrote:

Well, your PI shows a plethora of parameters, and no return parameter - no
definition on the same line as the PI definition.  I would expect
something more in the line of:

    D SqlGetZipD      pi   8f
    D   pZipCode1                    5a
    D   pZipCode2                    5a

Wouldn't this match
CREATE FUNCTION DMIPGMS/GETZIPDIST ( CHAR(5), CHAR(5) )
   RETURNS DOUBLE
   ...
more?


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





From:
MattLavinder@xxxxxxxxxxxxxxxxxxx
To:
rpg400-l@xxxxxxxxxxxx
Date:
06/30/2009 10:04 AM
Subject:
SQL UDF From RPG Program
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx






Trying to write an SQL UDF using RPG.  Usually I put them in a service
program, but this time I was trying to do it with a regular program.

Here is the interface for the program.  The function accepts two zip codes
and returns the distance between them.

    D SqlGetZipD      pi
    D   pZipCode1                    5a
    D   pZipCode2                    5a
    D   pRetDist                     8f
    D   pInpInd                      5i 0
    D   pOutInd                      5i 0
    D   pOutSqlState                 5a
    D   pInpFuncName               139a   varying
    D   pInpSpecName               128a   varying
    D   pOutDiagTxt                 70a   varying

Here is the function code:

CREATE FUNCTION DMIPGMS/GETZIPDIST ( CHAR(5), CHAR(5) )
   RETURNS DOUBLE
   LANGUAGE RPGLE
   PARAMETER STYLE SQL
   DETERMINISTIC
   PROGRAM TYPE MAIN
   NO SQL
   RETURNS NULL ON NULL INPUT
   EXTERNAL NAME 'DMIPGMS/SQLGETZIPD';


Not sure what I am doing wrong.  Any help would be appreciated.

Thanks,
Matt

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
______________________________________________________________________
--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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.