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

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 youdefine in the CREATE FUNCTION statement. So in the example you woulduse 2 CHAR(3) columns (or expression) and a date column (orexpression). And the result has to be put into a numeric column ifused in an UPDATE, say.

But your RPG has to have more parameters than those 3. And it has tohave a return value. The RPG service program would have, in thiscase, 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 thenull indicator of the return value.

So you would have, in this case, 7 parameters in the RPG functionprototype. 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 forthe 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 allcharacter parameters - even single-character ones. This lets you usesimple character literals in the call of the function - otherwise youhave to cast the values to CHAR or use non-varying CHAR columns. Ifyou do create this function with VARCHARs, then the first 2parameters would have the VARYING keyword.

Basically it just works. Now if you want to be more SQL-ish, you cantry the SQL style. This lets you return an SQL state value and ashort 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.