InterBase and Java: making the connection

<< Using Apache, PHP and InterBase to develop websites | Database technology articles | InterBase and Java: storing and retrieving BLOB data >>

InterBase® and Java®: making the connection

By Joe Shevland

This section describes how to connect to an InterBase® database using the Java® platform and JDBC (Java® DataBase Connectivity). InterBase® 6 and Interclient 1.6 are used in these examples, however the same information may apply to older or more recent versions. The Java® 2 platform has been used throughout this section however the code should also be compatible with JRE 1.1 environments.

The JDBC driver

JDBC provides a uniform methodology for accessing relational databases, and makes use of vendor provided drivers to hide the variances in the different database products. InterBase® JDBC access also uses the standard JDBC extension package (Javax.sql.*) which is provided as a standalone JAR package.

The InterBase® JDBC driver is bundled in a JAR file called interclient.jar which is available as part of the Interclient download.

The JDBC standard extension package is available from Sun's Java® website and is bundled in a JAR file called jdbc2_0-stdext.jar.

In order to successfully connect to an InterBase® database, both of these JAR files will need to be in the CLASSPATH of your application or applet e.g.

 set CLASSPATH=.:interclient.jar:jdbc2_0-stdext.jar:<...>
 SET CLASSPATH=.;interclient.jar;jdbc2_0-stdext.jar;<...> (Windows version)

InterServer will also need to be installed and running on the server to which you are connecting.

back to top of page

Connecting to a database

Once the required JAR files are located in your CLASSPATH and the necessary software is running on the server, it is a simple matter to connect to a local or remote InterBase® server. See the example code below for the relevant details.

Example 1.0: Creating a connection

 import Java®.sql.*;
 import InterBase®.interclient.DataSource;

 ...

 DataSource dataSource = new InterBase®.interclient.DataSource();

 try {

 // Set the standard properties
 /* set the network protocol */
 dataSource.setNetworkProtocol("jdbc:InterBase®:"); 
 /* the hostname or IP address of the server */
 dataSource.setServerName("dbserver.domain.com"); 
 /* the full path to your database file */
 dataSource.setDatabaseName("/data/mydb.gdb");
 /* a descriptive name */
 dataSource.setDataSourceName("SomeName"); 
 /* a descriptive comment */
 dataSource.setDescription("SomeDescription");
 /* the TCP port Interclient listens on by default */ 
 dataSource.setPortNumber(3060); 
 /* set the InterBase® role to use */
 dataSource.setRoleName("MyRole"); 
 /* set the login timeout to 10 seconds */
 dataSource.setLoginTimeout(10);  

 // Set the non-standard properties
 /* Perform a sweep when connecting */
 dataSource.setSweepOnConnect(false); 

 // The following line actually starts up the database connection:
 Connection con = dataSource.getConnection(con_user,con_pw);

 } catch ( SQLException se ) {

 // Report the problem to the standard error stream:
 System.err.println("Exception creating the database connection: "+se);

 }

Instead of using the new DataSource approach, you can also use the older method of registering a JDBC driver with the DriverManager and calling getConnection(). A brief example follows. Note that this approach does not allow you to specify an InterBase® role (I may have spotted somewhere in the docs that you can do this by specifying a system property or some such: anyone?).

 import Java®.sql.*;

 ...

 try {

 // Resolve the JDBC driver class:
 Class.forName("InterBase®.interclient.Driver");

 // The following line actually starts up the database connection:
 Connection con = DriverManager.getConnection
 ("jdbc:InterBase®://localhost/data/mydb.gdb","SYSDBA","masterkey");

 } catch ( ClassNotFoundException cnfe ) {

 // Report the problem to the standard error stream:
 System.err.println("Couldn't locate the driver class: "+cnfe);

 } catch ( SQLException se ) {

 // Report the problem to the standard error stream:
 System.err.println("Exception creating the database connection: "+se);

 }

back to top of page

Testing the connection: A simple SELECT

Now that you have a successful connection to the database, the following code snippet shows you how to perform a simple SELECT statement and retrieve the results. You can replace the table and column details with ones you have set up.

Example 1.1: Executing a SELECT query

 // Assume we still have the Connection instance 'con' from the previous example

 try {

 String username = null;
 int userid = 0;

 String sql = "SELECT userid, username FROM users WHERE userid = 10000";

 Statement sment = con.createStatement();

 ResultSet rs = sment.executeQuery(sql);

 if ( rs.next() ) {

 // Calls to rs.next() position the result set to the next available row.
 // You'd use 'while ( rs.next() )' in the case that multiple rows may be returned.
 userid = rs.getInt("userid"); /* the column index can also be used */
 username = rs.getString("username");

 } else {

 // The row does not exist in the database

 }

 rs.close(); /* Perhaps unnecessary as the next line will close the ResultSet too */
 sment.close(); /* But some drivers have resource problems, so its to be safe */

 } catch ( SQLException se ) {

 // Report the problem to the standard error stream:
 System.err.println("Exception performing query: "+se);

 } finally {

 // If we've finished with the connection, close it in the
 // finally clause so it will get closed no matter what.
 // May need to wrap this call in another try..catch block.
    con.close();
 }

back to top of page

Closing the connection

To close an active database connection, it is a simple matter of calling the close() method of the Connection class e.g.

 // Assuming 'con' is an active Connection instance:

 try {

 con.close();

 } catch ( SQLException se ) {

 ...

    }

This paper was written by Joe Shevland and is copyright Joe Shevland and IBPhoenix Inc.

See also:
InterBase® and Java®: storing and retrieving BLOB data
Java® UDF functional specification

back to top of page
<< Using Apache, PHP and InterBase to develop websites | Database technology articles | InterBase and Java: storing and retrieving BLOB data >>