When you declare a function or procedure using CREATE
FUNCTION/PROCEDURE, Derby
does not verify whether a matching Java
method exists. Instead, Derby
looks for a matching method only when you invoke the
function or procedure in a later SQL statement. At that time,
Derby
searches for a public, static method having the class and method name
declared in the EXTERNAL NAME clause of the earlier
CREATE FUNCTION/PROCEDURE statement. Furthermore, the Java types of
the method's arguments and return value must match the SQL types
declared in the CREATE FUNCTION/PROCEDURE statement. The
following may happen:
- Success - If exactly one Java method matches, then
Derby invokes it.
- Ambiguity - Derby
raises an error if more than one method matches.
- Failure - Derby
also raises an error if no method matches.
In mapping SQL data types to Java data types,
Derby considers the following
kinds of matches:
- Primitive match -
Derby looks for a primitive
Java type corresponding to the SQL type. For instance, SQL INTEGER matches Java
int.
- Wrapper match -
Derby looks for a wrapper
class in the java.lang or java.sql packages corresponding to the
SQL type. For instance, SQL INTEGER matches java.lang.Integer. For a
user-defined type (UDT),
Derby looks for the UDT's
external name class.
- Array match - For OUT and INOUT procedure arguments,
Derby
looks for an array of the corresponding primitive or wrapper type. For
instance, an OUT procedure argument of type SQL INTEGER matches
int[] and Integer[].
- ResultSet match - If a procedure is declared to return n
RESULT SETS, Derby looks for
a method whose last n arguments are
of type java.sql.ResultSet[].
Derby resolves function and
procedure invocations as follows:
- Function - Derby
looks for a method whose argument and
return types are primitive matches or wrapper matches for
the function's SQL arguments and return value.
- Procedure - Derby
looks for a method which returns void and
whose argument types match as follows:
- IN - Method arguments are
primitive matches or wrapper matches for the
procedure's IN arguments.
- OUT and INOUT - Method arguments are
array matches for the
procedure's OUT and INOUT arguments.
In addition, if the procedure returns n RESULT SETS,
then the last n arguments of the Java method must be of type
java.sql.ResultSet[].
Derby provides a tool,
SignatureChecker, which can identify any SQL functions or
procedures in a database that do not follow these argument matching rules. See
the Derby Tools and Utilities Guide for details.
Example of argument matching
The following function...
CREATE FUNCTION TO_DEGREES
( RADIANS DOUBLE )
RETURNS DOUBLE
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'acme.MathUtils.toDegrees'
...would match all of the following methods:
public static double toDegrees( double arg ) {...}
public static Double toDegrees( double arg ) {...}
public static double toDegrees( Double arg ) {...}
public static Double toDegrees( Double arg ) {...}
Note that Derby would raise
an exception if it found more than one matching method.
