ODBC is a multidatabase API for programs that use SQL statements to access data. An ODBC-based program can access heterogeneous databases without needing source code changes-one program can retrieve and store content in different vendors' databases via the ODBC interface. ODBC thus provides database-neutral delivery of both SQL and database content. Be aware, however, that you must load ODBC driver software for each vendor's database you want to access. Contrast this situation with that of the generic data access tools mentioned earlier.

ODBC Basics

Microsoft created ODBC in 1992 by extending the CLI (call level interface) from SAG (SQL Access Group, now part of X/Open). It gained acceptance over Borland's Integrated Database Application Programming Interface (IDAPI). The ANSI and ISO (International Standards Organization) adopted an updated version of that CLI as part of the SQL-92 standard, and ODBC version 3.0 aligns with that standard. ODBC drivers give applications a number of methods they can use to access databases, and these drivers also give tools the ability to retrieve and update metadata-the higher level of information in the database that specifies table names, column names, column attributes and other management data. However, because they're designed for a particular vendor's database product, vendor-specific database access middleware drivers give applications and tools an even greater ability to manage metadata within the database. ODBC is a lowest-common-denominator approach to database connectivity.

Database neutrality, multiple platform support and its adoption as a standard are the reasons data processing managers, system designers, and programmers like ODBC. The database product vendors as well as several third-party vendors give away or sell ODBC middleware drivers for a variety of operating environments and databases. ODBC is, by far, the most popular database access middleware in use today.

ODBC as a Layer of Middleware

ODBC defines the client side of database connectivity but not the server side, and ODBC middleware drivers typically rely on the underlying presence of a vendor's proprietary driver (SQL*Net, in the case of Oracle). ODBC drivers transform ODBC calls into vendor-specific access requests and responses. As a result, network administrators must install and configure not only an ODBC driver on each client but the underlying vendor-specific proprietary driver. Another result of ODBC's transforming requests and responses into vendor-specific data access calls is a lack of identity for ODBC network messages to and from the database server. If you used a protocol analyzer to capture database server network traffic, you'd find distinguishing ODBC network messages from vendor-specific driver messages difficult.

ODBC's addition of an extra layer of insulating middleware is both its strength and its weakness. ODBC presents a common, standard interface no matter what the vendor-specific middleware might look like, but ODBC consumes some memory and, in its earliest incarnations, slowed data access noticeably. Current offerings from companies like INTERSOLV, Visigenic Software, and Information Builders exhibit considerable performance improvements over previous versions and have consistent feature support across all major database products.

ODBC's network utilization depends more on the underlying vendor-specific layers of middleware, such as Oracle's SQL*Net, than on the ODBC driver itself. In particular, Oracle's layer of database access middleware beneath ODBC causes the least network traffic, while Microsoft's DB-Library connection to its SQL Server database causes the most. Network protocols can make quite a difference, too. NetBEUI is the "chattiest" protocol, IPX/SPX considerably less chatty, and TCP/IP the most frugal user of the network.

ODBC's Usefulness

ODBC drivers provide access to more than just multiuser relational databases. ODBC drivers exist for Microsoft's Access and FoxPro, Borland's Paradox and other desktop PC data sources. Many word processors and spreadsheets have ODBC support, as does Lotus Notes. Even some document management systems have ODBC interfaces. These examples notwithstanding, probably the most common use of ODBC is in client/server applications.

A typical client/server approach has program and data files on a file server, accesses a relational database and often has a two-tier architecture. Both presentation and business logic occur on the client, and data storage occurs on the database server. In contrast, a Web-based approach puts presentation software at the client (browser), business logic on an application server and data storage on yet another server-a three-tier architecture. While not all designs are as simple as this characterization portrays, you might think of the client/server versus Web approaches in terms of two-tier versus three-tier. Client/server applications frequently rely on ODBC for database connectivity, primarily because development environments such as Visual Basic and Delphi provide easy-to-use programming templates and easy-to-understand interface descriptions. If the computer programs running on an application server in a Web-enabled environment are written in Visual Basic, Delphi, or other non-Java language, they likely also use ODBC for their database accesses. Web-enabled applications written in Java, on the other hand, almost always use JDBC.