## JDBC DRIVER FOR THE HP 3000

### Shu-Feng Wei

Hewlett-Packard Company
19447 Pruneridge Avenue, MS 47UX
Cupertino, CA 95014
Phone: 408-447-6785
Fax: 408-447-7902

WHAT IS JDBC?

aJava Database Connectivity (JDBC) is the industry standard open interface API that facilitates the development of Java applications/applets/beans with a wide range of relational databases.

JDBC supports Java’s "Write Once, Run Anywhere" approach to eliminate cross-platform complexity. With the JDBC API, JDBC applications call the JDBC Driver Manager to bind JDBC driver. The JDBC driver then establishes a network connection with a database server, sends SQL statements, and processes the results.

HP JDBC driver is a thin, pure Java data access solution. It consists of a single standards-based JDBC driver, network protocol, and database server interface components for concurrent access IMAGE/SQL and ALLBASE/SQL databases. HP JDBC driver ensures scalable n-tier connectivity and easy access to enterprise data distributed across the Internet, Intranet and Extranet.

JDBC KEY FEATURES

- Full Access to Enterprise Data
The HP JDBC driver provides scalable and fast access from Java applications/applets/servlets to the distributed business information. Developer can use JDBC’s full metadata access feature and write a sophisticated application to communicate with the enterprise databases.>

- No Client Installation
A pure JDBC driver is automatically downloaded as part of the applet that makes the JDBC API calls. HP JDBC driver is a thin-client, pure Java implementation. Its fully downloadable nature minimizes installation and administration overhead.

- Flexible Deployment
The HP JDBC driver will provide a thin, pure Java proxy that enables n-tier deployment on any Java-enabled Web server platform. This thin Java proxy functions as an IP router, which routes JDBC calls to the database server. It does not perform heavy reprocessing of data or requests.

- Designed for the Java Environment
The JDBC API is now available as a standard part of the Java JDK Platform. Java applications can write database applications once and access data anywhere.

- Database Connection via URL
JDBC exploits the advantages of Internet-standard URLs to identify database connections. JDBC URLs allow different drivers to use different schemes for locating databases and encode all necessary connection information. A JDBC URL may refer to a logical host name that is dynamically translated to the actual name by a network naming system.

- Full JDBC specification support
The HP JDBC driver supports complete implementation of the JDBC API specification including interface, class, and SQL grammar.

JDBC DRIVER TYPES

JavaSoft classifies JDBC Drivers into four types. Since non-pure Java JDBC driver will require pre-installation of client-side components, these distinctions are important for implementing enterprise data access solution. Current drivers fit into one of four categories:

1. JDBC-ODBC Bridge
2. Native-API, Partly Java Driver
3. Network-Protocol, All-Java Driver
4. Native-Protocol, All-Java Driver

Each of these categories is described in the following sections.

JDBC-ODBC Bridge (Type 1)

Figure 1

The JDBC-ODBC Bridge was jointly developed by JavaSoft and Intersolv. The Bridge provides JDBC access via ODBC drivers by taking advantage of the widely use of ODBC for accessing relational databases. To connect to a database server, the client-side Java application makes a JDBC API call and the Bridge converts JDBC calls into ODBC calls. These ODBC calls are passed to the database-specific ODBC driver for processing. This implementation is shown in Figure 1.

This Bridge allows you to use existing ODBC driver to easily access different vendor databases from within Java. However, JDBC driver, the Bridge, ODBC driver manager, ODBC drivers, and database client libraries must be pre-loaded on each client machine. As a result, this type of database connectivity may involve considerable overhead and complexity.

The JDBC driver performance is dependent on the underlying ODBC driver. This kind of driver is most appropriate on a corporate network using ODBC as its data access standard.

Native-API, Partly Java Driver (Type II)

Figure 2

Type II driver requires a client library of relevant database vendors to convert JDBC calls into DBMS-specific client API, as shown in Figure 2. These vendors’ libraries need to be pre-installed on each client machine, since they are usually written in a combination of Java and C/C++ and are not transportable over HTTP protocol. The driver performance is dependent on the performance of the underlying DBMS’s specific client API. The Type II driver is most appropriate on a corporate network committed to a single database vendor strategy.

Network-Protocol, All-Java Driver (Type III)

Figure 3

The Type III architecture consists of:

- the JDBC client and server,
- server middleware, as shown in Figure 3.

The type III driver translates JDBC functions into a database-independent network protocol, which is then translated into database-specific API calls by a database server interface middleware. This server middleware is able to connect all its Java clients to many different databases.

The JDBC client is written in pure Java and executes on the client machine. It is a thin layer implementation, which sends SQL statements over the network to the JDBC server, receives the data back from the JDBC server, and manages the network connection.

The JDBC server manages multiple connections to the database, as well as exception and status events resulting from SQL execution. The result data is then packaged and transmitted back to JDBC clients.

The server middleware component connects to the database server using a DBMS-specific client library. The middleware server needs to be configured for the target database, including setting port numbers, database-specific environment variables, and parameters.

In general, this is the most flexible JDBC alternative and best suited for Internet/Intranet-based concurrent data access applications, where scalability and performance is a major concern. Some vendor’s Type III JDBC driver can handle database multiplexing management, logging, administration, load balancing, and catalog and query caches. It addresses most web database applications issues. The Type III JDBC driver is not bound inextricably to ODBC or database specific client libraries. It reinforces the "Write Once, Run Anywhere" Java value proposition. Type III driver facilitates the implementation of JDBC compliant applets, thereby reinforcing the "Zero Administration" Java value proposition.

A major disadvantage of Network-Protocol drivers is that the server components are proprietary. The performance depends on the underlying capabilities of the vendors’ database independent communication middleware. In addition, enterprise users fail to take advantage of capabilities provided by CORBA products and future upgrade paths.

Native-Protocol, All-Java Driver (Type IV)

Figure 4

The Type IV driver converts JDBC functions into the vendor’s specific network protocol used by its database directly, as shown in Figure 4. This allows a direct connection from the client machine to the database server and is a practical solution for Intranet access. Since many of these protocols are proprietary, these drivers are available only from database vendors. The Type IV driver supports the implementation of "Zero Administration" JDBC compliant applets.

The Type IV driver is limited typically to a single database type. It compromises the database independence feature of JDBC that the behavior of JDBC compliant applets is far less predictable across different database engines.

All ABOUT JAVA

JDBC is explicable once you understand a few basic Java concepts.

Java is implemented through a Virtual Machine (VM). It is the Runtime environment for Java applications, applets, and servlets.

The Java Virtual Machine (JVM) is written in C and Java. Java applications and applets are compiled into Java Bytecode during development and executed within the JVM. JVM serves as a Bytecode interpreter. Java Bytecode is a platform independent intermediary machine code format.

Java objects are built using Java classes. These classes are the compiled binary code of Java class interface definition and implementations.

Java applications, applets, and servlets are typically comprised of numerous other Java Objects working together through an Object messaging protocol.

Applets

Java Applets are small Java program fragments (classes) that are embedded in an HTML page. Applets are executed through any Java-enabled Web browser and do not require a separate Java interpreter. A Java applet is either downloaded from Web Server on the Internet/Intranet or loaded locally from the client’s hard disk drive.

Applications

Java applications are pre-installed on a local disk drive or on a network drive, similar to a typical C/C++ application. A Java Application is executed by invoking the Java interpreter from a command line within that machine’s JVM.

Servlets

Servlets are modules that run inside of Java-enabled Web servers. A servlet is responsible for taking HTML requests and applying the necessary business logic to update a company’s database. Servelts are to Web servers as applets are to Web browsers.

The Servlet API allows you to write Java servlets that will work with your existing Web server. Servlets are an effective substitute for CGI scripts. They provide a way to create dynamic documents that is both easier to write and faster to run. Many popular Web servers support Servlets API.

Interfaces

A Java interface is basically a class that has constant data with no implementation. It only defines method signatures (the name, type and parameters of a method).

Let’s look at a simple interface definition:

Public interface MyInterface

{

int MyMethod(int x);

}

This interface defines the signature for the method, MyMethod.

Here is how you associate an interface with a class:

Public class MyImplementation implements MyInterface

{

public int MyMethod(int x)

{

return addOne(x);

}

protected int addOne(int i)

{

return i + 1;

}

}

Thus, any object of type MyImplementation is guaranteed to have a MyMethod method. Application developer can use interfaces almost everywhere he can use classes. By using interfaces, the developer can hide his class implementation details from the user and only allow them access to a specific set of methods.

JDBC DRIVER COMPONENTS

The JDBC API itself is implemented as a group of Java classes. These classes present an abstraction layer that provides the user access to JDBC-enabled databases. The JDBC API now is a standard component of all Java Development Kit (JDK) version 1.1 and greater. However, JDBC connectivity can be added to JDK 1.0.2 by downloading the JDBC API package from the JavaSoft JDBC web site.

JDBC can be divided into four components:

- JDBC Driver Manger
- JDBC Driver Interfaces
- JDBC Exception Classes
- Vendor specific JDBC Driver.

The vendor’s JDBC Driver implements the Driver Interfaces, uses the Exception Classes, and registers itself with the Driver Manager.

The first three components are included as part of JavaSoft JDBC package. The fourth part, the JDBC Driver, is obtained separately from the driver vendors, such as HP.

Driver Manager

The JDBC Driver Manager is a static Java class that provides services to connect to JDBC drivers. The JDBC driver registers itself with the JDBC Driver Manager when it is loaded into the Java interpreter. When a JDBC program requests a database connection, it invokes the forName method on the java.sql.DriverManager class, which scans through its registered JDBC drivers list until it finds an appropriate driver. Once a JDBC driver is loaded, the Driver Manager drops out of the picture altogether, and the Java application or applet interfaces with the JDBC driver directly.

Driver Interfaces

The JDBC driver interfaces are used by the Java applications/applets to communicate with a JDBC driver. From here, a database connection can be made to perform work and a java.sqlConnection interface is returned to the Java applications/applet.

While the interfaces form the largest part of the JDBC API standard, the class (compiled object code) is intentionally very small. Therefore, JDBC drivers should be pre-registered with the system, enabling the Driver Manager to select a suitable JDBC driver by giving only a database URL. The Driver Manager must load the JDBC driver class to determine which driver can service the given URL. To minimize the searching time for an appropriate JDBC driver, each driver class should be as small as possible. Here is the list of the important JDBC interfaces.

- java.sql.Connection

This

is used for establishing a database connection with transaction operations such as commits and rollbacks.

- java.sql.Statement, java.sql.PrepareStatement, java.sql.CallableStatement

These classes

are used for executing SQL statements on a database connection. PreparedStatement inherits from Statement, and CallableStatement inherits from PrepareStatement. A Statement object is used to execute a simple SQL statement with no parameters; a PreparedStatement object is used to execute a precompiled SQL statement with or without IN parameters; and a CallableStatement object is used to execute a call to a stored procedure. The Statement interface provides basic methods for executing statements and retrieving results. The PreparedStatement interface adds methods for dealing with IN parameters; CallableStatement adds methods for dealing with OUT parameters.

- java.sql.ResultSet, java.sql.ResultSetMetaData

These classes

are used for processing results sets and dynamically accessing the layout of result sets. A ResultSet contains all of the rows, which satisfied the conditions in an SQL statement. By using a set of get methods, developer can access to the data in the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet.

- java.sql.DatabaseMetaData

This class

is used for dynamically discovering database information. It provides information about what is supported and how things are supported. It also supplies system catalog information such as tables, columns, indexes, procedures, and so on.

Since these are all interfaces, the Java applications/applets cannot create them directly. The Driver Manager creates the initial Connection object. Calling methods on the Connection object or objects obtained from the Connection object returns all the other interfaces.

Exceptions Classes

The Exception classes are Java classes that are used by both the JDBC driver interfaces and the JDBC drivers themselves. They include:

- SQLException
- SQLWarning
- DataTruncation

HP JDBC Driver

The HP JDBC driver is a Type III driver. It is a set of classes that implement the JDBC driver interfaces. The HP JDBC driver establishes the connection to the database. All communication with these classes is done through the JDBC driver interfaces. Typically, a Java application/applet needs to know how to load the HP JDBC Driver and what values to use to connect to a backend database.

The HP JDBC driver consists of a thin JDBC client, JDBC server, and IMAGE/SQL and ALLBASE database server interface components. The HP JDBC client is 100% pure Java and multi-thread implementation. HP JDBC driver supports:

- comprehensive metadata
- SQL data types
- stored procedure
- full transaction isolation levels
- NLS support

USING JDBC DRIVER

This section shows developers how to use the JDBC API and HP JDBC driver to connect to databases.

First, install the JDBC API package. JDBC API is part of standard release of JDK 1.1 or greater. If you are still developing for JDK version 1.0.2, you need to download the JDBC API package from the JavaSoft Web site.

Second, install the HP JDBC driver by decompressing the driver in the CLASSPATH environment variable, or setting it appropriately so that Java can find it later.

Now to code the JDBC application:

Import the java.sql.* package into the JDBC application. The JDBC base classes (JDBC API) contain the necessary elements for properly instantiating the HP JDBC driver. These APIs serve as the link between the developer and the low-level code in the HP JDBC driver. The JDBC API provides the developer with an easy interface for interacting with various databases, independent of the JDBC driver in use.

Loading JDBC Driver

The JDBC application loads the DriverManager class from a specific JDBC driver. In the case of the HP JDBC driver, the DriverManager class is known as com.hp.jdbc.HPSqlDriver. To load the HPSqlDriver, insert this line into JDBC application:

Class.forName("com.hp.jdbc.HPSqlDriver");

This method tries to load the com/hp/jdbc/HPSqlDriver from the local CLASSPATH. The act of loading a JDBC driver causes the JDBC driver to be registered with the Driver Manager.

Connecting to A Database

The next step is to connect to the database with a JDBC "URL". The driverManager.getConnection method in the JDBC API uses this URL when attempting to start a connection.

The Database URL

The URL is an extended format of a web URL broadly defined as follow:

jdbc:<subprotocol>:<subname>

where jdbc is the standard base, subprotocol is the particular driver type, and subname is an additional specification identifying the database. Each URL is specific to the database vendor. In the case of the HP JDBC driver, the format of the URL is:

jdbc:hpjdbc://hostname:portnumber/databasename

Making the Connection

Once the developer has loaded the JDBC driver, making a database connection with a URL is quite simple. The following is an example of how to create a Connection object from the DriverManager.getConnection method. This method returns a Connection object that is to be assigned to an instantiated Connection class:

String url="jdbc:hpjdbc://hostname:portnumber/databasename";

String uid="";

String pwd="";

Connection con;

try

{

Con = DriverManager.getConnection(url, uid, pwd);

} catch(SQLException e)

{

// Take action on error

}

Process SQL Statements

In the final step, issue an SQL statement to the database and process the returned data. To do this, use the Statement and ResultSet classes. The Statement class contains a method, executeQuery, that takes an SQL statement to be processed and returns a ResultSet class for processing. The JDBC driver may generate an exception, which must be trapped with a simple "try and catch" operation.

Try

{

Statement sqlStmt = Con.createStatement();

ResultSet sqlReslt = sqlStmt.executeQuery("SELECT * FROM SYSTEM.TABLE");

}

catch(SQLException e)

{

// Take action on error

}

Once data has come back, it can be extracted by using the methods in the ResultSet class. The getString(n) and next() methods provide a way to step through the resulting data and display it as needed.

int n = 1;

While (sqlReslt.next())

{

System.out.println("Column "+ n + ": " + sqlReslt.getString(n));

n = n + 1;

}

PROGRAMMING FOR JDBC

Now that JDBC driver usage has been introduced, it’s time to consider different options for developing JDBC applications. The choice of implementation depends on issues such as applet vs. application, trusted vs. untrusted applets, Internet vs. Intranet, and thin vs. ultra-thin clients.

Using JDBC in Applets

For JDBC applets to run correctly, set CLASSPATH so that the applet can find the JDBC driver. If you plan to load the JDBC API and JDBC driver along with the applet, make sure the JDBC driver is in the same place as the JDBC applet. Applets are executed through any Java-enabled Web browser and do not require a separate Java interpreter.

The JDBC applet is distributed with the HTML page over the WWW. The applet typically is imbedded in an HTML page. For example, to call the applet JDBCApplt.java, you might use the following applet tag:

<applet code="JDBCApplt.class", width=450 height=350>

To run this applet, a client Web browser that supports Java is needed. When you load your HTML page, the applet tag downloads the Java applet to your machine which then downloads the Java class files (JDBC API), including JDBC driver.

When the JDBC applet calls the JDBC API to connect to an HP3000 database, the JDBC driver establishes separate communications with the HP3000 database through the JDBC server and database interface middleware residing on the database server.

Untrusted Versus Trusted Applets

An applet is a set of Java classes downloaded by a Web browser from a Web server. An untrusted applet, when downloaded, is restricted from gaining control of local system resources. Prior to JDK 1.1, all applets were untrusted.

With JDK 1.1 or greater, some web browsers relaxed the security restriction of "trusted" applets, which are given either limited or full access to the browser’s system.

The untrusted applet affects what a JDBC driver can do. These restrictions prohibit an untrusted JDBC applet from:

- access the local file system
- load in dynamic libraries
- open a network connection back to any host except the machine from which the applet came from

These restrictions eliminate Type I and II drivers from being used in the untrusted JDBC applets.

Local Java Applet

All of the following must be pre-installed on a client machine:

- the web browser
- the HTML file containing the path of a Java applet and the Java applet itself
- the JDBC API
- JDBC driver client

The applet resides on the local disk in a directory in the CLASSPATH and is loaded by the file system loader via Web browser. The local Java applet is always a trusted applet.

Downloaded Java Applet

The web browser is pre-installed on a client machine. The JDBC API and JDBC driver classes are downloaded along with Java applet from a Web server at runtime. These downloaded applets are generally untrusted unless they are from a legitimate source determined by digital signatures, authorization, and certification.

Using JDBC in Applications

A Java application is installed on a local disk drive or a network drive. A Java application is executed by invoking the Java interpreter, which must be installed on the machine where the Java application resides. In addition, a JDBC driver must also be pre-installed. The Java application can be started from the GUI or command line. This is similar to the configuration of a traditional C or C++ application.

Using the JDBC-ODBC Bridge

The JDBC-ODBC Bridge is distributed with JDK from JavaSoft. Since ODBC API is probably the most widely used programming interface for accessing a relational database, it is a natural choice for developer to use this as their first JDBC driver.

To use the Bridge, you must pre-install the following:

- JDK and Bridge libraries
- ODBC driver manager
- Vendor’s ODBC driver on the client and server systems

In addition, properly configure the ODBC data source and use the correct version of the Bridge.

To register the JDBC-ODBC Bridge driver, use this class name:

sun.jdbc.odbc.JdbcOdbcDriver

And the URL syntax is:

jdbc:odbc:odbcdatasourcename

There are limitations when using the JDBC-ODBC Bridge:

- An untrusted applet cannot be downloaded from the Web server. The Web browser will forbid the Bridge from loading the dynamic library.

- The Bridge cannot be used with the Microsoft Virtual Machine. The Microsoft interpreter uses RNI (Raw Native Interface) for calling into dynamic libraries whereas the Sun interpreter uses JNI (Java Native Interface). These two are incompatible.

DEPLOYING JDBC

Two-tier Model

Figure 5

A two-tier model is a client-server architecture where Java applet or application talks directly to the database. A JDBC driver is needed to communicate with the particular database being accessed. The SQL statements are sent to the database server and the results are sent back to the client.

Untrusted applets are only allowed to open network connections back to the Web server from which they came from. Place the database and Web server on the same physical machine from which the JDBC applet was downloaded from, as shown in Figure 5. The JDBC applet, JDBC API, and JDBC driver client can be either pre-installed on the client systems or downloaded from the Web server. Using a Type III or IV JDBC driver in this model would simply connect to a certain port to talk to the backend database.

This architecture is ideal for companies that want to maintain clear security and performance isolation between internal production data and public query data. However, this architecture is not an optimal solution for high-volume environments where the database engine is on the same machine as a high volume Web server.

Three-tier Model – Downloadable Applet

Thin Client

Figure 6

It is often undesirable to have the database and the Web server on the same machine. The downloadable thin client applet architecture installs a thin Java Proxy on the Web server. The Java Proxy routes the IP address to the database server. It does not perform heavy data reprocessing. The JDBC API and JDBC client are downloaded along with JDBC applet from the middle tier (Web server) as shown in Figure 6. This architecture allows developers to use downloadable applets, access corporate data, and keep the database processing off a heavily loaded Web server.

Ultra Thin Client

Ultra-thin client applications and applets become more attractive. In these environments, the high performance is a key factor. Minimizing the size of the applet and download time is crucial. Thus, the business logic is moved up to the application server on the Web server from the client.

Figure 7

As shown in Figure 7, Java’s RMI, HTTP Sockets or IIOP are used for communication between the ultra-thin client and application server (or servlet) on the Web server. The JDBC client is loaded and run from the Web server to the backend database.

The Web application server (or servelt) appears as the client to the JDBC driver client and connects to the enterprise database via the JDBC server on the database server.

JDBC ADVANTAGES

Industry Momentum

Several leading database, middleware and tool vendors have been building JDBC support into their Java products. This industry momentum ensures that user can build portable JDBC applications access to a wide range of relational database to meet their needs.

Reduced Development and Maintenance Time

The combination of Java and JDBC makes application development easy and economical. With JDBC, pre-installation and configuration is not required on the client machine since the connections are completely defined by the JDBC URL. This centralizes software maintenance and distribution.

HP 3000 as a Viable Platform

The ability of the HP 3000 to be a full participant in the Intranet and Internet environment is important. With the support of Netscape FastTrack Web server and Java on HP 3000, JDBC will increase the likelihood of new applications development on the HP 3000. This is borne out by a recent survey in which customers indicated a strong interest in JDBC. With JDBC, businesses can continue to use HP 3000 databases and access information easily.

HP is committed to maintaining the vitality of the HP 3000 platform and to meeting the needs of its customers.