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:

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 Java DB 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:

Table 1. SQL and Java type correspondence
SQL type Primitive match Wrapper match
SMALLINT short java.lang.Integer
INTEGER int java.lang.Integer
BIGINT long java.lang.Long
DECIMAL - java.math.BigDecimal
NUMERIC - java.math.BigDecimal
REAL float java.lang.Float
DOUBLE double java.lang.Double
FLOAT double java.lang.Double
CHAR - java.lang.String
VARCHAR - java.lang.String
LONG VARCHAR - java.lang.String
CHAR FOR BIT DATA byte[] -
VARCHAR FOR BIT DATA byte[] -
LONG VARCHAR FOR BIT DATA byte[] -
CLOB - java.sql.Clob
BLOB - java.sql.Blob
DATE - java.sql.Date
TIME - java.sql.Time
TIMESTAMP - java.sql.Timestamp
XML - -
User-defined type - Underlying Java class