Re: Help with UDFs or stored procedures

[Dan <dan27649@xxxxxxxxx>]

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.