| # JDBCTM-ODBC Bridge Enhancements | Documentation Contents JDBC

The JDBC-ODBC Bridge allows applications written in the JavaTM programming language to use the JDBCTM API with many existing ODBC drivers. The Bridge is itself a driver based on JDBC technology ("JDBC driver") that is defined in the class `sun.jdbc.odbc.JdbcOdbcDriver`. The Bridge defines the JDBC sub protocol `odbc`.

## Status of the JDBC-ODBC Bridge

The JDBC-ODBC Bridge should be considered a transitional solution. Sun Microsystems and DataDirect Technologies are working to make the Bridge more reliable and robust, but they do not consider it a supported product. With the development of pure Java JDBC drivers, the JDBC-ODBC Bridge should become unnecessary.

## Why not just use ODBC?

The JDBC-ODBC Bridge offers several advantages over "naked" ODBC.

- The ODBC API is strongly oriented toward C/C++ programmers. The JDBC-ODBC Bridge makes it unnecessary for programmers using the Java programming language to deal with non-Java language concepts.
- The ODBC API is extremely complex and intermingles high level and low level functions. The JDBC-ODBC Bridge allows the programmer to rely on the JDBC API, which is relatively simple and easy to learn.
- The JDBC-ODBC Bridge lets a program deal with ODBC through a standardized JDBC interface. That helps a program avoid being tied to ODBC when a better solution comes along.

## Why use ODBC at all?

The ideal is "Pure Java": no native code, no platform dependent features. But you may need to begin your development effort right away, without waiting for your DBMS to offer a Java only JDBC driver. Partly native drivers, such as the JDBC-ODBC Bridge, let you create programs that easily adapt to Pure Java drivers as they become available.

## What's New with the JDBC-ODBC Bridge?

- A `jdbc:odbc:` connection can now have a `charSet` property, to specify a Character Encoding Scheme other than the client default. For possible values, see the Internationalization specification on the Java Software Web Site.
The following code fragment shows how to set 'Big5' as the character set for all character data.

           // Load the JDBC-ODBC bridge driver
           Class.forName(sun.jdbc.odbc.JdbcOdbcDriver) ;

           // setup the properties
           java.util.Properties prop = new java.util.Properties();
           prop.put("charSet", "Big5");
           prop.put("user", username);
           prop.put("password", password);

           // Connect to the database
           con = DriverManager.getConnection(url, prop);

- The bridge now supports JDBC 2.0 features, which includes batch updates, scrollable cursors, and so on.
- JDBC-ODBC bridge now offers most of the features required for a J2EE compatible JDBC driver.
- Sample implementations of `DataSource` and `ConnectionPoolDataSource` are available with the JDBC-ODBC bridge.
- `CallableStatement` OUT/INOUT parameter handling has been improved.
- Automatic garbage collection of unclosed `Statement` objects has been implemented.
- Improved support for updatable `Resultset` objects has been implemented.
- Additional robustness and reliability checking as been introduced.

## New DataSource Implementations in the JDBC-ODBC Bridge

Implementations of `javax.sql.DataSource` and `javax.sql.ConnectionPoolDataSource` are now available. Both are in the `sun.jdbc.odbc.ee` package. The following code fragment illustrates how to set `DataSource` properties. The last two lines use JNDI API to bind the `DataSource` object ds to "jdbc/OdbcDB1". Assume that the `DataSource` object is targeting an ODBC DSN "dsn1" connecting to an Oracle Database.

                // Establish the DataSource object instance

                sun.jdbc.odbc.ee.DataSource ds = new sun.jdbc.odbc.ee.DataSource();

                // Provide user credentials and database name

                ds.setUser("scott");
                ds.setPassword("tiger");
                ds.setDatabaseName("dsn1");
                ds.setCharSet("..."); // optional property
                ds.setLoginTimeout(100); // optional property

                // Establish initial context and bind to the datasource target

                InitialContext ic = new InitialContext();
                ic.bind("jdbc/OdbcDB1",ds);

In the preceding code, note that some properties are optional while some are required. Properties such as port number and role name are not implemented in the JDBC-ODBC bridge `DataSource` implementations as these properties are not applicable to ODBC paradigm.

Continuing the previous example, the following code fragment demonstrates retrieving the `DataSource` object by looking up the JNDI name "jdbc/OdbcDB1". With the `DataSource` object that is obtained, the code then activates tracing and creates two connections.

                // Get the initial context of JNDI and lookup the datasource.
                InitialContext ic = new InitialContext();
                javax.sql.DataSource ds1 = (javax.sql.DataSource) ic.lookup("jdbc/OdbcDB1");

                // Set the optional printwriter where the trace log is to be directed.
                ds1.setLogWriter(new PrintWriter(new FileOutputStream("/tmp/datasource.log")));

                Connection con1 = ds1.getConnection();
                Connection con2 = ds1.getConnection("system","manager");

