Sponsor Links
Fast, reliable data access for ODBC, JDBC, ADO.NET and XML
Understanding Java-Enabled Databases and Adaptive Servers
by Ken North
When I started collecting a paycheck as a programmer, my first assignment was to develop a COBOL compiler that included extensions to handle databases. The target market was IBM mainframe sites, so my employer decided to cancel the project when we learned that IBM was adding database extensions to its COBOL. Our decision may have been premature. Chris Date was working on the IBM project and he told me IBM put its project on the back burner for years. Other companies eventually released products that embedded database calls in compiled languages. Today, we have come full circle. Instead of database-enabled languages, vendors are introducing language-enabled databases. The evolution of languages has produced Java, and innovative companies such as Sybase have given us Java-enabled databases. In this article, we'll explore how to develop Java classes to install in a Sybase Adaptive Server Anywhere (ASA) database.
Figure 1 illustrates a scenario where a Java-enabled browser connects to a web server that has a database connection to Sybase Adaptive Server. The web server stores Java classes that it downloads when the user browses to a page having a Java applet. The database stores Java classes that Adaptive Server executes when a user runs an SQL query that references a Java object. It isn't necessary for a browser to embed the Java Virtual Machine (VM) to process HTML pages. Likewise, it isn't necessary for a database server to embed the Java VM in order to process SQL requests. Embedding the Java VM, however, provides more versatility to both browser and SQL server. Most users choose Java-enabled browsers; likewise database companies are betting there will be a similar demand for Java-enabled databases. In the March 1998 issue of Web Techniques, the Database Developer column discussed object-relational DBMS (ORDBMS) and server architectures that support Java and other extensions. Several of the major SQL DBMS vendors have upgraded their server technology to provide Java-enabled databases. Sybase is one of the companies that offers a Java-enabled server.
Extensible database servers have been available for several years, but Java in the database arrived with great promise. Performance improvements, memory management and adoption by SQL database vendors have caused Java to become a favored tool for creating database extensions. Programmers have often used C/C++ when developing dynamic link libraries (DLLs) to extend servers such as Raima Velocis, Sybase Adaptive Server, IBM Informix Dynamic Server, and Microsoft SQL Server. Database servers are often doing mission-critical processing, so installing a faulty extension can be an invitation to a server crash. Programmers writing C/C++ DLLs must exercise care to avoid memory leaks, runaway pointers and buffer overruns -- problems that Java was designed to eliminate.
Java is versatile enough to be suitable for programming all tiers of a multi-tier application. This solves the problem of using different languages for programming clients, servers, components, and stored procedures. Java adds a measure of security because it compiles to byte codes that the Java VM checks before execution. Java classes also provide abstract data types (user-defined types) and type inheritance.
Java-enabled servers include a version of the Java VM, system-defined classes and some mechanism for developers to register or install user-defined classes. After installation, the user-defined classes are a part of the database and accessible to clients connected to that database. Figure 2 illustrates a Sybase Central view of the classes in a Java-enabled database. At the top of the list in figure 2 are two user-defined classes discussed later in this article. The remainder of the list includes system-supplied classes.
Who Can Use Java-enabled Databases?
A Java-enabled database does not serve only Java clients, or Java-enabled programs such as Netscape Communicator and Microsoft Internet Explorer. Applications that use SQL, such as Cold Fusion and Visual Basic programs, can invoke Java User-Defined Functions (UDFs) and stored procedures, and access Java columns in tables. There are some added benefits, however, in cases in which the database client is a Java program. For example, Sybase upgraded its Tabular Data Stream (TDS) protocol to operate over HTTP and support object streaming. Sybase Adaptive Server Anywhere supports TDS, and it can exchange serialized Java objects with clients.
Both Sybase Adaptive Servers -- Adaptive Server Anywhere and Adaptive Server Enterprise (ASE) -- include Sybase's implementation of JDBC and a Java Virtual Machine (VM) optimized for execution in a database server. The first version to support Java-enabled databases was Adaptive Server Anywhere 6.0 (ASA6). Adaptive Server Enterprise was next, with ASA and ASE using the same Java VM and Sybase runtime classes. To test the Java examples for this article, I used Adaptive Server Anywhere. ASA is included in the SQL Anywhere Studio product.
Sybase Adaptive Server provides the option of creating databases without Java, but the default is to include Java. Creating a Java-enabled database installs the Sybase version of the Java runtime environment, which includes core classes supporting types, and JDBC classes that provide SQL access to databases. A Java-enabled database includes several Java packages:
Sun packages (sun.*)
Primitive Java types (java.lang.integer)
Utility package (java.util.*)
JDBC (java.sql.*)
Server-side JDBC (com.sybase)
Sybase classes (Sybase.sql)
The Java runtime expands the size of a database by approximately 8 megabytes. Adding your own classes will increase the database size. Because Java-enabled databases are larger, you need to remember to adjust the size of your server cache. Adaptive Server lets you adjust cache size, Java namespace size, and Java heap size.
Sybase's JDBC driver, jConnect, supports client connections using the TDS protocol. Classes in the database can use JDBC methods, but they don't require you to install jConnect because they use an internal JDBC driver. But, if you develop server-side classes that use JDBC MetaData methods, you can use jConnect's catalog script (jcatalog.sql) to optimize catalog queries.
Adaptive Server gives you the option of creating databases without Java, although the default is to create a Java-enabled database. The CREATE DATABASE statement includes a JAVA OFF clause to omit the Java runtime classes:
CREATE DATABASE 'c:\\sybase\\asa6\\temp2' JAVA OFF
You can also use Sybase Central to create and upgrade databases. Sybase provides commands to install new or updated Java classes in the database, and remove Java classes. Developers can create user-defined classes with the Java Development Kit (JDK), or Java tool suites such as Borland JBuilder. When developing server-side classes, you should compile and thoroughly debug them outside the database, then install them in the database, because creating test programs and testing outside the server is easier. After you debug your class, you add it to the database with the INSTALL command. This SQL statement installs one of this article's example classes:
INSTALL JAVA NEW FROM FILE 'd:\Examples\Java\PhoneticString.class'
To remove Java classes from a database, use the REMOVE command, as shown in this example:
REMOVE JAVA CLASS PhoneticString
You can use a variable whose type is based on a Java class. For the example in listing 1, assume the Java class is PizzaOrders. To create a variable of type PizzaOrders, use the CREATE VARIABLE statement shown in line 1. Create an instance of the class, or instantiate the object, with the second statement. Having created the variable, and made the Java object visible to Adaptive Server, you can now use its methods and fields. For example, to pass a value to a field in the PizzaOrders class, use a statement such as line 3 of listing 1. SQL statements can invoke instance methods of Java classes. If there were a discount for Pizza Club members, you might use a method to check a membership card number and return the discount. Assuming the PizzaOrders class contained an isDiscounted method, you could use the SELECT statement on line 4.
Listing 1
Using Java objects in Adaptive Server SQL statements
CREATE VARIABLE jvar PizzaOrders;
SET jvar = NEW PizzaOrders();
SET jvar>>typeCrust = 'Thin';
SELECT jvar>>isDiscounted('C9999','Carey', 'Drew');
You can also save Java objects as values in tables, and retrieve them using SELECT statements. The example in listing 2 shows how to store an object, when JCol is of type MyClass, a Java class:
Listing 2
Creating a table and inserting a Java object into it
CREATE TABLE myTab (
ID INT,
JCol MyClass);
INSERT INTO myTab (ID, JCol)
VALUES (1, Jvar);
CLASSPATH, main, and Stored Procedures
Adaptive Server's Java VM does not use the CLASSPATH environment variable to locate classes because the INSTALL SQL statement points to class locations. The main method, the starting point of Java applications, takes on a different meaning for classes in the database. Adaptive Server's VM enables you to execute a main method by using the SQL convention for calling a stored procedure, the CALL statement. If, for example, you had installed an AddressBook class, you can use it in a stored procedure call like this:
CALL AddressBook.main()
When developing Java classes for an Adaptive Server database, you might create programs that access data with JDBC, or programs that add other functionality without using JDBC methods and SQL. The examples for this article illustrate both types of classes. You can test these example statements and classes with the asademo database included with Adaptive Server Anywhere. I used an ALTER TABLE statement to add a CHAR(4) column named fon_name to the contact table of that database. The purpose of the new column is to store a phonetically-encoded value for the last_name column.
My example code is Java that installs in an ASA database. NameSrch, an example from chapter 5 of Database Magic with Ken North (Prentice-Hall), is a class that uses JDBC and SQL. NameSrch performs a phonetic search using the SQL SOUNDEX function. Another phonetic search example is found in chapter 4 of Database Magic with Ken North. PhoneticString is a Java class that implements a modified version of the Phillips Metaphone algorithm for phonetic encoding. PhoneticString uses no SQL statements or JDBC methods.
The original Russell Soundex algorithm encodes strings, such as names, to a four character code a letter and three digits. Not all SQL database managers provide a SOUNDEX function, and SQL dialects with SOUNDEX don't all follow the Russell Soundex algorithm. For example, SQL Server encodes Smith to a value of S530, but Adaptive Server Anywhere encodes Smith to 3827. Metaphone produces a more intuitive encoding than SOUNDEX. It encodes, for example, the name Houseman as 'HSMN'. There isn't space here to provide a full explanation of the encoding process, but chapter 4 of Database Magic with Ken North explains Phillips' original algorithm and my modifications.
The NameSrch example accepts a "sounds-like" string as an argument and uses SOUNDEX to return a list of employees whose surname sounds like the search argument. If you examine listing 3, you'll see NameSrch resembles other JDBC programs. It connects to the database, instantiates a statement and result set, and executes an SQL query. The primary clue that this is not an ordinary JDBC program is the null connection string, which you use to connect to the server-side JDBC driver. The similarity to JDBC code for other platforms is one reason Java and JDBC are likely to become a dominant standard for stored procedures.
After you've compiled PhoneticString to a Java class, you can use the sequence of commands in listing 5 to install PhoneticString in your database. After you start the asademo database, enter the commands in the ISQL Command Window. Figure 3 shows the execution of a script in ASA's ISQL program.
Listing 5
Adaptive Server ISQL Script for Testing the PhoneticString Class
INSTALL JAVA UPDATE FROM FILE 'D:\\Examples\\Java\\PhoneticString.class';
create variable jvar PhoneticString;
SET jvar = NEW PhoneticString();
create variable mfon char(4);
SET mfon = jvar>>Metafon4('BROWN');
SELECT emp_lname FROM employee WHERE (jvar>>Metafon4(emp_lname) = mfon);
To use the script on your machine, you'll need to use the same directories or adjust the path in the INSTALL statement. Using listing 5 as a reference, you can put together a test script for NameSrch. After you install Java classes in the database, you can use Java objects and methods in SQL statements such as SELECT, INSERT, and UPDATE. To use Java objects in scripts, you must create an instance of the class as discussed earlier. Refer to listing 5 for examples of instantiating a PhoneticString object.
After you have a variable to refer to an instance of PhoneticString, you can use it to UPDATE, INSERT, or SELECT data. The following example updates all fon_name columns of asademo's contact table:
UPDATE contact SET fon_name = jvar>>Metafon4(last_name);
The fields of a Java object are of a type that corresponds to an SQL type, therefore, you can update fields of a Java object. To update a Java field, you cannot use a standard UPDATE statement -- you must use an EVALUATE function in the UPDATE statement and provide methods that update field values.
The following INSERT statement adds a row to the contact table, using the Metafon4 method to encode the fon_name column:
INSERT INTO contact (id, last_name, first_name, title, street, city, state, zip, phone, fax, fon_name)
VALUES (61, 'Houseman', 'Horst', 'cs', '1230 Elm Ave.', 'Carlsbad', 'CA', '92008',
'7604383181','7604383182', jvar>>Metafon4('Houseman'));
You can include Java columns in SELECT statements, as shown in listing 5.
A SELECT statement can invoke instance method or class methods, retrieve Java objects from tables, and use Java fields in WHERE clauses.
User-Defined Functions (UDFs)
Adaptive Server supports user-defined functions (UDFs) and you can use Java objects in UDFs. Listing 6 is METANAME.SQL, a UDF I created to make it easy to use the PhoneticString class. After creating the metaname UDF, I was able to write queries such as this:
SELECT emp_lname FROM employee WHERE (metaname(emp_lname) = metaname('BROWN'));
Listing 6
Create a User-Defined Function that uses Java methods
INSTALL JAVA UPDATE FROM FILE 'D:\\Examples\\Java\\PhoneticString.class';
CREATE FUNCTION metaname (emp_lname CHAR(20))
RETURNS CHAR(4)
BEGIN
DECLARE mfon4 CHAR(4);
create variable jvar PhoneticString;
SET jvar = NEW PhoneticString();
SET mfon4 = jvar>>Metafon4(emp_lname);
RETURN ( mfon4 );
END
Java permits Sybase to embody its Adaptive Server product line with object-relational properties. Java is an enabling technology that makes ASA a platform that is flexible, adaptable, and extensible. If SQL procedures or UDFs aren't sufficiently powerful, turn to Java. If you need new types, add Java classes. My impression, after using ASA and testing its ability to support Java-enabled databases, is that Adaptive Server Anywhere gives Sybase one of today's most versatile mobile and embeddable database managers.
Ken wrote the Database Developer column for Dr. Dobb's Sourcebook and Web Techniques (where an earlier version of this article was published). Ken is the author of Windows Multi-DBMS Programming (John Wiley & Sons) and Database Magic with Ken North (Prentice-Hall). He develops software, speaks at conferences, and teaches seminars. He is also the editor of , GridSummit.com and SQLSummit.com.
/***************************************************************************
* FILE NAME: NameSrch.java TITLE: Perform phonetic search
*
* AUTHOR: K. E. North II, Ken North Computing
*
* from Database Magic with Ken North (Prentice-Hall PTR, 1998)
****************************************************************************
*
* Copyright (c) Kendall E. North II, 1998. All rights reserved. Reproduction
* or translation of this work beyond that permitted in Section 117 of the
* United States Copyright Act without express written permission of the
* copyright owner is unlawful. The purchaser may make backup copies for
* his/her own use only and not for distribution or resale. The Author and
* Publisher assumes no responsibility errors, omissions or damages caused
* by the use of these programs or from use of the information contained
* herein.
*
****************************************************************************
* Synopsis:
*
* Demonstrate server-side Java using JDBC. Use Sybase runtime environment.
* Install this class in asademo, the Adaptive Server sample database.
*
* This class executes SOUNDEX search of the employee table in the
* Adaptive Server Anywhere sample database.
***************************************************************************/
// import JDBC classes
import java.sql.*;
public class NameSrch {
// data members
public String sqlQry;
private Connection conn;
private Statement stmt;
private ResultSet rs;
private boolean isMore;
public NameSrch(String lname ) throws SQLException {
// use internal connection with server-side JDBC driver
conn = DriverManager.getConnection(" ");
stmt= conn.createStatement();
sqlQry = "select emp_lname, emp_fname ";
sqlQry = sqlQry + "from employee ";
sqlQry = sqlQry + "where SOUNDEX(emp_lname) = ";
sqlQry = sqlQry + "SOUNDEX(\'" + lname + "'\')";
// echo class and query to Adaptive Server status window
String msg1 = "Class NameSrch: ";
msg1 = msg1 + sqlQry;
System.out.println (msg1);
// execute the query
rs = stmt.executeQuery(sqlQry);
// position the cursor at the first row of the ResultSet
isMore = rs.next();
}
public String getName() throws SQLException {
String fName = "";
String lName = "";
String eName = "";
if ( isMore )
{
lName = rs.getString(1);
fName = rs.getString(2);
eName = lName + ", " + fName;
}
isMore = rs.next();
if ( eName == "" )
{
return null;
}
else
{
return eName;
}
}
public boolean setName( String lname )
throws SQLException {
sqlQry = "select emp_lname, emp_fname ";
sqlQry = sqlQry + "from employee ";
sqlQry = sqlQry + "where SOUNDEX(emp_lname) = ";
sqlQry = sqlQry + "SOUNDEX(\'" + lname + "\')";
// execute the query
rs = stmt.executeQuery( sqlQry );
isMore = rs.next();
return isMore;
}
public boolean more()
{
return isMore;
}
}
© 1998, Ken North
Ken North Home Page _SQL Summit_ _Articles
