The Future of Data Access: ODBC, DAO, and OLE DB

In the client/server database world, Windows NT is steadily gaining favor as a server operating system and Windows operating systems are the dominant client platforms. Microsoft has added data access to the feature set of its Windows applications by using technologies that are available to programmers and end users.

When discussing database client applications, it's easy to use the local desktop as a frame of reference, but distributed-object technologies will create situations where objects are everywhere and computers can act as servers in one context and clients in another. Many pundits see future versions of today's Windows NT as becoming the desktop operating system of choice for enterprise applications, so it's a good idea for NT users to understand data-access solutions, particularly Open Database Connectivity (ODBC), Object Linking and Embedding (OLE), and Data Access Objects (DAO).

What are ODBC, OLE, and DAO? The short answer is: ODBC is a programming interface for SQL data access; OLE is a binary standard for object sharing; and DAO is an object layer that encapsulates data-access services. ODBC is based on a SQL industry standard; OLE is an open specification from Microsoft; and DAO has been, until recently, a technology available only with Microsoft developer products. For the long answer, read on!

Open Database Connectivity
ODBC is a call-level interface (CLI). It lets application programs call functions exported from dynamic link libraries (DLLs). The first implementation of ODBC was for Windows, but it's now available for the Macintosh, Power Macintosh, OS/2, and a variety of UNIX and other operating systems. ODBC uses SQL for all data access: relational or non-relational, text or spreadsheet.

Microsoft developed ODBC by extending a CLI originally specified by the SQL Access Group (SAG), a consortium of SQL vendor companies. A revised version of the SAG specification has been accepted by X/Open, ANSI, and the International Standards Organization (ISO), prompting Microsoft to update ODBC to align with the new standard. Microsoft has no interest in seeing ODBC take a different course. An updated version of the SAG and X/Open CLI, which includes enhancements for SQL-92, recently became the international standard programming interface for SQL-92.

Prior to its adoption, the SQL standard included only embedded SQL, which uses source-code preprocessing and compile-time data binding. A CLI permits execution-time binding, so it's easier for writing interoperable applications.

ODBC uses an environment handle, connection handles, and statement handles to track information and resources. An application has a single environment handle, but it may have multiple active connections and statements. ODBC has functions for allocating handles, connecting to databases, binding program variables to columns in tables, executing SQL statements, fetching bound or unbound data, and returning error information.

ODBC supports transaction processing (TP) with isolation levels, pessimistic and optimistic locking, and the ability to commit or rollback database changes. It uses an abstraction known as a data source to encapsulate the server names, database names, network libraries, directory paths, or other information necessary to provide a unique path to the data.

One major difference between ODBC and a proprietary application programming interface (API) (e.g., Oracle Call Interface) is that ODBC works with many different database management system (DBMS) products by reporting what features, SQL, data types, and API functions a DBMS and its driver support. (Reporting, not mandating, is the operative word here.)

Many developers believe incorrectly that because ODBC represents a standard, they can write applications using a standard set of features and data types that all the DBMSs support. However, ODBC tells an application what a DBMS can do, not what it must do.

ODBC uses loadable drivers for each type of database it connects to. Because the capability of a text-data driver differs from that of a massively parallel server (e.g., the White Cross 9000), ODBC defines levels of conformance with SQL grammar and the ODBC API. An application can make execution-time calls to determine a driver's features (e.g., whether it supports outer joins).

ODBC includes an SQL grammar that uses escape clauses to express extensions, such as outer joins, scalar functions (providing aggregation, string processing, phonetic searches, and so on), calling procedures, and dates. Escape clauses enable you to express a query in a DBMS-neutral syntax. The driver translates the query to native syntax before it sends the statement to the DBMS. ODBC 2.0 includes more than 50 functions for SQL data access alone. It also has functions for installing drivers and configuring data sources. (For information on the next version of ODBC, see the sidebar "ODBC 3.0" on page 29.)

Direct Access Objects
Microsoft developers in Redmond developed DAO to be an object layer to simplify programming for applications using Microsoft Access's database engine, commonly known as Jet. Jet can attach desktop databases (e.g., Paradox) and SQL servers (e.g., Oracle). When attaching a local desktop database, Jet uses a keyset-driven cursor model and Indexed Sequential Access Method (ISAM)-based techniques. When accessing SQL servers, it uses a keyset-driven cursor model and ODBC.

Figure 1 illustrates the DAO hierarchy of collections and objects. DAO raises the level of abstraction in database programs by insulating you from having to manage low-level details. It represents a single means of providing access to multiple, heterogeneous data sources. You can write one routine using common collections and objects and use it whether the data is in an Access, ISAM, or remote SQL database. Microsoft includes DAO in Access, Visual Basic (VB), and Visual C++, so there have been interim releases to synchronize the versions across product lines.

Microsoft didn't build Jet or DAO with an OLE infrastructure, so Visual C++ programmers had to use ODBC and SQL to work on Access databases. Access and VB users, however, could work directly on ISAM and Access databases. For Visual C++ 4.0, the company rewrote DAO as an OLE Automation server, opening the door for use by multiple applications. Microsoft has been using Visual Basic for Applications (VBA) as a common language across applications so DAO code is highly portable. This lets Visual C++ developers prototype with VB and Excel users to reuse code from Access applications.

   Previous  [1]  2  3  Next