Argument matching When you declare a function or procedure using CREATE FUNCTION/PROCEDURE, does not verify whether a matching Java method exists. Instead, looks for a matching method only when you invoke the function or procedure in a later SQL statement. Argument matching CREATE FUNCTION statement CREATE PROCEDURE statement

At that time, 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 invokes it.
  • Ambiguity - raises an error if more than one method matches.
  • Failure - also raises an error if no method matches.

A procedure or function that takes varargs must resolve to a varargs Java method.

In mapping SQL data types to Java data types, considers the following kinds of matches:

  • Primitive match - looks for a primitive Java type corresponding to the SQL type. For instance, SQL INTEGER matches Java int.
  • Wrapper match - 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), looks for the UDT's external name class.
  • Array match - For OUT and INOUT procedure arguments, 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, looks for a method whose last n arguments are of type java.sql.ResultSet[].

resolves function and procedure invocations as follows:

  • Function - 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 - 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[].

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

...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 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 maps specific SQL data types to Java data types.

SQL and Java type correspondenceThis table lists the SQL datatypes and shows how maps them to Java primitive and wrapper types. SQL Type Primitive Match Wrapper Match BOOLEAN boolean java.lang.Boolean SMALLINT short java.lang.Integer INTEGER int java.lang.Integer BIGINT long java.lang.Long DECIMAL None java.math.BigDecimal NUMERIC None java.math.BigDecimal REAL float java.lang.Float DOUBLE double java.lang.Double FLOAT double java.lang.Double CHAR None java.lang.String VARCHAR None java.lang.String LONG VARCHAR None java.lang.String CHAR FOR BIT DATA byte[] None VARCHAR FOR BIT DATA byte[] None LONG VARCHAR FOR BIT DATA byte[] None CLOB None java.sql.Clob BLOB None java.sql.Blob DATE None java.sql.Date TIME None java.sql.Time TIMESTAMP None java.sql.Timestamp XML None None User-defined type None Underlying Java class