The Java External Environment

The database server includes support for Java stored procedures and functions. A Java stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Java and the execution of the procedure or function takes place outside the database server (that is, within a Java VM environment).

It should be noted that there is one instance of the Java VM for each database rather than one instance per connection. Java stored procedures can return result sets.

There are a few prerequisites to using Java in the database support:

A copy of the Java Runtime Environment must be installed on the database server computer.
The database server must be able to locate the Java executable (the Java VM).

To use Java in the database, make sure that the database server is able to locate and start the Java executable. Verify that this can be done by executing:

START EXTERNAL ENVIRONMENT JAVA;

If the database server fails to start Java then the problem probably occurs because the database server is not able to locate the Java executable. In this case, you should execute an ALTER EXTERNAL ENVIRONMENT statement to explicitly set the location of the Java executable. Make sure to include the executable file name.

ALTER EXTERNAL ENVIRONMENT JAVA
  LOCATION 'java-path';

For example:

ALTER EXTERNAL ENVIRONMENT JAVA
  LOCATION 'c:\\jdk1.6.0\\jre\\bin\\java.exe';

You can query the location of the Java VM that the database server will use by executing the following SQL query:

SELECT db_property('JAVAVM');

Note that the START EXTERNAL ENVIRONMENT JAVA statement is not necessary other than to verify that the database server can start the Java VM. In general, making a Java stored procedure or function call starts the Java VM automatically.

Similarly, the STOP EXTERNAL ENVIRONMENT JAVA statement is not necessary to stop an instance of Java since the instance automatically goes away when the all connections to the database have terminated. However, if you are completely done with Java and you want to make it possible to free up some resources, then the STOP EXTERNAL ENVIRONMENT JAVA statement decrements the usage count for the Java VM.

Once you have verified that the database server can start the Java VM executable, the next thing to do is to install the necessary Java class code into the database. Do this by using the INSTALL JAVA statement. For example, you can execute the following statement to install a Java class from a file into the database.

INSTALL JAVA 
NEW 
FROM FILE 'java-class-file';

You can also install a Java JAR file into the database.

INSTALL JAVA 
NEW
JAR 'jar-name'
FROM FILE 'jar-file';

Java classes can be installed from a variable, as follows:

CREATE VARIABLE JavaClass LONG VARCHAR;
SET JavaClass = xp_read_file('java-class-file')
INSTALL JAVA 
NEW
FROM JavaClass;

To remove a Java class from the database, use the REMOVE JAVA statement, as follows:

REMOVE JAVA CLASS java-class

To remove a Java JAR from the database, use the REMOVE JAVA statement, as follows:

REMOVE JAVA JAR 'jar-name'

To modify existing Java classes, you can use the UPDATE clause of the INSTALL JAVA statement, as follows:

INSTALL JAVA 
UPDATE
FROM FILE 'java-class-file'

You can also update existing Java JAR files in the database.

INSTALL JAVA 
UPDATE
JAR 'jar-name'
FROM FILE 'jar-file';

Java classes can be updated from a variable, as follows:

CREATE VARIABLE JavaClass LONG VARCHAR;
SET JavaClass = xp_read_file('java-class-file')
INSTALL JAVA 
UPDATE
FROM JavaClass;

Once the Java class is installed in the database, you can then create stored procedures and functions to interface to the Java methods. The EXTERNAL NAME string contains the information needed to call the Java method and to return OUT parameters and return values. The LANGUAGE attribute of the EXTERNAL NAME clause must specify JAVA. The format of the EXTERNAL NAME clause is:

EXTERNAL NAME 'java-call' LANGUAGE JAVA

java-call :

[package-name.]class-name.method-name method-signature

method-signature :

( [ field-descriptor, ... ] ) return-descriptor

field-descriptor and return-descriptor :

