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.
START EXTERNAL ENVIRONMENT JAVA;
ALTER EXTERNAL ENVIRONMENT JAVA LOCATION 'java-path';
ALTER EXTERNAL ENVIRONMENT JAVA LOCATION 'c:\\jdk1.6.0\\jre\\bin\\java.exe';
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.
INSTALL JAVA NEW FROM FILE 'java-class-file';
INSTALL JAVA NEW JAR 'jar-name' FROM FILE 'jar-file';
CREATE VARIABLE JavaClass LONG VARCHAR; SET JavaClass = xp_read_file('java-class-file') INSTALL JAVA NEW FROM JavaClass;
REMOVE JAVA CLASS java-class
REMOVE JAVA JAR 'jar-name'
INSTALL JAVA UPDATE FROM FILE 'java-class-file'
INSTALL JAVA UPDATE JAR 'jar-name' FROM FILE 'jar-file';
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
[package-name.]class-name.method-name method-signature
( [ field-descriptor, ... ] ) return-descriptor
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 |
double some_method( boolean a, int b, java.math.BigDecimal c, byte [][] d, java.sql.ResultSet[] rs ) { }
'(ZILjava/math/BigDecimal;[[B[Ljava/SQL/ResultSet;)D'
CREATE PROCEDURE insertfix() EXTERNAL NAME 'JDBCExample.InsertFixed()V' LANGUAGE JAVA;
CREATE PROCEDURE InvoiceMain( IN arg1 CHAR(50) ) EXTERNAL NAME 'Invoice.main([Ljava/lang/String;)V' LANGUAGE JAVA;
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
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." ); } }
INSTALL JAVA NEW FROM FILE 'Hello.class';
CREATE PROCEDURE HelloDemo( IN name LONG VARCHAR ) EXTERNAL NAME 'Hello.main([Ljava/lang/String;)V' LANGUAGE JAVA;
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.
CREATE FUNCTION Whare() RETURNS LONG VARCHAR EXTERNAL NAME 'Hello.whoAreYou(V)Ljava/lang/String;' LANGUAGE JAVA;
SELECT Whare();
You should see the response in the Interactive SQL Results window.