An Industry Standard: ODBC

ODBC is Microsoft’s implementation of a CLI. It enables a programmer to develop, compile, and deploy an application without targeting a specific DBMS. Modules called drivers link the application to the database of their choice. For this reason and because ODBC is independent of the network layer protocols, ODBC permits maximum interoperability. The availability of specific drivers for almost all relational database management systems has determined its success. The JDBC mechanisms are very close to the ODBC, but are adapted for Java. In designing an appropriate interface for direct use from Java, issues such as security, implementation, robustness, and portability were addressed.

The ODBC interface

The ODBC interface defines a library of function calls that allow an application to connect to a DBMS, execute SQL statements, and retrieve results. Its syntax is based on the X/Open and SQL Access Group SQL Common Applications Environment (CAE) specification (1992), which defines a standard set of error codes, a standard way to initiate a connection, and a standard representation for data types. In addition to the core functions based on the X/Open and SQL Access Group Call Level Specification, ODBC provides extended functions for handling scrollable cursors and asynchronous processing.

ODBC components

The ODBC interface defines the possible interactions between the user application and the driver manager. Figure 2-4 shows the relationship between the four ODBC components.

Figure 2-4: The four ODBC components.

The Application

The user application calls ODBC functions to send SQL statements to the database and retrieve results. It performs these tasks:

• Requests a connection with a data source

• Sends SQL statements to the data source

• Defines storage areas and data types for the result sets

• Requests results

• Processes errors

• Controls transactions; requests commit or rollback operations

• Closes the connection

The Driver Manager

The driver manager’s primary purpose is to load specific drivers on behalf of the user application. It may also do the following:

• Performs a lookup in an ODBC configuration file or system Registry to map the ODBC Data Source Name (DSN) to a specific DBMS driver

• Processes ODBC initialization calls

• Provides entry points to ODBC functions for each specific driver

• Performs parameter and sequence validation for ODBC calls

The Driver

The driver processes ODBC function calls, sends SQL statements to a specific data source, and returns results back to the application. When necessary, the driver translates and/or optimizes requests so that the request conforms to the syntax supported by the specific DBMS. The driver performs the following tasks:

• Establishes a connection to a data source

• Sends requests to the data source

• Performs translations when requested by the user application

• Returns results to the user application

• Formats errors in standard ODBC error codes

• Manipulates cursors if necessary

• Initiates transactions if they are explicitly required

There are two types of ODBC drivers:

• Single-tier drivers: Process ODBC calls and SQL statements; no real database is involved. Such drivers usually implement a small database engine themselves and rely on flat files to store the data. Some of them are able to use another application’s file format. dBASE, XLS, and FoxPro files are the most common examples.

• Multiple-tier drivers: Process ODBC calls and send SQL statements to the data source, which is the real database. Such drivers are available from database vendors. They either use platform-specific Inter-Process Communication (IPC) mechanisms such as shared memory or portable TCP/IP socket connections to communicate with their host database.

The Data source

The data source consists of the data the user application wants to access and its associated parameters — that is, the type of operating system, DBMS, and network layer (if any) used to access the DBMS.

JDBC versus ODBC

JDBC is very similar to ODBC: It is a portable, open, and published API that uses drivers to target particular databases. However, unlike ODBC, JDBC can’t be used from C or other languages. The JDBC API is much more object-oriented in its nature and use than ODBC.