Re: SQL UDF From RPG Program

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.