Re: Specify null parameter for SQL UDF

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

Hi Roger,

If I understand your first question correctly, the answer is no, you do 
have to define those parameters.  The complete script is

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)'


and the parameter is a date type field.  The function returns an integer.

By saying the parameter style is GENERAL WITH NULLS, what SQL actually passes 
to my RPGLE program is what I have in the prototype (which I've corrected since 
my original post -- indicator variables are small integer, not integers):

d AcctgMonth...                            
d                 pr            10i 0      
d  amDate                         d   const
d  amDateNull                    5i 0
d  amResultNull                  5i 0

amDate is the input parameter; amDateNull is the null indicator array (since 
there's only 1 parameter I didn't bother to make it an array); amResultNull is 
a single null indicator field that my program uses to inform SQL whether or not 
the result I'm providing is null or not.  A value of zero says the associated 
variable is not null; a value of -1 says it is.

Null indicators are not the same as RPG indicators. I got confused somewhere but I 
finally tracked down in the SQL reference manual where it states that "The variable 
designated by the second host-identifier is called an indicator variable and must have a 
data type of small integer."

The problem is, when I use this UDF with no parameter, e.g. select AcctgMonth() 
from xyz, I should have options(*nopass) on my prototype, but I can't because 
then I would have to have it on the null indicators, and I can't because SQL is 
always going to pass those.

So I agree with Elvis Budmilic when he says "As I suspected, empty parenthesis are treated as 
a separate UDF (no arguments hence different signature) so I get "not found" error when 
running it like that.  To handle this case, I suggest you create another function in the same 
service program that handles this case and then register a separate UDF. That way you can reuse the 
code base already in your SRVPGM."


*Peter Dow*/
Dow Software Services, Inc.
909 793-9050

/


Mackie, Roger L. (Precision Press) wrote:
> This looks like a time when I can learn something about SQL UDFs that I
> didn't know before. If the UDF registration script is complete, it looks
> like you can create a UDF that accepts parameters without defining those
> parameters. Is that correct?
>
> For an RPGLE service program to accept null parameters, I thought you
> had to specify Options(*Omit) on those parameters. I don't see that
> keyword on the AcctgMonth parameters. Does it work differently when you
> wrap the service program in a UDF?
>
> Thanks for any answers you can provide,
> Roger Mackie
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
> Sent: Tuesday, April 04, 2006 4:07 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Specify null parameter for SQL UDF
>
> I think your UDF syntax is fine, especially being that you did specify
> CALLED ON NULL INPUT.
>
> Perhaps empty parenthesis are not treated as NULL input?
> Test it with: 
>    SELECT ACCTGMONTH(NULL) FROM SYSIBM/SYSDUMMY1 and verify if that's
> the case or not.
>
> If it is, you'll have to create another wrapper UDF that takes no
> parameters and perhaps maps to the original one.
>
> Elvis
>
> -----Original Message-----
> Subject: 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>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
>