AW: AW: Specify null parameter for SQL UDF

["Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx>]

Hi Peter,

Sorry, but it works (at least on release V5R2M0)!

Here is an example:
1. A simple RPG-Function to center a text
P Center          B                   Export

 

D Center          PI                  like(Text)

D   ParText                           like(Text) const

D   ParLen                       5I 0            const Options(*NoPass)

 

D LenParText      C                   const(%Size(ParText))

D UsedLength      S              5I 0

D RetText         S                   like(Text)

D Start           S              3U 0

  //--------------------------------------------------------------------
 /Free

   If %Parms >= 2 and ParLen > *Zeros;

      UsedLength = ParLen;

   Else;

      UsedLength = LenParText;

   EndIf;     
                                      
   Select;                                                           
   When ParText = *Blanks;                                           
        Return *Blanks;                                            
   When %Len(%Trim(ParText)) = UsedLength;                           
        Return ParText;                                            
   Other;                                                            
        Start = %Int((UsedLength - %Len(%Trim(ParText))) / 2) + 1;   
        %Subst(RetText: Start) = %Trim(ParText);                     
        Return RetText;                                              
   EndSl;                                                            
/End-Free                                                           
 Center          E     

2. With the following SQL script I created and called this RPG function
successfully with only a single and with both parameters.
Note: Both function need different specific Names!

-- 1. CenterText with only 1 Parameter
Drop Function MYSCHEMA/CENTERTEXT (Char(20));  

CREATE FUNCTION MYSCHEMA/CENTERTEXT (
        PARTEXT CHAR(20) )
        RETURNS CHAR(20)
        LANGUAGE RPGLE
        SPECIFIC MYSCHEMA/CENTER1
        DETERMINISTIC 
        NO SQL
        CALLED ON NULL INPUT
        DISALLOW PARALLEL
        NOT FENCED
        EXTERNAL NAME 'MYSCHEMA/CENTER(CENTER)'
        PARAMETER STYLE SQL ;  

COMMENT ON SPECIFIC FUNCTION MYSCHEMA/CENTER1
        IS 'Center Text' ;

-- 2. CenterText with 2 Parameters
Drop Function ITSO4710/CENTERTEXT (Char(20), SMALLINT);  

CREATE FUNCTION MYSCHEMA/CENTERTEXT ( 
        PARTEXT CHAR(20),
      PARLEN  SMALLINT) 
        RETURNS CHAR(20)   
        LANGUAGE RPGLE 
        SPECIFIC MYSCHEMA/CENTER2
        DETERMINISTIC 
        NO SQL 
        CALLED ON NULL INPUT 
        DISALLOW PARALLEL 
        NOT FENCED 
        EXTERNAL NAME 'MYSCHEMA/CENTER(CENTER)' 
        PARAMETER STYLE SQL ; 
  
COMMENT ON SPECIFIC FUNCTION MYSCHEMA/CENTER2 
        IS 'Center Text' ;

Select centerText(MyFld1), centerText(MyFld2), 
       cast(Length(MyFld2) as SmallInt))) 
   from MYSCHEMA/MYTABLE;

Select centerText(Cast('ABC' as Char(20))), 
       centerText(Cast('XXX' as Char(15)), cast(15 as SmallInt)) 
from SYSIBM/SYSDUMMY1;

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: Mittwoch, 5. April 2006 21:00
An: Midrange Systems Technical Discussion
Betreff: Re: AW: Specify null parameter for SQL UDF

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> /
>
>
>