RE: Specify null parameter for SQL UDF

["Mackie, Roger L. (Precision Press)" <RLMackie@xxxxxxxxxxxxx>]

Thanks for the explanation. Now I'll go back to lurking while the wheels
turn and I figure out how to leverage this new knowledge.

Roger 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Peter Dow (ML)
Sent: Wednesday, April 05, 2006 1:16 PM
To: Midrange Systems Technical Discussion
Subject: Re: Specify null parameter for SQL UDF

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