Easysoft JDBC-ODBC Bridge User's Guide - Introduction

Introducing the Easysoft JDBC-ODBC Bridge

The Easysoft JDBC-ODBC Bridge (JOB) is a Type 3 JDBC Driver that enables 100% pure Java programs to make calls across the network from any Java-enabled platform to an ODBC data source running on Windows or Unix.

The interface at the client end is JDBC, allowing the Easysoft JDBC-ODBC Bridge to be incorporated into ready-written Java database-aware programs.

The Easysoft JDBC-ODBC Bridge implements all the functionality required for working with Java 2, Enterprise Edition (J2EE) compatible products and is "Certified for J2EE" under the certification program managed by KeyLabs, Inc. on behalf of Sun Microsystems, Inc. (see JDBC Certification).

Chapter Guide

Why ODBC?

ODBC is an API (Application Programming Interface) definition, compliant with ANSI SQL and X/Open's SQL Call Level Interface (CLI).

Applications can be written to this API standard without considering the intricacies of the various database engines, as the ODBC driver takes care of all the database-specific code, if necessary mapping the structure of the underlying system to a relational framework.

ODBC permits the DBMS-specific parts of the program to be separated from the part that fulfils the business requirement.

The result is that the completed application can be attached to any Database Management System (DBMS) that has a corresponding driver.


REF

For the definitive SQL CLI document consult the Open Group CAE Specification C451, ISBN 1-85912-081-4 at http://www.opengroup.org/pubs/catalog/c451.htm.

The Microsoft ODBC 3.0 Programmer's Reference explains ODBC usage in some detail.


Why JDBC?

Given that you are using Java and you need access to a database, JDBC is really your only option. JDBC is the standard database Application Program Interface (API) for Java, supported by Sun and many large database vendors.

JDBC is modelled on ODBC but in addition provides an object-oriented model for accessing databases, permitting use of Java methods as well as SQL for querying and updating data.

The JDBC standard means that applications can be written without considering what driver will be used in the final deployment, and gives system managers the freedom to change database engines without requiring a change in program logic.

With the Easysoft JDBC-ODBC Bridge, you can connect your Java programs to any ODBC-conformant database without waiting to see whether the relevant JDBC drivers become available.

JDBC driver types

The Sun website at http://java.sun.com/products/jdbc/datasheet.html identifies four JDBC Driver types to help customers select the correct driver (these explanations use Sun's own terminology and diagrams):

Type 1: A JDBC-ODBC Bridge (not the Easysoft JDBC-ODBC Bridge)

Figure 1: Type 1 JDBC Driver architecture

Type 1 JDBC drivers are always written in platform-specific code and thus limit the potential of the application for cross-platform deployment.

This simple JDBC driver is provided by Sun with the Java Development Kit and implements the JDBC API by making ODBC calls, allowing connection to any ODBC data source available on the local machine.

Type 2: Native-API Partly Java technology-enabled driver

Figure 2: Type 2 JDBC Driver architecture

Type 2 JDBC drivers talk directly to the API of the DBMS, rather than any mapping layer such as ODBC, and can be accessed from Java.

This implies that the driver either does not provide the complete JDBC API (but provides enough to drive the native database API) or is not written completely in Java (thus losing out in cross-platform functionality).

Technically, this type of driver is the most efficient user of machine resources, but this advantage is far outweighed by the need to write a different JDBC driver for each DBMS and for each platform.

Type 3: Net-protocol fully Java technology-enabled driver

Figure 3: Type 3 JDBC Driver architecture

Type 3 JDBC drivers are client-server pairs which use a database-independent network protocol to bridge the network.

The client is written in Java, enabling cross-platform deployment of the application.

Sun cites this as generally "the most flexible JDBC API alternative", as the location of the database is not confined to the same machine as the Java program, and the client can be written in pure Java.

Platform and database dependent code is moved to the server running the ODBC driver, allowing these drivers to run over the internet.

Type 4: Native-protocol fully Java technology-enabled driver

Figure 4: Type 4 JDBC Driver architecture

Type 4 JDBC drivers are written entirely in Java and transmit database-specific messages direct to the DBMS, which cannot be changed without modifying or replacing the driver.

Architecture

