Overview of InterBase JDBC Connectivity

By: Conference Speaker

Abstract: This session paper discusses the JDBC Class 4 driver for InterBase. It consists of demonstrations of different connectivity scenerios and a general overview of how to use the driver.

This is the technical paper from a talk given at the 13th Annual Borland Developer's Conference
By Shaunak Mistry – Borland Software Corporation

Shaunak Mistry is a staff engineer in the InterBase group at Borland. He has been in the InterBase R&D team for seven years where he has made significant contributions to the JDBC driver for InterBase and the InterBase engine. In his spare time he enjoys hiking, traveling, and photography.

Note: No portion of this paper may be distributed or reproduced by any means, or in any form, without the author's and/or Borland's prior written permission.

Introduction

InterClient was a new driver developed by the InterBase team to provide JDBC connectivity for InterBase. All versions of InterClient lower than 3.x are of type 3. With the latest InterClient 3.0 we are releasing our first all Java class 4 type driver. We will look into a brief history of InterClient and how it has evolved to the InterClient 3.0. Further we will look into the various methods which can be used to connect to InterBase using InterClient.

JDBC API

JDBC API

The JDBC API makes it easy to send SQL statements to relational databases. However with the advent of the newer JDBC 2.0 and 3.0 the 3.0 API goes further than just mere SQL as it also allows communication with files containing tabular data. The value of the JDBC API is that a program written using the API can be used to access databases from different vendors without any changes being made to the program. In a simplistic model a JDBC driver like InterClient enables; establishing sockets to a database; send queries and updates to a database; processes the returned results from the database.

A big question for people having existing application and expertise in ODBC is: why should we use JDBC for our newer applications? JDBC has the following advantages over ODBC:

  1. Directly callable from Java (no need of a JNI layer), ODBC relies on native C pointer logic (void *) which would be difficult to implement in Java.
  2. Allows for a pure Java solution on the client side.
  3. Is not evolving as Microsoft is interested in other newer APIs like OLE etc.

The API can be split into two parts as the core API compared and the extended optional API package. The core API comes packaged in the core JDK under java.sql, while the optional packages come in the j2ee.jar under the javax.sql libraries. The core API contains all the additions that have been made to existing interfaces and classes, in addition to a few classes and interfaces. The new optional API specification contains parts of the JDBC API which are closely related to other pieces of the Java platform such as JNDI (Java Naming and Directory Interface) and JTS (Java Transaction Service). Connection Pooling and Rowsets are also part of the optional package.

JDBC Driver Types

The JDBC specifications not only talks about what the APIs should do and how they are packaged but also details the classification of the various JDBC solutions.

There four types (or classes) of JDBC drivers:

  1. Class 1 drivers are the JDBC-ODBC Bridge, these types just convert the JDBC calls to equivalent ODBC calls. This type of driver needs the ODBC driver code and in most cases needs the database client code on the client machines. InterBase comes bundled with an ODBC driver from EasySoft, you can use this with a JDBC-ODBC bridge from Sun or EasySoft.
  2. Class 2 drivers are the Native API partly Java type drivers, these drivers convert the JDBC calls to vendor specific client API calls. The drivers need some platform specific native code on the client machine.
  3. Class 3 (InterClient up to version 2.5), client side all Java type drivers translate the JDBC calls into a DBMS independent net protocol. This is then translated into DBMS specific API calls on the server. Past users of InterClient will be familiar with this setup, we had the all Java InterClient on the client side and the native (and somewhat infamous) InterServer on the server side to translate the net protocol into InterBase specific API.
  4. The class 4 JDBC driver is the all Java solution. In this case the driver talks directly to the database by converting the JDBC calls directly into the DBMS specific network protocol.

Type 3 and 4 are the preferred drivers for performance and all java solution reasons.

2 types of JDBC applications

The creators of the JDBC 2 specification had two types of applications in mind using the API. These two applications are generally classified as 2 tier and 3 tier applications.

The 2-tier application is the classic client/server application. It assumes that the database server and the application server are on the same machine.

 

The 3-tier system is also called the n-tier system, this is the new paradigm for developing applications, in this case the application server and the database server are on different machines. This model allows you to separate the client the application server (web server) and the backend database server.

JDBC 2 provides various methods to enable connecting to the database in both of these models. These are covered below.

InterBase JDBC driver - InterClient

InterClient is the JDBC driver provided by the InterBase team at Borland.

InterClient type-3

When InterClient was first release it started as a type 3 driver. The connection diagram is show here

The idea at that time was that the InterBase team could get into the business of providing a middleware JDBC API solution for various databases. However this did not work out, actually another team with in Borland did make an effort to make a product out of this idea it was called Data-gateway. The advantages of this type of a driver are performance and the small download library size of the jar file.

InterClient type 4

