Dr. Dobb's Sourcebook July/August 1997
Software developers are a clever lot, so it should be no surprise that putting developers to work to devise multidatabase programming solutions produced a long list of products and application programming interfaces (APIs). Some of the solutions include gateways, SQL-92 CLI, ODBC, JDBC, Oracle Glue, IDAPI, Data Access Objects, Remote Data Objects, ActiveX Data Objects, and OLE DB. Many of those solutions operate as though a database management system is a monolithic entity. Microsoft instead created an API that operates at the component level to access data; OLE DB was designed to be an API that cooperates with transactions and other enterprise APIs built on the Component Object Model (COM). Microsoft's goal is to provide a single API for heterogeneous data access instead of using different APIs for different data stores. This means that, for example, to access SQL, mail, and directory data, a program would use only OLE DB, instead of using ODBC, MAPI, and LDAP, respectively.
Database technologies continue to evolve and DBMS architects have been creating new generation products by integrating objects with SQL. Some architects are moving in the direction of extending SQL engines by adding support for objects. Others have been extending object database management systems to support SQL and relational extensions. Some tools take the direction of putting everything -- relational data, text, video, images, and other types -- in the database. This strategy fuels the new generation of universal servers. Other products use content-specific servers for relational, text, video, graphics, and other data. These rely on middleware to present a unified programming interface to client programs. OLE DB falls into the category of middleware because it provides a uniform interface to heterogeneous data.
OLE DB includes components that support command processing, including SQL commands, although it isn't necessary to use a command to retrieve data. OLE DB programmers can use SQL-92 and connect to SQL databases by using ODBC drivers. OLE DB requires that you use a programming language that uses pointers and C-style bindings. ActiveX Data Objects (ADO) were developed as an object layer to operate over OLE DB for programmers using Visual Basic, Java, or scripting languages that lack pointers.
OLE was developed to provide shared component services based on COM. COM provides fundamental object sharing, but Microsoft also developed services such as in-place activation and structured storage that layer over COM. After implementing layers that provide services for desktop applications, Microsoft implemented distributed components (DCOM) and a variety of COM-based enterprise- and distributed-computing services. While Microsoft was developing its distributed computing technologies, the Internet caught everyone's attention, and OLE evolved into ActiveX.
Microsoft released OLE DB as part of its Active Platform SDK. The Active Platform is made up of ActiveX components, scripts, HTML documents, and technologies that collectively support distributed applications. These include DCOM, Active Directory, a distributed file system (DFS), Active Server, Transaction Server, messaging middleware, and other services. Although there is much discussion of OLE DB and ADO in the context of data access for Active Server, don't overlook the fact that either API operates on clients and servers.
OLE DB programs can act in concert with other programming interfaces that provide SQL access to data and transaction processing with local or distributed transactions. Developers writing transaction-processing programs can use OLE transaction interfaces, Microsoft Distributed Transaction Coordinator, and Microsoft Transaction Server. OLE DB includes a data provider for SQL data access using ODBC drivers.
When viewed from an OLE DB perspective, the roles of data-access software are that of consumer, data provider, or service provider. A consumer might typically be a program that performs some function while using a database, whereas a provider is analogous to the database driver that your program uses. A consumer, such as a spreadsheet application, might use a data provider to access an SQL database. Data providers support access to data stores for text, spreadsheet, directory, file system, ISAM, relational, and other data sources. A service provider uses data providers while providing a service, such as heterogeneous query processing or data replication. As part of a "DBMS as components" strategy, the query processor that provides access to Microsoft's SQL Server databases is evolving into a service provider that operates with heterogeneous data sources.
OLE DB consists of 32-bit COM interfaces that provide access to relational and nonrelational data. OLE DB supports the Unicode character set and the OLE DB SDK includes header files that provide C/C++ bindings. Although OLE DB provides a generic command processing capability, it works "right out of the box" with SQL. The built-in capability to support SQL includes an ODBC data provider (MSDASQL), queries that return logical and physical schema information, and support for Entry Level SQL-92.
COM-object programming experience and an understanding of dispatching, structured storage, COM persistence, and other commonly used techniques will be valuable when using component programming to solve data-access problems. OLE DB programmers also benefit from a knowledge of OLE event processing and error handling. OLE DB error handling extends the Automation error model. Notifications build on the OLE connection-point mechanism for event notifications.
Once you start programming with OLE DB, you'll expand your universe of interfaces by adding enumerator, data source object, session, command, transaction, rowset, index, error, and other COM objects. Because rowsets provide a tabular representation of metadata and query results, they are at the heart of the OLE DB programming model. OLE DB processes commands on a per-session basis and returns query results and catalog information as rowsets. OLE DB programs use a bind and fetch model that is familiar to SQL programmers. The OLE DB programming model is compatible with SQL standards. Catalog rowsets conform to the SQL-92 standard and the transaction interfaces used by OLE DB programs conform to standard COMMIT and ROLLBACK logic. OLE DB's notification scheme is analogous to triggers in SQL database. Using either technology, for example, you can define actions that occur based on events that change data. Notifications are attached to rowset instances, however, while triggers fire based on changes to database tables.
There are components and base-level interfaces that are fundamental to OLE DB programming. The model for these components is straightforward: Programs operate in the context of a session (Session) to retrieve rowsets (Rowset) from data sources (Data Source Objects). Figure 1 shows the relationship where a programmer, having instantiated a Data Source Object (DSO), creates a session and in turn opens a rowset.
Providers must support a DSO. A Session must support rowsets and expose a method for opening a rowset (DBSession::IOpenRowset). Rowsets must implement four interfaces: IRowset, IRowsetInfo, IAccessor, and IColumnsInfo. OLE DB also provides objects that can optionally be used in OLE DB programs. These include Index (rowsets with index information), Command (multiple per session), and Transaction objects. A program may use multiple commands per session and use local or distributed transactions. Programs also use handles to keep track of resources. OLE DB programmers use handles to maintain context for rows (HROW), accessors (HACCESSOR), result codes (HRESULT), and other information.
OLE DB also provides a fundamental object type called an "Enumerator," which answers the question, "What data sources are available?" Specifically, these components access the Registry to enumerate information about data sources, providers, or other enumerators.
OLE DB supports typical SQL types and uses standard Windows and OLE data types. The SQL types include timestamp, floats, doubles, numerics, various size integers, and large objects (CLOBs and BLOBS). OLE DB supports the storage and retrieval of OLE objects that expose a persistent stream or storage interface -- an IPersistStream, IPersistStreamInit, or IPersistStorage interface. OLE DB 1.x does not support user-defined, abstract data types. (OLE DB defines almost two dozen SQL types, but not all providers support all types.)
OLE DB programs use bind and fetch logic. First, you set up bindings that associate columns in the data store with buffers or memory variables. Then, you fetch data into those buffers. Programs use type indicators to describe types when binding. A type indicator is a variable of type DBTYPE. DBTYPE is a WORD with values that are enumerated types in DBTYPEENUM. OLE DB programs use type indicators to describe the type of columns or parameters, to indicate the types stored in VARIANT structures, and to describe bound data values.
OLE DB belongs to the genre of multidatabase APIs. Because one of the reasons to use such an API is its ability to connect to a variety of databases, a multidatabase program often needs to get run-time information about schemas and database capabilities. This permits the program to use logic to adapt to the database environment, a process I've frequently described as "adaptive programming." Some programs do not know in advance what schema, databases, tables, or columns they will access because they simply respond to a user's ad hoc query commands. In that instance, a program must also obtain rowset and column metadata. OLE DB interfaces used for adaptive programming include IEnumerator, IDBInfo, IRowsetInfo, and IColumnsInfo. A program uses IEnumerator for provider and enumerator information and IDBInfo for keyword and literal information for a data provider. IColumnsInfo provides information about columns of a prepared command or rowset. All rowsets implement IRowsetInfo to provide information about rowsets.
Schema information is available to consumers on a per session basis (IDBSession). OLE DB programs process schema information in a manner similar to ODBC programs -- that is, schema information is returned in the form of rowsets.
IDBSchemaRowset is an optional rowset interface that provides schema information. IDBSchemaRowset is useful for consumers that don't know the structure of the data on which they will operate. IIndexDefinition includes methods for creating and dropping indexes, and ITableDefinition includes methods for manipulating tables (create, drop, or alter tables).
Rowset operations are at the heart of OLE DB programming. Rowset objects expose over 20 interfaces for manipulating tabular data. Consumers uses IRowset interfaces to insert, change, and update rows. They also use rowset interfaces for operations such as scrolling, locking rows, and copying rows.
To read and write data, consumers use an Accessor in conjunction with bind and fetch logic. An Accessor is a group of bindings that provide services such as unpacking and type coercion. Figuratively, a program tells the OLE DB components, "Here's the data I want, the location where I want in, and the format in which I'd like it."
To do this, a program performs the following steps:
The rowset's GetData method uses Accessor bindings when deciding what data to return and in what format. Accessors provide type coercion before transferring data to the consumer. Before doing the transfer, GetData converts data in its cache to the destination type specified in the binding. (To force type coercion when setting up bindings, a consumer can use the wType element of the DBBINDING structure.)
Although OLE DB supports SQL processing, its command architecture is generic. For example, one can expect that OLE DB will ultimately expose interfaces that permit a consumer or service provider to use parse trees, the product of parsing a command. Simple providers may not support command processing, but providers (such as the ODBC provider) include Command COM objects and expose ICommand* interfaces. Command COM objects support text, properties, column information, and accessor interfaces. Consumers enable command processing by using DBSession::IDBCreateCommand to create a Command object. When a provider supports commands, the consumer doesn't open rowsets in the manner depicted by Figure 1. Instead the program uses the Execute method off of the ICommand interface to instantiate the rowset. Figure 2 illustrates rowset creation in a command-processing context.
OLE DB supports more than one execution model. You can develop consumers that use single execution, ad hoc queries. Consumers can also use parameters and a prepare and execute model. The prepare and execute model permits a DBMS to optimize a command and generate an access plan for its execution. To prepare a query command, you call ICommandPrepare::Prepare. Consumers that execute commands with parameters, such as parameterized SQL queries, use ICommandWithParameters to describe parameters or get parameter information. After preparing, use ICommandWithParameters::SetParameterInfo to specify the command's parameter types to the provider. You also create an accessor for a buffer and put parameter values in the buffer. Then you pass the buffer pointer and accessor handle to ICommand::Execute.
After preparing a command you can also use methods to get a list of parameter information (GetParameterInfo), or get parameter mappings by ordinal position (MapParameterNames). To discard an access plan, use ICommandPrepare::Unprepare.
Rowsets include a callback interface, IRowsetNotify, that permits you to associate rowset notifications with logic in your consumer application. In simple terms, a change to a rowset fires an event that causes your program to take action. Notifications provide the ability to synchronize objects attached to the same instance of a rowset, not to underlying shared tables. To support notifications, a rowset uses OLE connection points and supports IConnectionPointContainer.
Providers support schemas and catalogs consisting of more than one schema. Consumers retrieve schema information by using IDBSchemaRowset. OLE DB includes a defined set of queries (rowsets) with schema information. The information schema is part of all SQL-92 catalogs. These well-known queries include the logical information and physical information schema. The logical information schema includes rowsets of logical metadata that are, with two exceptions, a superset of the INFORMATION_SCHEMA views defined by SQL-92. It includes FOREIGN_KEYS, ASSERTIONS, VIEWS, COLUMN_PRIVILEGES, SQL_LANGUAGES, TABLE_CONSTRAINTS, CHECK_CONSTRAINTS, PROCEDURES, and other metadata. The COLUMNS and DOMAINS rowsets are exceptions where OLE DB provides a subset of the information defined by the SQL-92 standard. Providers that implement IDBSchemaRowset must support TABLES and COLUMNS schema rowsets. Other logical information schema rowsets (see Table 1) are optional.
The Physical Information Schema includes rowsets that identify physical metadata. These include CATALOGS, INDEXES, PROVIDER_TYPES, and STATISTICS. Providers that implement IDBSchemaRowset must support the PROVIDER_ TYPES schema rowset. Other schema rowsets are optional.
Every call from your consumer to an OLE DB interface produces a result code (HRESULT). Each time your program uses an interface, it should test status using HRESULT. When a call produces an error, a program should use logic that is capable of handling multiple errors. An OLE DB program can encounter errors at several levels, including providers, network libraries, and the DBMS. Because of the potential for multiple errors, a program should include logic to inform users of all of the errors received for a call. Displaying all error messages is important because the native error messages emitted by the DBMS are often the most helpful in correcting a problem.
OLE DB error objects are an extension of OLE Automation error objects. However, the OLE DB ErrorObject can reference multiple error records using IErrorInfo to return error messages, component names, return code, the ID (GUID) of the interface returning the error, and a reference to the appropriate help file. Consumers call GetErrorInfo in the OLE Automation DLL to get the IErrorInfo interface pointer. OLE DB also supports optional custom error objects (CustomErrorObject).
Because some methods operate on multiple items or arrays, OLE DB programs often work with an array of row status values that specify the errors and warnings encountered when processing rows. A data consumer can use the DBROWSTATUS value array to monitor status for specific rows. Status values that include "_S_" indicate success or a warning when operating on a row. Examples includes DBROWSTATUS_S_OK and DBROWSTATUS_S_MULTIPLECHANGES. Status values that include "_E_" indicate a failure or error condition resulted from operations on a row.
Methods that operate on multiple items can flag individual, item-level errors. When a method encounters an item-level error, it sets a flag, continues processing and returns a status code that indicates an item-level error. A status of DB_S_ERRORSOCCURRED indicates success in processing one or more items whereas DB_E_ERRORSOCCURRED indicates the failure to process any of the items successfully. Example error status values that a method might return include DBROWSTATUS_E_ DELETED and DBROWSTATUS_E_CONCURRENCYVIOLATION. (Your OLE DB programs can use provider-specific error information. OLE DB Error objects can use IErrorLookup to get provider-specific information that includes a provider's error code, error messages, the error source, context ID and help file path.)
OLE DB supports transaction processing and four isolation levels that conform to the SQL standards. The isolation levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Transaction processing programs use ITransaction::Commit to commit database changes, and ITransaction::Abort to roll back changes. In addition to an interface for local transactions (ITransactionLocal), OLE DB programs can run in an environment that supports distributed transactions using Microsoft Transaction Server and Distributed Transaction Coordinator.
To permit Visual Basic, Java, scripting tools, and other languages without pointers to use OLE DB providers, Microsoft bundles ADO with the OLE DB SDK. ADO provides language-neutral objects that layer over OLE DB. ADO is a much simpler object layer than Data Access Objects (DAO) and Remote Data Objects (RDO), the extant object layers from Microsoft. It consists of independent objects that include Connection, Command, Recordset, Field, Parameter, and Error objects.
DDJ