SQL Server Magazine

OLE DB and ODBC are APIs that provide access to a range of data sources. Microsoft designed ODBC to access SQL data and OLE DB to access any type of data in a COM environment. Many people don't entirely understand the factors that led Microsoft to implement OLE DB. Others overestimate the current role of OLE DB in the corporate and Web-development environments. In this article, I aim to clarify why Microsoft introduced OLE DB, the role it plays today, and more important, the role it will play in the future. In doing so, I address the main concerns that seasoned ODBC developers might have about OLE DB.

What's Wrong with ODBC?
The introduction of OLE DB doesn't mean that Microsoft is dropping ODBC. For the foreseeable future, Microsoft plans to support ODBC, as do other database and tool vendors. So what's wrong with ODBC? Nothing. ODBC is adequate for data access. My real-world experience confirms that if ODBC meets your needs and your clients' needs, you can forget (for now) about OLE DB and related technologies.

However, ODBC has become a mature, fully expressed technology, and Microsoft probably won't develop it further. ODBC is on a dead-end track with a few switches left before the end of the line. The last of these switches shunts your applications toward OLE DB. And you have to decide whether you've already passed the last switch.

So nothing is wrong, unsupported, or invalid about ODBC. You know about its performance, flexibility, and architecture. You know about the various development tools and the framework built on top of ODBC (e.g., RDO). To determine how close you are to the point of decision, analyze how well your information system's planned future enhancements match ODBC's capabilities. Keep in mind that in the next five years, ODBC will provide virtually the same operational capabilities as it does today. ODBC will continue to let you access SQL data that isn't integrated with other, nonrelational data types such as extensible markup language (XML) files, Microsoft Office documents, or email. If these elements are part of your company's data store, you need to consider OLE DB.

What's Right with OLE DB
OLE DB has evolved from ODBC. OLE DB and ODBC both provide a relatively universal layer of code that uses the same core API to access different databases. The operation under the hood is left to special, invisible modules that are database-specific and that act as drivers for the topmost layers of code. The open database connectivity principle emerges somewhat intact in OLE DB. The big difference is in the surrounding context and in the generalization of a few main terms; Table 1 shows commonly used terms and what they mean in ODBC and OLE DB.

Through a common functionality set, OLE DB accesses all types of information—relational and non-relational, flat and hierarchical, persistent and volatile, based on SQL or on any other query language. To facilitate that access, OLE DB data sources are COM-based components with a well-defined programming interface. These components, called data providers, wrap a data store. When connected to a data provider, a client application always receives a set of records, whether the data provider manages tables from a relational database management system (RDBMS) or from a directory listing. The data provider is responsible for retrieving the data from the physical source and formatting it. The data can reside on a persistent medium (disk files or databases), be kept in memory, or span various machines and platforms. It can be relational or hierarchical, structured or flat, written in a standard or a proprietary format, and accessible or inaccessible through ODBC.

In addition, the result set (called a rowset or a recordset) that OLE DB returns isn't a simple stream of bytes copied into the client application's memory, as with ODBC. It's a stream of data that an independent COM module, with a separate programming interface, holds. Such a module gives you several facilities for manipulating the recordset—sorting, filtering, and scrolling—and it enables concurrent access to the same data from multiple clients. You can set up the data rows to work even when they're disconnected from the data source, making the recordset a new and powerful data type.

Drawbacks to Using OLE DB
So, is OLE DB all good? To decide, carefully consider two points. On one hand, OLE DB is a somewhat immature technology. On the other hand, Microsoft is pushing it as the core data service for future Windows platforms. This push means that Microsoft will significantly improve OLE DB in the future. Let's look at these two points in detail to anticipate their impact on users.

Microsoft proposed OLE DB as a central data-access technology with Visual Studio 97. At TechEd 99, OLE DB 2.5—a new release that is scheduled to ship with Windows 2000—debuted. Many changes since OLE DB's inception make the technology seem somewhat unstable. But in the early 1990s, people perceived ODBC as unstable, too. ODBC 3.x and later worked fine for most people, and today developers think of ODBC as a stable and consolidated technology. But that process took time. Data is the fuel of companies, so carefully evaluate any technology that affects data access.

Figure 1 compares the architecture of ODBC and OLE DB. Both rely on specialized components (drivers in ODBC and providers in OLE DB) that connect to the data source. Within ODBC, a driver usually acts as a proxy component, passing in SQL commands to the DBMS and returning result sets. An OLE DB provider accepts data in any query language it supports (not only and not necessarily SQL) and returns recordsets. A data provider encapsulating a DBMS is limited to passing SQL commands to the underlying database server. A data provider that renders a nonrelational data store (e.g., an email store) accomplishes the additional task of creating the recordset and populating it with the data. Such a provider might support a query language that is simpler than SQL. For example, to return email from a customer, the provider needs to know only the name of the sender. This type of command

sender=Joe User; 

is simple, effective, and easy to code.

   Prev. page   [1] 2     next page