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 WebServicesSummit.com,
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
