db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Create function problem
Date Wed, 01 Mar 2006 00:05:17 GMT
Jean T. Anderson wrote:

> John English wrote:
> 
>>I have a function to round a Double to a specified number of decimal
>>places:
>>
>>  public static final Double derbyDouble (Double value, Integer places) {
>>    ...
>>  }
>>
>>I've declared it to Derby like so:
>>
>>  CREATE FUNCTION DoubleFormat (value  FLOAT,
>>                                places INTEGER)
>>  RETURNS FLOAT
>>  PARAMETER STYLE Java
>>  LANGUAGE Java
>>  EXTERNAL NAME 'utils.Utils.derbyDouble';
>>
>>This works if I use "double" and "int" but not Double and Integer (as in,
>>the methos is found & used corrrectly if I use primitive types but it isn't
>>found if I use the corresponding wrapper types). I've also tried Float,
>>which
>>is no better.
>>
>>The big difference is that, using primitive types, I can't handle nulls...
>>
>>Anyone got any ideas whhat I'm doing wrong?
> 
> 
> Derby maps a SQL INTEGER to Java int (a primitive) and passing SQL NULLs
> isn't supported for primitive types. There's an FAQ on this at
> http://db.apache.org/derby/faq.html#null_args that also points to the
> mail list topic that fed the FAQ.

That FAQ is a bit out of date (I think, I can't see it now, get timeouts
connecting to the site).

There are two options for a function:

1) Declare the function RETURNS NULL ON NULL INPUT, this means that if
any parameter is NULL the result is NULL and the Java method is never
called. There are a couple of bugs outstanding on this which will be
fixed in 10.2.
http://issues.apache.org/jira/browse/DERBY-479
http://issues.apache.org/jira/browse/DERBY-1030

CREATE FUNCTION DoubleFormat (value  FLOAT,
                               places INTEGER)
RETURNS FLOAT
PARAMETER STYLE Java
LANGUAGE Java
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'utils.Utils.derbyDouble'

2) In the external name clause define the Java method signature, e.g.

CREATE FUNCTION DoubleFormat (value  FLOAT,
                               places INTEGER)
RETURNS FLOAT
PARAMETER STYLE Java
LANGUAGE Java
EXTERNAL NAME
'utils.Utils.derbyDouble(java.lang.Float,java.lang.Integer)'

This will then resolve to the method with that signature (assuming it
exists).

Method signature support was added in 10.1.

Dan.



Mime
View raw message