At the server end, although the Easysoft JDBC-ODBC Bridge has a Type 3 architecture, it produces ODBC calls which can be picked up by any ODBC driver and used to drive the database, rather than DBMS-specific (`native') calls.

The JOB Server is dependent only on the platform and the database engine can be replaced without users, application programmers or the Easysoft JDBC-ODBC Bridge knowing and as long as the chosen DBMS supports ODBC the code can remain unchanged.

At the application end, the driver is pure Java and therefore completely portable.

The following table summarizes the advantages of the Easysoft JDBC-ODBC Bridge:


Type 1 Type 2 Type 3 Type 4 Easysoft
Can connect to ODBC data sources Yes No Not always No Yes
Application has "write once/run anywhere" platform independence No No Yes Yes Yes
Can connect across internet No No Subject to firewall Subject to firewall Subject to firewall
Can replace database engine without changing JDBC driver Yes No Not normally No Yes

Figure 5: Advantages of the Easysoft JDBC-ODBC Bridge


NB

In practice, the SQL syntax and semantics vary slightly between database vendors. By carefully partitioning your code however, the database-specific functions can be stored on the server, so that the deployed programs do not need to be modified.


Note that if you want to utilize the support of the Easysoft JDBC-ODBC Bridge for JDBC 2 functionality within your applet then you must amend your HTML to work with the Java Plug-in, which users may install on their client in order to override their web browser's default virtual machine and take full advantage of the latest capabilities and features of the Java 2 SDK.

The Java Plug-in is available as a component of Sun's Java 2 Runtime Environment and you can obtain the Java Plug-in HTML Converter and supporting documentation from http://java.sun.com/products/plugin.

In order to develop and test for JDBC 2 functionality you need to:

Driver Managers

The barest ODBC system would include an ODBC-conformant driver accessing some data, and an ODBC-conformant application, linked to the driver library.

If commercial applications were distributed in this way, users would need to re-link their applications to their chosen driver whenever they wanted to access a different data source.

Instead, the application program is linked to a driver manager, which is responsible for loading the required ODBC driver at runtime and then initializing the driver with a stored set of attributes:

Figure 6: The Driver Manager as a dynamic linker

To support user-transparent replacement of the back end the driver manager provides a mapping from an abstract Data Source Name to a driver along with a default set of attribute=value pairs which are passed to the driver on connect.

Although it is possible to use ODBC without a driver manager by linking directly with the ODBC driver at build time, this option needlessly removes some beneficial functionality and the method of linking programs with the driver manager shared object (Unix) or DLL (Windows) provides three key results:

On Unix, the unixODBC driver manager is included in the Easysoft JDBC-ODBC Bridge distribution.

Windows users should have the Windows ODBC driver manager included with the ODBC Driver for their database application.


REF

For the Microsoft Windows driver manager, go to http://support.microsoft.com and search for MDAC, Microsoft Data Access Components.

See http://www.unixodbc.org for the full set of unixODBC data access components.


Components

The Easysoft JDBC-ODBC Bridge is installed on the server (i.e. the host where the ODBC data source is installed) and comprises of:

This is a native-code program that services incoming requests for data and executes code in the ODBC driver.

This allows your JDBC application to transmit these requests across the network.

This allows the client computer to download the Easysoft JDBC-ODBC Bridge classes at runtime and configure the JOB Server.

Deployment

Scenario 1: Java Application

Scenario 1 shows a Java application accessing two remote ODBC data sources using the Easysoft JDBC-ODBC Bridge.

The Easysoft JDBC-ODBC Bridge Client is 100% Java and therefore the only requirements of the client device are a Java Virtual Machine and TCP/IP connectivity.

One database is hosted on the same platform as the Easysoft JDBC-ODBC Bridge Server and another on a separate device.

Figure 7: A Java application using JOB

Scenario 2: Zero Installation Java Applet

Scenario 2 illustrates the loading of a Java applet from a Web Server.

No client installation is required.

Applet rules require that the applet and driver are served from the same machine on which the Easysoft JDBC-ODBC Bridge Server is running.

The target database can be local to the Easysoft JDBC-ODBC Bridge Server or on a remote database server.

Figure 8: A Zero Installation Java applet using JOB

Scenario 3: Zero Installation Java Applet from a different machine

Scenario 3 is a variation on scenario 2 and demonstrates how the HTML page containing the applet tag can be served from a different machine to the applet code and Easysoft JDBC-ODBC Bridge Client.

Figure 9: A Zero Installation Java applet using JOB with multiple servers

Scenario 4: Heterogeneous Database Access

Scenario 4 introduces the Easysoft ODBC Join Engine as a device to simplify and optimise multi-database queries.

To the Java application on the client platform the three existing ODBC data sources on the Easysoft JDBC-ODBC Bridge Server appear as a single heterogeneous data source.

Queries are passed to the Easysoft ODBC Join Engine and resolved on the Easysoft JDBC-ODBC Bridge Server platform, reducing both execution times and network traffic.

The Easysoft ODBC Join Engine can be used in this way with both Java applications and Java applets.

Figure 10: Heterogeneous database access using the ODBC Join Engine