Re: Specify null parameter for SQL UDF

Hi Roger,

create function UTLIB/ACCTGMONTH (date) returns integer

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