Re: Help with UDFs or stored procedures

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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.