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:

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

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

Derby resolves function and procedure invocations as follows:

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'

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

Table 1. SQL and Java type correspondence
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