The Internals Of ODBC

The Open Database Connectivity (ODBC) product was initially developed by Microsoft as a generic database driver. It’s architecture has now been generalized and many different vendors are offering open database connectivity products that are based on ODBC. ODBC is the predominant common-interface approach to database connectivity and is a part of Microsoft’s Windows Open Service Architecture (WOSA). ODBC and WOSA define a standard set of data access services that can be used by a variety of other products when interfacing with an MS-Windows application.

ODBC consists of more than 50 functions that are invoked from an application using a call-level API. The ODBC API does not communicate with a database directly. Instead, it serves as a link between the application and a generic interface routine. The interface routine, in turn, communicates with the database drivers via a Service Provider Interface (SPI), as shown in Figure 8.1.

Figure 8.1  The ODBC architecture for Oracle.

Each custom application within Windows will have call-level API calls to the ODBC database driver—which, in turn, directs the request to the appropriate database driver for execution. The database driver manages the communication between the database and the program, and handles all returning data and messages, passing then back to the ODBC driver—which, in turn, passes them back to the invoking application.

As ODBC becomes more popular, database vendors are creating new ODBC drivers that will allow ODBC to be used as a gateway into their database products. A word of caution is in order: Although most programmers can be successful with ODBC in a simple application, effective use of ODBC in multidatabase environments is a very difficult task. The programmers need to be aware of all of the different dialects of SQL, and they must also be aware of the native API to the database engines. However, a few tips can ease the effort, despite this steep learning curve.

Essentially, ODBC serves as the “traffic cop” for all data within the client/server system. When a client requests a service from a database, ODBC receives the request and manages the connection to the target database. ODBC manages all of the database drivers, checking all of the status information as it arrives from the database drivers.

It is noteworthy that the database drivers should be able to handle more than just SQL. Many databases have a native API that requires ODBC to map the request into a library of functions. An example would be an SQL-Server driver that maps ODBC functions to database library function calls. Databases without a native API (i.e., non-SQL databases) can also be used with ODBC, but they go through a much greater transformation than the native API calls.

When accessing multiple databases with ODBC, it is up to the API programmer to manage the multiple database connections and the multiple SQL requests that are being directed to the connections. In ODBC, handles are used to point to each database connection. A handle is usually a pointer into the database, and the value of the handle is a record key, a row ID, or an object ID.

Most people associate ODBC with SQL. While SQL is now the single most common access method for databases, there are many important non-SQL databases that are widely used. Popular non-SQL databases include IMS, CA-IDMS, Basis Plus, and almost all of the new object-oriented databases. It is a misconception that a database that does not support SQL cannot use ODBC.

Programming For Portability

The key to success with ODBC in a distributed relational database environment is to create the illusion of location transparency. This transparency is ideally maintained by requiring that all cross-database applications handle their queries with vanilla SQL. The term vanilla refers to the common features of SQL that are shared by all of the vendors. Determining which features are vanilla can often be a difficult task, since each major database vendor has implemented SQL with its own “enhancements” that are not shared by other vendors. Fortunately, most of these differences are found in the CREATE TABLE and referential integrity syntax, and are not germane to SQL queries. For queries, the most common extensions relate to syntax tricks that are used to force the SQL to use a specific index. For example, in Oracle SQL a null string can be concatenated into the query to force the transaction to use a specific index.

Please note that ODBC can also be used to interrogate the system tables of the target database. With additional programming effort, an ODBC routine can be written to interrogate the metadata in the target database and determine the SQL features that are supported by the database product.

For example, a database such as Oracle can be interrogated to see if it has stored procedures associated with a database event, and these procedures can be accessed by ODBC and displayed as a list. System users can then choose from the list if they want ODBC to utilize the stored procedure.

This approach can be very cumbersome, and most users of ODBC recommend the “generic SQL” approach. Unfortunately, the selection of the vanilla SQL must be done very carefully because of the differences in implementation of different vendors’ SQL. Almost every implementation has vendor-supplied, nonstandard extensions called features that are not supported by other databases. These include support for stored procedures and built-in functions that the vendor adds to enhance programmer productivity.

Several problems arise when using non-ANSI SQL:

•  The SQL is rejected as a syntax error. This is the simplest problem to correct and can be fixed in the testing phase, long before delivery of the completed system. The introduction of a new database into the federation may also cause problems if the existing SQL relies on non-standard extensions that are not supported by the new SQL dialect.

•  Cross-database referential integrity is difficult to enforce. When business rules span physical databases, the enforcement of those rules can be very hard to accomplish. Since Oracle cannot enforce RI across servers, the developers must create procedural mechanisms to ensure that the rules are maintained. For example, a business rule that prohibits any rows in tokyo.order without parent entry in cleveland.customer will have to write an extended two-phase commit transaction to roll back the entire transaction if one piece of the distributed update fails.

•  The SQL performance is different on each target database. This happens when the SQL optimizer uses different access paths for different implementations of SQL. For example, an identical SQL request that is valid for both Oracle and DB2 will return identical results sets, but may use different access methods to retrieve the data. For example, DB2 SQL uses the concept of sargeable predicates to determine the optimization of an SQL query. Depending on how the SQL request is phrased, the SQL optimizer may choose to invoke sequential prefetch, use a merge scan, or utilize other access techniques that can affect performance. Whenever possible, it is recommended that the programmer ignore this issue initially, because rewriting an SQL query for performance reasons can be very time-consuming. Of course, performance remains an issue, but the SQL tuning can be left to the final stages of the project.