Z
| B
| S
| I
| J
| F
| D
| C
| V
| [descriptor
| Lclass-name;

A Java method signature is a compact character representation of the types of the parameters and the type of the return value. If the number of parameters is less than the number indicated in the method-signature, then the difference must equal the number specified in DYNAMIC RESULT SETS, and each parameter in the method signature that is more than those in the procedure parameter list must have a method signature of [Ljava/SQL/ResultSet;.

For Java UDFs, you do not need to set DYNAMIC RESULT SETS; DYNAMIC RESULT SETS equal to 1 is implied.

The field-descriptor and return-descriptor have the following meanings:

Field type	Java data type
B	byte
C	char
D	double
F	float
I	int
J	long
L class-name;	an instance of the class class-name. The class name must be fully qualified, and any dot in the name must be replaced by a /. For example, java/lang/String
S	short
V	void
Z	Boolean
[	use one for each dimension of an array

For example,

double some_method(
  boolean a,
  int b,
  java.math.BigDecimal c,
  byte [][] d,
  java.sql.ResultSet[] rs ) {
}

would have the following signature:

'(ZILjava/math/BigDecimal;[[B[Ljava/SQL/ResultSet;)D'

The following procedure creates an interface to a Java method. The Java method does not return any value (V).

CREATE PROCEDURE insertfix() 
EXTERNAL NAME 'JDBCExample.InsertFixed()V' 
LANGUAGE JAVA;

The following procedure creates an interface to a Java method that has a String ([Ljava/lang/String;) input argument. The Java method does not return any value (V).

CREATE PROCEDURE InvoiceMain( IN arg1 CHAR(50) )
EXTERNAL NAME 'Invoice.main([Ljava/lang/String;)V'
LANGUAGE JAVA;

The following procedure creates an interface to a Java method Invoice.init which takes a string argument (Ljava/lang/String;), a double (D), another string argument (Ljava/lang/String;), and another double (D), and returns no value (V).

 CREATE PROCEDURE init( IN arg1 CHAR(50),
                       IN arg2 DOUBLE, 
                       IN arg3 CHAR(50), 
                       IN arg4 DOUBLE) 
EXTERNAL NAME 'Invoice.init(Ljava/lang/String;DLjava/lang/String;D)V'
LANGUAGE JAVA

The following Java example contains the function main which takes a string argument and writes it to the database server messages window. It also contains the function whare that returns a Java String.

import java.io.*;

public class Hello
{
    public static void main( String[] args )
    {
        System.out.print( "Hello" );
               for ( int i = 0; i  < args.length; i++ )
                   System.out.print( " " + args[i] );
        System.out.println();
    }
    public static String whare()
    {
        return( "I am SQL Anywhere." );
    }
}

The Java code above is placed in the file Hello.java and compiled using the Java compiler. The class file that results is loaded into the database as follows.

INSTALL JAVA 
NEW 
FROM FILE 'Hello.class';

Using Interactive SQL, the stored procedure that will interface to the method main in the class Hello is created as follows:

CREATE PROCEDURE HelloDemo( IN name LONG VARCHAR ) 
EXTERNAL NAME 'Hello.main([Ljava/lang/String;)V'
LANGUAGE JAVA;

Note that the argument to main is described as an array of java.lang.String. Using Interactive SQL, test the interface by executing the following SQL statement.

CALL HelloDemo('SQL Anywhere');

If you check the database server messages window, you will find the message written there. All output to System.out is redirected to the server messages window.

Using Interactive SQL, the function that will interface to the method whare in the class Hello is created as follows:

CREATE FUNCTION Whare()
RETURNS LONG VARCHAR
EXTERNAL NAME 'Hello.whoAreYou(V)Ljava/lang/String;'
LANGUAGE JAVA;

Note that the function whare is described as returning a java.lang.String. Using Interactive SQL, test the interface by executing the following SQL statement.

SELECT Whare();

You should see the response in the Interactive SQL Results window.

In attempting to troubleshoot why a Java external environment did not start, that is, if the application gets a "main thread not found" error when a Java call is made, the DBA should check the following:

If the Java VM is a different bitness than the database server, then ensure that the client libraries with the same bitness as the VM are installed on the database server machine.
Ensure that the sajdbc.jar and dbjdbc12/libdbjdbc12 shared objects are from the same software build.
If more than one sajdbc.jar are on the database server machine, make sure they are all synchronized to the same software version.
If the database server machine is very busy, then there is a chance the error is being reported due to a timeout.