Argument matching
Derby Reference Manual
206
Argument matching
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 'example.MathUtils.toDegrees'
