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 '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.

Mapping SQL data types to Java data types

The following table shows how Derby maps specific SQL data types to Java data types: