Re: Help with UDFs or stored procedures -- RPG400-L

Finally able to devote a little more time to this.  I got the extra
parameters in the RPG service program like you suggested.  Tried using it in
interactive SQL, however I am getting a MCH3601 (Pointer not set for
location referenced) error at the point where I assign a zero value to the
parameter which is the result null indicator.

FWIW:

CREATE FUNCTION dandev/TRUEDATE
 (DEC(2,0), DEC(2,0), DEC(2,0), DEC(2,0))
RETURNS DATE
EXTERNAL NAME 'DANDEV/TRUEDATE(TRUEDATE)'
LANGUAGE RPGLE
NO SQL
NOT DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
PARAMETER STYLE GENERAL WITH NULLS

RPG service program:

    d/copy testsrc,TrueDatePr

    p TrueDate        b                   export

    d TrueDate        pi              d
    d  p_CenturyIn                   2p 0
    d  p_YearIn                      2p 0
    d  p_MonthIn                     2p 0
    d  p_DayIn                       2p 0
    d  pni_CenturyIn                 5i 0
    d  pni_YearIn                    5i 0
    d  pni_MonthIn                   5i 0
    d  pni_DayIn                     5i 0
    d  pni_Result                    5i 0

    d                 ds
    d DateIn                         8s 0
    d  inCentury                     2s 0 Overlay( DateIn : 1 )
    d  inYear                        2s 0 Overlay( DateIn : 3 )
    d  inMonth                       2s 0 Overlay( DateIn : 5 )
    d  inDay                         2s 0 Overlay( DateIn : 7 )

    d DateOut         s               d   Inz

     /free
      inCentury = p_CenturyIn ;
      inYear    = p_YearIn    ;
      inMonth   = p_MonthIn   ;
      inDay     = p_DayIn     ;
      Test(DE) *iso DateIn ;
      If %error ;
        pni_Result = -1 ;   // Null Indicator is set ON
      Else ;
        DateOut = %date( DateIn : *iso ) ;
        pni_Result = 0 ;   // Null Indicator is set OFF
      Endif ;

      *inLR = *off ;
      Return DateOut ;
     /end-free
    p                 e

Any ideas on how to resolve this would be appreciated!

And, since I'm out of here in a few minutes, I will wish all of you a Merry
Christmas!

- Dan

On 12/20/06, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Dan -

It is easier than it looks at first - you have it partly figured out.

The parameters used in a SELECT statement are just those that you
define in the CREATE FUNCTION statement. So in the example you would
use 2 CHAR(3) columns (or expression) and a date column (or
expression). And the result has to be put into a numeric column if
used in an UPDATE, say.

But your RPG has to have more parameters than those 3. And it has to
have a return value. The RPG service program would have, in this
case, a prototype with 2 3-character parameters and a date parameter,
followed by - what I do - 3 integers (5 & 0 I think - 2 byte signed)
for the null indicators of the parameters, and a 4th integer for the
null indicator of the return value.

So you would have, in this case, 7 parameters in the RPG function
prototype. To return a NULL, simply set the last parameter to -1 ---
the SQL engine will convert that to what it needs.

Here is a possible prototype - you can use separate parameters for
the indicator "array" - I like it because I can use meaningful names.

DYOURPROC         PR            10P 5
D ORIG_CCY                       3A
D TRGT_CCY                       3A
D FX_DATE                         D
D NIORIG_CCY                     5I 0
D NITRGT_CCY                     5I 0
D NIFX_DATE                      5I 0
D NIRETURNVAL                    5I 0

***NOTE*** I strongly recommend using VARCHAR instead of CHAR for all
character parameters - even single-character ones. This lets you use
simple character literals in the call of the function - otherwise you
have to cast the values to CHAR or use non-varying CHAR columns. If
you do create this function with VARCHARs, then the first 2
parameters would have the VARYING keyword.

Basically it just works. Now if you want to be more SQL-ish, you can
try the SQL style. This lets you return an SQL state value and a
short message text - not much more difficult, actually.

In a SELECT WHERE clause you could say WHERE yourproc('aaa', 'bbb',
current date) is not null

Section 15.3 of the redbook explains this pretty well - i think -
after i studied it a while!!!

HTH
Vern

At 02:54 PM 12/19/2006, you wrote:

>On 12/18/06, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
> >
> > Dan - you are now in the world of using the SQL or the GENERAL WITH
> > NULLS parameter style - that means you have to add indicators for
> > NULL. This means you cannot use an existing service program as is.
> > Check the redbook for what the parameters need to be. Basically you
> > set an extra parameter to indicate a NULL.
>
>
>Thanks Vern.  I scanned through the redbook.  Sorry, but this is over my
>head, as in, the space shuttle is over my head.  ;-)  I found the
PARAMETER
>STYLE GENERAL WITH NULLS, but...   The example shows:
>
>     CREATE FUNCTION SAMPLEDB01.GET_FX_RATE (
>           ORIG_CCY CHAR(3) ,
>           TRGT_CCY CHAR(3) ,
>           FX_DATE DATE )
>     RETURNS DECIMAL(10, 5)
>     LANGUAGE COBOLLE
>     SPECIFIC SAMPLEDB01.GET_FX_RATE01
>     DETERMINISTIC
>     READS SQL DATA
>     RETURNS NULL ON NULL INPUT
>     EXTERNAL NAME 'SAMPLEDB01/UDF_CBL(GET_FX_RATE)'
>     PARAMETER STYLE GENERAL WITH NULLS;
>
>and explains:
>
>    For a UDF defined as in Example 15-10, the HLL
>    program will have four input parameter and one
>    output parameter, being the fourth a vector of
>    null indicators corresponding to the null state
>    for the first three parameters. The fifth
>    parameter corresponds to the null state for the
>    result the UDF will return.
>
>SQL Reference says that, after the input parms specified on the on the
>CREATE FUNCTION statement:
>     -> An additional argument is passed for an indicator variable array.
>     -> A parameter for the indicator variable for the result.
>How are these two items defined in SQL?  Are they implicitly defined?
>
>I guess I understand what I need to do in the RPG program and in the
CREATE
>FUNCTION statement, but what do I need to do to use the returned null
>indicator in an SQL statement that uses the UDF?  Can I even use this in
a
>SELECT statement?  I did not see a usage example in that redbook, nor in
the
>SQL Reference or SQL Programming manuals.
>
>TIA,
>Dan
>--
>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.