The implementation of `javax.sql.ConnectionPoolDataSource` uses an underlying pool of JDBC-ODBC connections. A `ConnectionPoolDataSource` object is used to create `PooledConnection` objects, which are in turn used to get `Connection` objects. From the user's viewpoint, the `Connection` object is just like any other connection.

The following code fragment creates the `ConnectionPoolDataSource` object cpds and sets its properties. The final two lines use JNDI API to bind cpds to "jdbc/OdbcPool", which can later be supplied to the method `InitialContext.lookup` to retrieve cpds.

                // Establish ConnectionPoolDataSource instance

                sun.jdbc.odbc.ee.ConnectionPoolDataSource cpds =

                    new sun.jdbc.odbc.ee.ConnectionPoolDataSource("jdbc/OdbcPool");

                // Provide user credentials and database name
                cpds.setUser("scott");
                cpds.setPassword("tiger");
                cpds.setDatabaseName("dsn1");
                cpds.setCharSet("...") // optional property
                cpds.setLoginTimeout(100); // optional property

                cpds.setMinPoolSize("10");
                cpds.setInitialPoolSize("15");
                cpds.setMaxPoolSize("20");
                cpds.setMaxIdleTime("300");
                cpds.setTimeoutFromPool("600");

                // Maintenance interval of the pool. A maintenance thread will remove
                // unwanted connections and cleanup the pool at the interval specified.
                // This cannot be zero.
                cpds.setMaintenanceInterval("900");

                InitialContext ic = new InitialContext();
                ic.bind("jdbc/OdbcPool",cpds);

In all cases in order get the `ConnectionPoolDataSource` to function as a pooled datasource, it is the responsibility of the application to configure the pool as described in the code example above. The default behavior of pooled datasource uses a zero minimum, initial and maximum pool sizes. Note that the JDBC-ODBC bridge implementation of `ConnectionPoolDataSource` does not yet include the pooling of `Statement` objects or the property `propertyCycle`.

The following code fragment shows how to use a `ConnectionPoolDataSource` object as simply a `DataSource` object. This is done by doing a JNDI lookup of "jdbc/OdbcPool" and casting it to a `DataSource` object instead of to a `ConnectionPoolDataSource` object.

                InitialContext ic = new InitialContext();
                javax.sql.DataSource ds1 = (javax.sql.DataSource) ic.lookup("jdbc/OdbcPool");

                // First getConnection will initializes the pool.
                Connection con1 = ds1.getConnection();
                Connection con2 = ds1.getConnection("system","manager");
                -------------
                -------------
                // An application need to close the connection explicitly. This will allow the pool to recycle the connection.
                con1.close();
                con2.close();

Using the implementation as a `ConnectionPoolDataSource` object is shown in the following line of code. Note that closing a `PooledConnection` object closes the actual physical connection, whereas closing a connection that was created from a `PooledConnection` object just returns it to the pool of connections.

                InitialContext ic = new InitialContext();
                javax.sql.ConnectionPoolDataSource cpds =

                    (javax.sql.ConnectionPoolDataSource) ic.lookup("jdbc/OdbcPool");

                PooledConnection pc1 = cpds.getPooledConnection();
                Connection con1 = pc1.getConnection();
                PooledConnection pc2 = cpds.getPooledConnection("system","manager");
                Connection con2 = pc2.getConnection();
                -------------
                -------------
                // An application needs to close the connection explicitly. This will allow the pool to recycle the connection.
                con1.close();
                con2.close();

                Connection con3 = pc1.getConnection();
                Connection con4 = pc2.getConnection();
                -------------
                -------------
                // This will close the physical connection!
                pc1.close();
                pc2.close();

A pool of connections can be shut down in two ways. If the method `shutDown` is given the argument `false`, only those connections that are not being used will be closed. If the argument `true` is supplied, all connections will be closed immediately, regardless of whether they are being used or not.

                // Hot shutdown
                ((sun.jdbc.odbc.ee.ConnectionPoolDataSource) cpds).shutDown(true);
                or
                // Cold shutdown
                ((sun.jdbc.odbc.ee.ConnectionPoolDataSource) cpds).shutDown(false);

Copyright © 2002 Sun Microsystems, Inc. All Rights Reserved.

Please send comments to: jdbc-odbc@sun.com