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:

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.

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.