Re: Specify null parameter for SQL UDF

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

Hi Birgitta,

Hey, don't be sorry!  If it works, great.  I had to define a variable to 
hold %parms and debug it to see that it was getting %parms = -1.  My 
procedure didn't blow up, it just took the wrong logic path because of 
the unexpected value of %parms.

I do see some differences though -- you used Parameter Style SQL, while 
I was using General With Nulls.

And it looks to me like you let the system define the specific names for 
the two functions, based on the function names and their parameters 
(i.e. I didn't see the "SPECIFIC name" clause of the CREATE FUNCTION 
statement, in which case the system generates one).  I did the same, 
although I had to define two difference procedures, one for the function 
with parameters and the other for the one without.

I also notice that you didn't define the other parameters that are 
passed when using Parameter Style SQL; however, I doubt that has 
anything to do with it one way or another.  I was on v5r3, but again, 
I'm not sure that had anything to do with it either.  My guess is that 
the difference in behavior is due to the parameter style.

Elvis Budimlic suggested using style DB2SQL in order to be able to use 
the SCRATCHPAD clause, so I may try %parms again with that style.

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

Hauser, Birgitta wrote:
> 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)