1.9.1 Specification of external routines

External routines can be specified in CREATE PROCEDURE and CREATE FUNCTION statements because they can be used to define Java procedures and Java functions as external routines.

Organization of this subsection
(1) Format
(2) Explanation
(3) Rules

(1) Format

external-routine-name ::= 'JAR-file-name:Java-method-name [Java-signature]'

Java-method-name ::= Java-class-name.method-identifier
Java-class-name ::= [package-name.]class-identifier
Java-signature ::= ([Java-parameters])[returns type-name]
Java-parameter ::= type-name[,type-name]...

(2) Explanation

JAR-file-name
Specifies the name of an archive file, which is a set of classes or packages defined in Java. The JAR file name should not be specified as a path name. The length of a JAR file name cannot exceed 255 bytes.
method-identifier
Specifies the identifier of the Java method in which the actual processing is coded. The length of a method identifier cannot exceed 255 bytes.
package-name
Specifies the name of a package, which is a set of classes defined in Java.
class-identifier
Specifies the identifier of the class in which the Java method is defined. The length of the package name and class identifier together cannot exceed 255 bytes.
returns type-name
Specifies the Java type name associated with the arguments and return values for the Java method. If a Java procedure is being defined, either specify void (no return values) for type name of the return values or omit returns type-name.
If a Java function is being defined, returns type-name must be specified, except that it is not required when Java signature is omitted. Also in the case of a Java function, void (no return values) cannot be specified as the return values type name.
type-name[,type-name]...
Specifies the Java type names associated with the arguments and the return values of the Java method. Java type names must conform to the following rules:
  1. The type names of Java method parameters coded in Java parameters should be coded in the order of the SQL parameter names that are specified in CREATE PROCEDURE or CREATE FUNCTION.
  2. All Java method parameter type names associated with the SQL parameter names must be coded.
  3. If the maximum number of the results sets in the DYNAMIC RESULTS SET clause of the CREATE PROCEDURE command is equal to or greater than 1, specify at the end of the Java parameters the type name java.sql.ResultSet[] and a value that is no more than the number of specified results sets.
  4. When a function is defined with the CREATE FUNCTION command, specify Java data types that can be mapped to HiRDB data types.
    In a procedure defined in CREATE PROCEDURE, if the input/output mode for the SQL parameters is IN, specify Java data types that can be mapped to HiRDB data types.
    In a procedure defined in CREATE PROCEDURE, if the input/output mode for the SQL parameters is OUT or INOUT, specify a one-dimensional array for Java data types that can be mapped to HiRDB data types.
    For example, with respect to the INTEGER or BLOB output parameters, specify either java.lang.Interger[] or byte[][]. For details on Java data types that can be mapped to HiRDB data types, see 1.9.2 Type mapping.

(3) Rules

  1. If the Java signature is omitted, the Java data type is determined from the HiRDB data type and the SQL parameter input/output mode that are specified in the Java procedure or Java function definition, according to the following rules:
    • If a function is defined using CREATE FUNCTION, the data type of the Java method is determined according to the mapping rules.
    • If the input/output mode for the SQL parameter of a procedure defined in CREATE PROCEDURE is IN, the data type of the Java method is determined according to the mapping rules.
    • If the input/output mode for the SQL parameter of a procedure defined in CREATE PROCEDURE is OUT or INOUT, the data type of the Java method is determined according to the one-dimensional array type of the mapping rules.
    • If the Java signature is omitted by specifying the maximum number of results sets in the DYNAMIC RESULTS SET clause of CREATE PROCEDURE as being equal to or greater than 1, java.sql.ResultSet[] type parameters equal to the maximum number of results sets are added as Java method parameters to the data type determined according to the mapping rules.
    For example, either java.lang.Integer[] or byte[][] is determined with respect to the INTEGER or BLOB output parameters. For the mapping rules, see 1.9.2 Type mapping.
  2. External routine names must be enclosed in single quotation marks (').
  3. The only Java method that can be specified in an external routine name is the class method that is declared as static in a class definition.
  4. The following character sets can be specified in the various items:
    • JAR file names
      Upper- and lowercase alphabetic characters
      Numeric characters, _ (underline), $ (dollar sign), . (period), and - (hyphen)
    • Class identifiers, method identifiers, and type names
      Upper- and lowercase alphabetic characters
      Numeric characters, _ (underline), and $ (dollar sign)
    • Package names
      Upper- and lowercase alphabetic characters
      Numeric characters, _ (underline), $ (dollar sign), and . (period)
  5. A numeric cannot be specified as the first character of a package name, class identifier, method identifier, or type name.
  6. If a type name with a package name omitted is coded, HiRDB interprets the type name as containing the following package name:
    Default coding formatType name containing a package name, as interpreted by HiRDB
    Integerjava.lang.Integer
    Shortjava.lang.Short
    BigDecimaljava.math.BigDecimal
    Doublejava.lang.Double
    Floatjava.lang.Float
    Stringjava.lang.String
    Datejava.sql.Date
    Timejava.sql.Time
    Timestampjava.sql.Timestamp
    ResultSetjava.sql.ResultSet