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 format	Type name containing a package name, as interpreted by HiRDB
   Integer	java.lang.Integer
   Short	java.lang.Short
   BigDecimal	java.math.BigDecimal
   Double	java.lang.Double
   Float	java.lang.Float
   String	java.lang.String
   Date	java.sql.Date
   Time	java.sql.Time
   Timestamp	java.sql.Timestamp
   ResultSet	java.sql.ResultSet

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 type	Java data type (null value allowed)
INT [EGER]	java.lang.Integer
SMALLINT	java.lang.Short
[LARGE] DEC [IMAL]	java.math.BigDecimal
FLOAT, DOUBLE PRECISION	java.lang.Double
SMALLFLT, REAL	java.lang.Float
CHAR [ACTER]	java.lang.String
VARCHAR(n), CHAR [ACTER] VARYING
NCHAR, NATIONAL CHAR [ACTER]
NVARCHAR, NATIONAL CHAR [ACTER], NCHAR VARYING
MCHAR
MVARCHAR
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
INTERVAL YEAR TO DAY	java.math.BigDecimal
INTERVAL HOUR TO SECOND
BLOB, BINARY LARGE OBJECT	byte[]
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 type	Java data type (null value allowed)	Java data type (null value not allowed)#1
INT [EGER]	java.lang.Integer	int
SMALLINT	java.lang.Short	short
[LARGE] DEC [IMAL]	java.math.BigDecimal	--
FLOAT, or DOUBLE PRECISION	java.lang.Double	double
SMALLFLT, or REAL	java.lang.Float	float
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
DATE	java.sql.Date	--
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
INTERVAL YEAR TO DAY	java.math.BigDecimal
INTERVAL HOUR TO SECOND
BLOB, or BINARY LARGE OBJECT	byte[]
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.

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

Classification	SQL statement	Executable?	Condition in which SQL statement cannot be executed
Definition SQL	ALTER PROCEDURE	C	Specification of an external Java procedure
	ALTER ROUTINE	C	Specification of an external Java procedure or function
	CREATE FUNCTION	C	Use of an external Java routine
	CREATE PROCEDURE	C	Use of an external Java routine
	CREATE TYPE	C	Use of an external Java routine
	CREATE VIEW	C	Specification of an external Java function
	DROP FUNCTION	Y	N/A
	DROP PROCEDURE	Y	N/A
	DROP SCHEMA	Y	N/A
Data manipulation SQL	ASSIGN LIST statement	Y	N/A
	CALL statement	C	External Java procedure Procedure that uses an external Java routine
	DELETE statement	Y	N/A
	DROP LIST statement	Y	N/A
	FREE LOCATOR statement	Y	N/A
	INSERT statement	C	Use of an external Java function
	PURGE TABLE statement	Y	N/A
	Single-row SELECT statement	C	Use of external Java function
	Dynamic SELECT statement	C	Use of an external Java function
	UPDATE statement	C	Use of an external Java function
	Assignment statement	C	Use of an external Java function
Control SQL	LOCK statement	Y	N/A
Embedded SQL language	INSTALL JAR	N	N/A
	REPLACE JAR	N	N/A
	REMOVE JAR	Y	N/A
	INSTALL CLIB	Y	N/A
	REPLACE CLIB	Y	N/A
	REMOVE CLIB	Y	N/A
Other	Function call	C	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