On 1/6/14 6:09 AM, Rick Hillegas wrote:
On 1/6/14 4:47 AM, Tim Dudgeon wrote:
I'm trying to write a function that performs aggregation and I'm not
clear on how to handle the varargs part.
Lets assume I was wanting to write a function that does averaging,
and handles null values correctly.
I'm defining my function like this:
CREATE FUNCTION MYAVG
( VALS DOUBLE ... )
RETURNS DOUBLE
PARAMETER STYLE DERBY
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'myorg.misc.derby.functions.MyFunctions.myAverage;
What does the signature of the Java method need to be?
I've tried:
static Double myAverage(Double[] vals) { }
static Double myAverage(Double... vals) { }
Both of these fail with:
java.sql.SQLSyntaxErrorException: No method was found that matched
the method call
myorg.misc.derby.functions.MyFunctions.myAverage(double...), tried
all combinations of object and primitive types and any possible type
conversion for any parameters the method call may have
Seems like its wanting
static Double myAverage(double... vals) { }
but if that's the case how are nulls handled?
Thanks
Tim
Thanks for tripping across this, Tim. Looks like there's a bug in
here. Works for Integer args but not Double args. Stand by while I
take a look...
Thanks,
-Rick
Hi Tim,
Well, I'm puzzled. Now I can't reproduce the problem I thought I was
seeing. I have written a script which tests all possible datatypes with
varargs. The script runs cleanly. The following small test case works
too. Compile the following class...
public class ww
{
public static Double doubleAverage( Double... vals )
{
double result = 0;
for ( Double val : vals )
{
result += val.doubleValue();
}
return result / vals.length;
}
}
...then the following script runs cleanly for me...
connect 'jdbc:derby:memory:db;create=true';
create function doubleAverage( vals double... ) returns double
language java parameter style derby no sql
external name 'ww.doubleAverage';
values doubleAverage( 1.11, 2.22, 3.33 );
values doubleAverage( 1.0, 2.0, 3.0 );
Maybe your problem is that your methods are not public.
By the way: If your ultimate problem is an aggregation problem, you may
be interested in the user defined aggregates feature which was
introduced by release 10.10.1:
http://db.apache.org/derby/docs/10.10/devguide/cdevspecialuda.html
Hope this helps,
-Rick