With InterClient 3.0 we have moved to the type 4 driver that is all java, The main motivation for moving to this type of driver was the ability to provide an all Java solution on the client side. An all Java solution had been a long time request from Borlands two all Java product groups (JBuilder and Borland Application Server). This move from type 3 to type 4 should not require any changes in the applications build using JDBC. However there will be changes in the way the applications are deployed, as there is no need for starting the InterServer service on the database server.

With this move to type 4 we have two main concerns. The first concern involves the size of the downloadable jar. If this does become a significant problem we can look into making a RMI library to overcome this issue and splitting the JDBC client from the InterBase client. The other concern that has been raised internally has been of performance due to the code which was native is now going to be running in a Java JVM. The idea is that the change from type 3 to type 4 will remove one Inter-Process communication (shared memory on Windows, and socket on Solaris/Linux) layer.

Connecting to InterBase Databases

The JDBC API provides some options on how you connect to a database. These APIs can be divided into two parts, one that is available in the core API and another that is available through the optional API.

JDBC is a dynamic API, so once you get a connection to a database, you can query the database meta data which describes JDBC driver capabilities, database engine capabilities, and database schema such as columns, tables, stored procedures, etc.

Core API - DriverManager

The DriverManager class is responsible for selecting the database driver you want to use for JDBC database access. But before you can use the manager, you must activate a driver, which is registered. Once the necessary drivers are registered, an application can get a connection by calling DriverManager.getConnection() with a database URL, username, password, and connection properties. The driver manager will use the JDBC driver that's appropriate for the particular database URL, and return a connection established using that driver.

The simplest way probably is for the program to set the driver directly in the application.

String url = "jdbc:interbase://localhost/e:/testbed/database/employee.gdb";
Class.forName("interbase.interclient.Driver");
//Driver d = new interbase.interclient.Driver (); /* this will also work if you do not want the line above */
Connection conn = DriverManager.getConnection( url, "sysdba", "masterkey" );

However as can be obviously seen the if we want to change anything like the Driver, URL, username and password we will need to recompile. In order to overcome this problem we can use a properties file. There is nothing new about the concept of reading the parameters from a file except for the fact that java provides some useful calls to parse the text file. In order to use this, the application can set the system property jdbc.drivers to a list of drivers. To do this, first create a text file named borcon02.properties with the following lines:

jdbc.drivers=interbase.interclient.Driver
jdbc.url= jdbc:interbase://localhost/e:/testbed/database/employee.gdb
jdbc.username=sysdba
jdbc.password=masterkey

Now change the java code to use this file for the connection.

FileInputStream input = new FileInputStream("./borcon02.properties");
props.load(input);
String drivers = props.getProperty("jdbc.drivers");
if (drivers != null)
System.setProperty("jdbc.drivers", drivers);
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password");
Connection conn = DriverManager.getConnection( url, username, password );

 

Extension API - DataSource

When deploying a JDBC 2 application in an enterprise environment, the management of database connections can be integrated with the Java Naming and Directory Interface (JNDI). A directory manages the location of data sources across the enterprise. In such an environment, you can use something similar to establish a database connection.

Context jndiContext = new InitialContext;
DataSource ibSource = (DataSource) jndiContext.lookup (jdbc/borcon02);
Connection con = source.getConnection (username, password);

The DriverManager is not required. Instead the JNDI service locates a data source. The idea with JNDI is to free the application from having to make these decisions, and put the information about what driver should be used with what database into some kind of naming service repository. So a database could be moved from one machine to the next, and only the name repository would have to change, not the application. A data source is an interface that allows simple JDBC connections and advanced services like the PooledConnection and distributed transactions that involve multiple databases (XA). A jndi url would be something like "jdbc:interbase:jndi:LDAP_hostname:port_number/...". The jndi service provider implementation could use an LDAP server (lightweight directory access protocol).

The complete DataSource interface lies in the javax.sql standard extension package. The trend in enterprise JDBC deployment is to support increasing array of services directories and connection pooling but to have them decoupled from database programming. Scalability and performance enhancement are assumed to be available on the enterprise system of your choice.

Connection Pooling and XA resources are on our internal priority list of features that we want to implement and support in 3.0 or the next version.

SQL Commands

Now that we have a connection we can execute various SQL commands for manipulating the data in the various database table as well as modification of the database meta-data which would involve adding, removing or modifying tables, index, and other RDBMS features. The SQL commands can be broadly classified into executing commands and query commands. Executing commands result in some change in the data or metadata in the databases, while query commands results in just retrieving data from the database.

Execute commands

These commands have actions such as INSERT, UPDATE and DELETE and data definition commands such as CREATE TABLE and DROP TABLE. To Execute a SQL command you first need a Statement object. This statement object can be created using the Connection object we received from the Driver or the DataSource object.

Statement stmt = conn.createStatement();

Now that we have the Statement we can create a regular SQL statement and pass it on to be executed with this statement:

String sql = update job set min_salary = min_salary + 1500 where min_salary < 40000;
stmt.executeUpdate(sql);

The executeUpdate method returns the number of rows that were affected by the SQL command. So in our example above it will return the number of rows whose minimum salary was updated (18 rows updated).

