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.
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.
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); }
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(); }
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.