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.
- 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, then 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[].