User defined Java functions

Java classes can be stored within ASE and be used as functions accessible from T-SQL. This document shows how to enable Java in ASE, create a sample class file, store it in the database and access it through a SQL statement.

Configuration of Java in ASE

Enable Java in ASE with sp_configure.

sp_configure "enable java",1

Reboot the server, it's a static setting.

Create a Java class

In this example a java class is created that retrieves the hostname of an (internet)server when the ip-address is supplied.

import  java.net.InetAddress;
import  java.net.UnknownHostException;

public class HostName
{
   public static String get(String ipaddress)
   {
       try
       {
           return  InetAddress.getByName(ipaddress).getHostName();
       }
       catch (UnknownHostException e)
       {
           return  null;
       }
   }
}

Compile the java file

javac HostName.java

Store the class in the database

Store the class file in a jar file. ASE can only accept jar files. When you try to store class files you will see this error:

Server Message: ASE1 - Msg 10728, Level 16, State 1:
The specified file or image column contains a Java class. Java classes cannot be directly installed. Enclose this class in a jar and re-try your command.

Create the jar file, it should be uncompressed.

jar cf0 HostName.jar HostName.class

Store the jar file in the database

installjava -f HostName.jar  -S<server> -U<username> -P<password> -D<database>

When the jar file is loaded into ASE each individual class is extracted from the jar file and stored under its own name.

Use the java class

Now you can use the java class:

select HostName.get("127.0.0.1")

Create a wrapper around the Java class

You can create a wrapper about the java class in order to make the function more compatible with T-SQL.

create function getHostName(ipaddress varchar(255))
returns varchar(255)
language java parameter style java external name "HostName.get(java.lang.String)"

Call the function like this

select getHostName("127.0.0.1")

Remove a java class from the database

remove java class HostName

Use jdk 1.2.2

You should compile the java source with the most old-fashioned java compiler around: version 1.2.2. When you use a more recent compiler you are able to store the jar file into the database, but at runtime you get this error:

Msg 10707, Level 16, State 1:
Server 'ASE1', Line 1:
Unhandled Java Exception: 
java.lang.ClassFormatError: HostName

Luckily you can still download this version from the archives at sun: http://java.sun.com/products/archive/j2se/1.2.2_017/index.html This issue raises some doubts if Sybase is still really committed to this functionality. Talk to your local customer support center to get more background info on this issue.