Re: Specify null parameter for SQL UDF -- MIDRANGE-L

Hi Elvis,

I came to pretty much the same conclusion. However, rather than have acompletely different procedure to handle the null case, I tried aprototype like this:

d AccountingMonth...d pi 10i 0d amDate d options(*nopass)

d  amDateNull                    5i 0 options(*nopass)
d  amResultNull                  5i 0 options(*nopass)

I realize you're a C programmer, but the idea here is that I can thencheck a built-in function, %parms, which returns the number ofparameters actually passed, to see if the parameter was actuallypassed. Unfortunately, it appears that SQL is one of those programsthat the RPGLE manual mentions that does not pass a minimal operationaldescriptor, so %parms is unreliable; in fact, it is apparently always -1.

Another interesting thing I've noticed here is that although my serviceprogram is compiled with activation group *CALLER, the static variablesI have defined are not static, i.e. they're reinitialized for everyrecord returned by the SELECT statement. The SCRATCHPAD parameter onthe CREATE FUNCTION statement says it's only allowed for Java programs;so how does an RPGLE program get some static storage?

It looks like I'll have to have a completely separate procedure in myservice program to handle the null case, and I'll have to take theperformance hit because of the lack of static storage.


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





Elvis Budimlic wrote:

Peter, bear with me as this is going to be a long post, but it should
resolve your issue (I hope).

My bad on suggesting that test without testing it myself.  Reason you're
getting the error with this is because it's looking for column named NULL in

SYSIBM1 and that of course does not exist.Better test would be this:


CREATE TABLE UTLIB/T1 (F1 DATE );
INSERT INTO UTLIB/T1 VALUES(NULL);
SELECT ACCTGMONTH(F1) from UTLIB/T1;

As you see I've created a table with a date field and then inserted a single
NULL field in it.  This is just so we can test NULL input to your UDF.

I have also created a dummy UDF that sort of does what you're doing in
yours.  I code in C so my sample is also in C but you should be able to
follow it as it's bare bones.  Here is the sample function:

int ACCTGMONTH(char * in, /* first parm */

   short * inNull,                      /* null indicator array  */
   short * outNull                      /* output null indicator */

){*outNull = 0; /* never NULL */

  if (inNull[0] == -1)                  /* if NULL passed in     */
    return -1;                          /* output negative one   */
  else                                  /* if real value passed  */
    return 1;                           /* output positive one   */
}

I usually don't code with GENERAL WITH NULLS parameter style, but it is
relatively straight forward.
We have one input variable, so our inNull null indicator array of 2 byte
binaries only has one element.  I check if it's null by comparing it against
negative 1.
I don't have any logic for the output value but rather just output -1 if
NULL was passed in and 1 if incoming argument was non-NULL.

I don't code in RPG so I can't tell you if your function declaration is
valid or not, but that's likely where the problem is.

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.

Good luck.

Elvis

-----Original Message-----
Subject: Re: Specify null parameter for SQL UDF

Hi Elvis,

I tried it and got "Column NULL not in specified tables."

Another wrapper would work, but then what's the point of "CALLED ON NULLINPUT"? There should be a way to use it...shouldn't there?

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

Elvis Budimlic wrote:

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 toreturn the accounting month for a given date (the only parameter), butif 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 inputparameter types, but I don't understand how to tell it a particularparameter may be optional.
I created it with the following:

create function UTLIB/ACCTGMONTH (date) returns integer
language RPGLEparameter style GENERAL WITH NULLSnot deterministicno SQLcalled on null inputno external actionexternal name 'UTLIB/SVCBUS(ACCTGMONTH)'
ACCTGMONTH is a procedure within a service program, with prototype likethis:
d AcctgMonth...d pr 10i 0d amDate d constd amDateNull 10i 0d amResultNull 10i 0
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx>