Network Computingwww.networkcomputing.com

Initially all available drivers are loaded by calling the class.forName() method. For example, class.forName("mycompany.mydriver"); would load mydriver. All loaded drivers should register themselves with the Driver Manager by calling registerDriver(). Once the getConnection() method is invoked, the Driver Manager calls the connect() method of all available drivers; the first driver to recognize the URL will open the connection.

This model works well if you have a lot of time and multiple drivers, which can connect to the same database source, are available. However, most of the time neither is true. But you can use something similar to the following piece of code:

IDSDriver drv = new IDSDriver();

Connection con=drv.connect(url,null);

Statememet stmt= con.createStatement();

This example will establish a connection using the IDS driver. As you can see, similar code can be used with other drivers, in effect bypassing the driver manager and choosing a driver manually. The URL specified is very driver-specific, however, and most drivers require a host name, a port number and a driver-specific protocol. The URL we used in our test applet was the following:

jdbc:ids://http://127.0.0.1:12/conn?dsn=TEST

The "jdbc" keyword will always be present. "ids" will be replaced by whatever keyword your driver uses. The host address will be there, the port number will vary and the rest of the URL will depend on your driver.

Notice that we hard-coded the IP address of our development system in this example. This would not work if you are running your code in an applet running atop a browser. Java's sandbox security prevents applets from establishing connections to hosts other than the host from which the the applet was downloaded. To make your code more portable and robust you can use another shortcut called getCodeBase.getHost(). This method will determine the IP address of the server hosting your applet.

Once the connection is established, you can use SQL statements to retrieve information from the database. With JDBC there are three different ways to execute an SQL statement:

ResultSet rs=stmt.executeQuery(sqlQuery);

stmt.executeUpdate(sqlStatement);

stmt.execute(sqlStatement);

The first method executes a query and returns a result set containing the rows from the database that correspond to the given query. The executeUpdate() method is used to update the database when using INSERT, UPDATE, DELETE, or SQL DDL (Data Definition Language). The last form is used when multiple result sets can be returned. In this case you will need to call other methods to retrieve the result sets one at a time. A familiarity with SQL will help you use JDBC statements effectively. If you are comfortable with SQL but need more information on the specific JDBC syntax you can refer to java.sun.com/products/jdk/1.2/docs/guide/jdbc/getstart/statement.doc.html.

Once your information is retrieved and you are done with the database, you need to close your connection by calling the close() method. Although this is not required by the letter of the law--Java's garbage collector should manage resources and free up the connection--in this case you should it. Most documentation available for JDBC drivers recommend that the programmer close the connection manually and not rely on the Java garbage collector to do it at an undetermined time in the future.

The Life of JDBC

Most JDBC based programs go through the following three steps in their life span:

1. Establish a connection with the database

2. Send SQL statements to the database

3. Process the results

To help you understand the process, we offer a generic SQL reporting applet that

1. Connects to a database source

2. Queries the database for the columns (fields) defined in the database (using getMetaData())

3. Presents the user with a list of the available fields

The user can then choose which fields to include in the output report, and their order. The user can also choose any number of conditions on any number of fields (whether or not they are included in the report) to control which records are included in the output. Once the user is satisfied, the applet dynamically builds an SQL query string, sends it to the database and places the output results in a grid for the user to view.

Although the applet is generic, and can be implemented using any different set of tools, we chose to use Symantec's Visual Café Database Development Edition 2.5 as the development IDE, Microsoft's Access 7.0 as the DBMS, and IDS Server 3.02 as the Type 3 driver. Symantec also offers a Type 3 driver called dbAnywhere, which could have also been used. However, we've found that the dbAnywhere server is not the fastest solution available and its memory footprint is quite large.

<APPLET CODE="Example.class" WIDTH=100% HEIGHT=100%>

<param name=Table value="test">

</APPLET>