1.10.1 External Java routines

This section describes the following topics relating to external Java routines:

In addition, note that in HP-UX, Solaris, and AIX, external Java routines cannot be used if you have not set up the POSIX library version of HiRDB (using the pdsetup command), or if you have set up a non-POSIX library version of HiRDB over a POSIX library version of HiRDB. For details about the pdsetup command, see the manual HiRDB Version 9 Command Reference.

Organization of this subsection
(1) External Java routine names
(2) Type mapping
(3) SQL executability using a non-POSIX library version of HiRDB(UNIX edition only)

(1) External Java routine names

Since external routines consist of external Java procedures and external Java functions, external Java routine names are specified using CREATE PROCEDURE or CREATE FUNCTION.

(a) Format

external-Java-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]...

(b) 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.
When defining an external Java procedure, either make the return value type name void (no return value) or omit returns type-name.
When defining an external Java function, returns type-name is required, unless the Java signature is omitted, in which case there is no need to specify returns type-name. For an external Java function, you cannot specify void (no return value) as the return value 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 about Java data types that can be mapped to HiRDB data types, see 1.10.1(2) Type mapping.
(c) Rules
  1. If the Java signature is omitted, the Java data type is determined based on the HiRDB data type specified in the external Java procedure or function and the input/output mode of the SQL parameters, 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.10.1(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 lower-case alphabetic characters
      Numeric characters, underscores (_), dollar signs ($), periods (.), and hyphens (-)
    • Class identifiers, method identifiers, and type names
      Upper- and lower-case alphabetic characters
      Numeric characters, underscores (_), and dollar signs ($)
    • Package names
      Upper- and lower-case alphabetic characters
      Numeric characters, underscores (_), dollar signs ($), and periods (.)
  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

(2) Type mapping

This section explains the mapping between the data types that are recognized in the Java language and the data types that are recognized by HiRDB.

The following table indicates the implicit mapping that occurs when a Java signature specifying an external routine is omitted.

Table 1-27 Implicit mapping that occurs when a Java signature specifying an external routine is omitted

HiRDB data typeJava data type (null value allowed)
INT [EGER]java.lang.Integer
SMALLINTjava.lang.Short
[LARGE] DEC [IMAL]java.math.BigDecimal
FLOAT, DOUBLE PRECISIONjava.lang.Double
SMALLFLT, REALjava.lang.Float
CHAR [ACTER]java.lang.String
VARCHAR(n), CHAR [ACTER] VARYING
NCHAR, NATIONAL CHAR [ACTER]
NVARCHAR, NATIONAL CHAR [ACTER], NCHAR VARYING
MCHAR
MVARCHAR
DATEjava.sql.Date
TIMEjava.sql.Time
TIMESTAMPjava.sql.Timestamp
INTERVAL YEAR TO DAYjava.math.BigDecimal
INTERVAL HOUR TO SECOND
BLOB, BINARY LARGE OBJECTbyte[]
BINARY

The following table indicates the mapping between HiRDB data types and Java data types specifiable in a Java signature of an external routine specification.

Table 1-28 Mapping between HiRDB data types and Java data types specifiable in a Java signature of an external routine specification.

HiRDB data typeJava data type (null value allowed)Java data type (null value not allowed)#1
INT [EGER]java.lang.Integerint
SMALLINTjava.lang.Shortshort
[LARGE] DEC [IMAL]java.math.BigDecimal--
FLOAT, or DOUBLE PRECISIONjava.lang.Doubledouble
SMALLFLT, or REALjava.lang.Floatfloat
CHAR [ACTER]java.lang.String, or byte[]#2--#3
VARCHAR(n), or CHAR [ACTER] VARYING
NCHAR, or NATIONAL CHAR [ACTER]
NVARCHAR, NATIONAL CHAR [ACTER], or NCHAR VARYING
MCHAR
MVARCHAR
DATEjava.sql.Date--
TIMEjava.sql.Time
TIMESTAMPjava.sql.Timestamp
INTERVAL YEAR TO DAYjava.math.BigDecimal
INTERVAL HOUR TO SECOND
BLOB, or BINARY LARGE OBJECTbyte[]
BINARY
Abstract data type--
--: No corresponding data type exists in the Java language.
#1: Assigning the null value may cause an error at runtime.
#2: Either Java.lang.String or byte[] can be specified as the Java data type corresponding to the HiRDB character string data type. Specifying byte[] suppresses character code conversion.
#3: Passing and receiving Japanese data through a String class (or a class inheriting that class) between HiRDB and an external Java routine is performed according to the rules regarding the mapping of Java character codes (mapping between a given character code and Unicode). This means that some gaiji codes might not convert correctly.

(3) SQL executability using a non-POSIX library version of HiRDB(UNIX edition only)

The table below indicates the executability of SQL statements in non-POSIX library versions of HiRDB. For the HP-UX, Solaris, and AIX editions, the non-POSIX library version of HiRDB refers to a HiRDB in which the load module in the POSIX library version has not been set up with the pdsetup command.

Table 1-29 Executability of SQL statements in non-POSIX library versions of HiRDB

ClassificationSQL statementExecutable?Condition in which SQL statement cannot be executed
Definition SQLALTER PROCEDURECSpecification of an external Java procedure
ALTER ROUTINECSpecification of an external Java procedure or function
CREATE FUNCTIONCUse of an external Java routine
CREATE PROCEDURECUse of an external Java routine
CREATE TYPECUse of an external Java routine
CREATE VIEWCSpecification of an external Java function
DROP FUNCTIONYN/A
DROP PROCEDUREYN/A
DROP SCHEMAYN/A
Data manipulation SQLASSIGN LIST statementYN/A
CALL statementC
  • External Java procedure
  • Procedure that uses an external Java routine
DELETE statementYN/A
DROP LIST statementYN/A
FREE LOCATOR statementYN/A
INSERT statementCUse of an external Java function
PURGE TABLE statementYN/A
Single-row SELECT statementCUse of external Java function
Dynamic SELECT statementCUse of an external Java function
UPDATE statementCUse of an external Java function
Assignment statementCUse of an external Java function
Control SQLLOCK statementYN/A
Embedded SQL languageINSTALL JARNN/A
REPLACE JARNN/A
REMOVE JARYN/A
INSTALL CLIBYN/A
REPLACE CLIBYN/A
REMOVE CLIBYN/A
OtherFunction callC
  • Calling an external Java function
  • Function that uses an external Java function
Y: Can be executed.
C: Might result in an error depending on the condition.
N: Cannot be executed.
N/A: Not applicable