Michael Pizzo and Jeff Cochran
Microsoft Corporation
March 1997
This paper presents an introduction to OLE DB programming using the Microsoft® OLE DB Software Developer's Kit (SDK), which can be downloaded from http://www.microsoft.com/oledb/. The goal of this paper is to aid Open Database Connectivity (ODBC) programmers in understanding and using OLE DB. It discusses the relationship of ODBC calls to corresponding OLE DB methods and the related programming issues. Basic OLE DB concepts are discussed but advanced OLE DB features, such as notifications, IPersist * objects, transaction objects, and coordinated transactions, are not covered.
OLE DB and ODBC are application programming interfaces (APIs) designed to provide access to a wide range of data sources. A data source consists of the data, its associated database management system (DBMS), the platform on which the DBMS exists, and the network used to access that platform.
ODBC is designed to provide access primarily to SQL data in a multiplatform environment. OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. OLE DB includes the Structured Query Language (SQL) functionality defined in ODBC but also defines interfaces suitable for gaining access to data other than SQL data.
OLE facilitates application integration by defining a set of standard interfaces, groupings of semantically related functions through which one application accesses the services of another. Interfaces are the binary standard for component object interaction. Each interface contains a set of functions that define a contract between the object implementing the interface and the client using it.
OLE DB is designed using OLE COM; for more information about COM, see the Microsoft OLE 2 Programmer's Reference, Volume 1. Each interface consists of a set of related methods. The full functionality of OLE DB is factored into a number of different interfaces. Each data provider will implement some subset of these interfaces.
Some of the key OLE concepts that you should be aware of include:
An interface in OLE is a set of related methods. A single OLE object may support multiple interfaces at the same time. The consumer of an object can move between any interfaces supported on that object by calling QueryInterface (see "Interface negotiation," below). If an object supports an interface, it supports all of the methods within that interface. Thus, once the consumer has determined that a particular interface is supported, it understands how to interact with the object. New interfaces that augment the functionality already supported by the existing interfaces can be added later, but methods can never be added or removed from existing interfaces.
Because an object must either support all or none of the methods within an interface, interfaces are generally factored according to functionality. For example, if an object supports reading data and does or does not support writing data, the methods for reading data and those for writing data would appear in two different interfaces. Only the objects that supported writing data would support the interface containing the methods for writing data.
OLE DB makes extensive use of interface factoring. Individually supportable functionality, such as different levels of scrollability in a result set or command preparation and parameter support, are factored into different interfaces. Each object has one or more required interfaces that encapsulate base functionality for that object and that can expose extended functionality by implementing one or more optional interfaces. The OLE DB consumer can determine what extended functionality the provider supports by querying for these optional interfaces.
IUnknown is implemented by all component objects. All other interfaces inherit, directly or indirectly, from this interface. It has three methods: QueryInterface, AddRef, and Release. Each interface is identified by a globally unique identifier (GUID) by which it is known at compile time. To determine if an object supports a particular interface, the client calls QueryInterface on that object. If an object supports the requested interface, QueryInterface returns a pointer to the interface. The interface identifier (IID) allows the client to dynamically determine, by way of a call to IUnknown::QueryInterface, the capabilities of other objects and to get the pointers to needed interfaces. Every interface that is obtained directly (by calls to QueryInterface) or indirectly (by calls to a method that returns an interface) must be released by calling the Release method of that object.
Reference counts are kept on each instance of a pointer to an interface that is derived from IUnknown. This ensures that the object is not destroyed before all references to it are released.
OLE uses two kinds of memory: local application task memory and shared memory. All task memory allocated by the OLE libraries and by the object handlers is allocated using either an application-supplied allocator or the default allocator provided by OLE.
The standard memory management model in COM requires that the callee allocates and the caller frees. OLE DB generally follows this model, except for certain cases where performance can be gained by the caller allocating and reusing the same piece of memory, or in some special cases, by the callee giving the caller pointers to callee-owned data. In this case, the caller is not allowed to write to or free the memory.
All COM interface methods pass Unicode rather than ANSI strings. OLE DB follows this convention, except for getting and setting ANSI data that resides in tables.
OLE DB providers can be classified as data providers and service providers. A data provider is one that owns data and exposes it in a tabular form. Some examples are relational database systems and spreadsheets. A service provider is any OLE DB component that does not own the data but encapsulates some service by producing and consuming data through OLE DB interfaces. Examples are query processors and transaction managers.
The following illustration shows the core object model of OLE DB.
The data source object is the initial object returned from an enumerator (see "Enumerator Object," below), generated by binding a file moniker or other moniker to a data source, or instantiated by calling the OLE function CoCreateInstance with a given OLE DB data provider's unique class identifier (CLSID). It encapsulates the functionality of the ODBC environment as well as the connection and informational properties of the ODBC connection.
A session object defines the scope of a transaction and generates rowsets from the data source. If the provider supports commands, the session also acts as a command factory. The data source object can also support interfaces for describing schema information and for creating tables and indexes for providers that support that functionality. Along with the data source object, the session encapsulates the functionality of the ODBC connection. Calling IDBCreateSession::CreateSession creates a session from the data source object. There can be multiple sessions associated with a data source object.
If a provider supports building and executing queries, it exposes a command object. A command object is generated from a session object. It is used to specify, prepare, and execute a DML query or DDL definition and associated properties. The command encapsulates the general functionality of an ODBC statement in an unexecuted state. There may be multiple commands associated with a single session.
A rowset object is a shared data object that represents tabular data, such as a result set returned by executing a query. Minimally, rowsets can be generated by calling IOpenRowset::OpenRowset on the session. All providers are required to support this minimal functionality. If the provider supports commands, rowsets are used to represent the results of row-returning queries. There are a number of other methods in OLE DB, such as the schema functions, that return information in the form of a rowset. A rowset encapsulates the general functionality of an ODBC statement in the executed state. There may be multiple rowsets associated with a single session or command.
The following objects are also defined in OLE DB. They provide recursive data source enumeration, enhanced transaction control, and extended error retrieval.
Enumerator objects list the data sources and enumerators visible to that enumerator. This is similar to the information provided by SQLDataSources, except that the information can be recursive.
In addition to supporting ITransactionLocal on the session, providers that support transactions can optionally support the creation of a transaction object. Transaction objects provide more advanced transaction functionality, such as the registration of transaction notifications.
In addition to the return codes and status information returned by each method in OLE DB, providers can optionally expose an OLE DB error object for extended error information, such as a description of the error or the appropriate SQLSTATE. This is similar to the information returned by SQLError or SQLGetDiagRec. (Note: Unless explicitly stated otherwise, ODBC 3.0 functions and function names are used throughout this article. For more information on mapping ODBC 1.x and 2.x to their ODBC 3.0 equivalents, see the ODBC 3.0 documentation.)