Re: AW: Specify null parameter for SQL UDF

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

Hi Birgitta,

Unfortunately, it appears that SQL does not pass a minimal operational 
descriptor, so %parms is "unreliable" (the quote is from the RPGLE 
Reference manual) -- actually debug showed it to be reliably -1 <grin>.  
I ended up having two uniquely named procedures in the service program, 
but the same name for the UDF.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Hauser, Birgitta wrote:
> Hi Peter,
>
> you have to overload your UDF.
> That means you have to create a second UDF with the same name in the same
> library, but without parameters.
>
> create function UTLIB/ACCTGMONTH () 
>        returns integer
>        language RPGLE                                 
>        parameter style GENERAL WITH NULLS             
>        not deterministic                              
>        no SQL                                
>        called on null input                           
>        no external action                             
>        external name 'UTLIB/SVCBUS(ACCTGMONTH)'
>
> The new procedure gets its own signature and now can be called with or
> without parameter.
>
> Passing a NULL Parameter, does not mean the parameter is not passed, but a
> NULL pointer is passed.
>
> In RPG you'll use *OMIT to pass a NULL-Pointer-Parameter to an other
> procedure.
> In RPG you can check passed NULL-Pointers with ParmXYZ = *NULL.
> If the parameter is optional and not passed, no pointer gets passed.
> Checking a parameter that is not passed with = *NULL will cause a MCH3601
> failure.
> To check the number of parameter passed you'll use %Parms.
> Note: Ommitted parameter are counted as passed! 
>
> Just an information for your RPG procedure.
> I'd add OPTIONS(*NOPASS) to all optional parameters.
> This allows you to check the number of passed parameters using %PARMS.
>
>  d AcctgMonth...                            
>  d                 pr            10i 0      
>  d  amDate                         d   const Options(*NoPass)
>  d  amDateNull                   10i 0       Options(*NoPass)     
>  d  amResultNull                 10i 0       Options(*NoPass)
>
> Birgitta
>
> "If you think education is expensive, try ignorance"
> (Derek Bok)
>  
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Peter Dow (ML)
> Gesendet: Dienstag, 4. April 2006 21:35
> An: MIDRANGE-L@xxxxxxxxxxxx
> Betreff: Specify null parameter for SQL UDF
>
> Hi Everyone,
>
> How do I call a UDF with a null parameter? I have a UDF created to 
> return the accounting month for a given date (the only parameter), but 
> if no parameter is given, it will return the current accounting month.
>
> It works fine when I pass it a date, e.g.
>
> SELECT ACCTGMONTH(CURRENT_DATE) FROM ANYFILE
>
> but complains (says it cannot find ACCTGMONTH) if I use it like this:
>
> SELECT ACCTGMONTH() FROM ANYFILE
>
> I understand that it identifies a UDF based on the name and the input 
> parameter types, but I don't understand how to tell it a particular 
> parameter may be optional.
>
> I created it with the following:
>
> create function UTLIB/ACCTGMONTH (date) returns integer
>        language RPGLE                                 
>        parameter style GENERAL WITH NULLS             
>        not deterministic                              
>        no SQL                                
>        called on null input                           
>        no external action                             
>        external name 'UTLIB/SVCBUS(ACCTGMONTH)'
>
> ACCTGMONTH is a procedure within a service program, with prototype like 
> this:
>
>  d AcctgMonth...                            
>  d                 pr            10i 0      
>  d  amDate                         d   const
>  d  amDateNull                   10i 0      
>  d  amResultNull                 10i 0
>
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /
>
>
>