Note that executeUpdate method cannot be used to execute SELECT queries. This will be covered in the next topic.

Once we have returned successfully from the executeUpdate method we commit our updates or roll them back. There is a minor caveat, auto-commit is set to true by default for JDBC drivers. We need to first set this to false before we create the statement by

con.SetAutoCommit(false);

Once set to false we use the createStatement method followed by the executeUpdate method to run our command. Now we can choose between committing our changes or rolling back by using

con.commit();
for commit, and:
con.rollback();
for rollback.

Query Commands

Once again in order to make a query we need to create the Statement object as described above. To execute a query command, simply replace the executeUpdate method by an executeQuery method. The SQL String supplied in this case must be a query and not an execute statement. Further the same Statement object can be reused for unrelated queries. As is the case in other embedded SQL languages we are interested in the returned result of the query. (This returned result is often called a cursor in older embedded SQL languages.) As far as JDBC is concerned the returned results are available in the ResultSet object.

String sql = select * from employee;
ResultSet rs = stmt.executeQuery (sql):

Now that the query has been executed the results will need to be analyzed one at a time using the loop

while (rs.next())
{
//access the contents of the result set one row at a time.
}

We need to use the getXxx methods (accessor methods) to access the database columns of the individual row. There is an accessor method available for every Java language datatype, such as getString and getShort. Each of these methods has two forms, one takes the column name as its argument while the other takes a numeric argument. In case of the latter the number represents the column offset in that particular ResultSet. Hence in our example a rs.getString(2) will return the data of FIRST_NAME column from the EMPLOYEE table for a particular row. Once we reach the end of rows for the table the next method of the ResultSet object will return false and we will fall out of the while loop above. Here are some examples of using the accessor methods:

String fName = rs.getString(2);
Short empNo = rs.getShort(EMP_NO);

As an example we have modified our while loop above to get these values from the employee table and print them:

while (rs.next())
{
//access the contents of the result set one row at a time.
String fName = rs.getString(2);
Short empId = rs.getShort(EMP_NO);
System.out.println (fName + t + empNo);
}

Now that we can create connections, statements etc. we need to inform the driver to release the resources it has reserved for our use, once that is done we have all we need to write simple JDBC applications. In order to close a connection and resultSet we call the close method on the object. We add this to our sample application as:

rs.close();
conn.close();

Also note that most JDBC methods throw the java.sql.SQLException and we must be prepared to catch it and take the required action. The getSQLState method returns the SQLState formatted using the X/Open Standard. The getErrorCode gets the vendor-specific exception code, and finally the getNextException gets the exception chained to this one. It usually will contain more information about the error.

 

catch (SQLException e)
{
while (e != null) {
System.out.println ("SQL State: " + e.getSQLState ());
System.out.println ("Error Code: " + e.getErrorCode ());
System.out.println ("Message: " + e.getMessage ());
e = e.getNextException ();
}
}

Refer to the CD for the complete source code for our program, which will update the values in the JOB table, display the updated values and then display the FIRST_NAME and EMP_ID of employees from the EMPLOYEE table.

Advanced commands

Sometimes it is more efficient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, which we already have discussed above. If you want to execute a Statement Object many times, it will normally reduce execution time to use a PreparedStatement object instead. The main feature of this object is that unlike a Statement object, PreparedStatement is given a SQL statement when it is created. This statement is then sent to the InterBase engine where it is compiled. This means that when the statement is executed: the InterBase engine just runs the precompiled statement. The other feature of a PrepareStatement is that it allows us the use of parameters. The advantage of a parameterized query is that you can use the same statement and supply it with different values each time you execute it. In the example describe above we can replace the query by a parameterized query SELECT * FROM EMPLOYEE WHERE EMP_NO = ?, here the ? will be replaced at run time. Here is how it looks in code:

pstmt = conn.prepareStatement ("Select * from employee where emp_no = ?" );

 

In order to set the value, we use the setXxx methods. In this case since the EMP_NO is SMALLINT we use setShort method, let us select the record for the employee whose EMP_NO is 9:

pstmt.setShort(1, (short)9);

 

Other commands not currently supported in InterClient.

Some of the JDBC 2 features are not supported in InterClient, such as scrollable cursors, batched updates, connection pooling and distributed transactions. We are actively looking at providing connection pooling and distributed transaction support.

Performance tips when using InterClient

Here are some tips that should be kept in mind when using InterClient:

  1. Use stored procedures for lots of inserts
  2. Use prepared statements for repeated executions of SQL
  3. Use VARCHAR for large text fields  This will no longer be a problem with the newer class 4 driver as this has been fixed in the InterBase client library for the 6.5 release.
  4. Cancel long running queries before terminating clients.

Summary

In summary, we covered:

  1. What are JDBC drivers, where they are used, and how they are classified.
  2. Where does InterClient fit into the JDBC driver scenario.
  3. How to use InterClient to connect to InterBase database.
  4. Performance tips for InterClient.

Server Response from: ETNASC04