SQL Anywhere 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:

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;

Java JAR files can be installed from a variable, as follows:

CREATE VARIABLE JavaJar LONG VARCHAR;
SET JavaJar = xp_read_file('jar-file')
INSTALL JAVA 
NEW
JAR 'jar-name'
FROM JavaJar;

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;

Java JAR files can be updated from a variable, as follows:

CREATE VARIABLE JavaJar LONG VARCHAR;
SET JavaJar = xp_read_file('jar-file')
INSTALL JAVA 
UPDATE
FROM JavaJar;

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;.

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

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 whoAreYou 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 whoAreYou()
    {
        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 whoAreYou in the class Hello is created as follows:

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

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

SELECT WhoAreYou();

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

In attempting to trouble shoot 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:

 See also