OLE DB Minimum Levels of Consumer and Provider Functionality
Microsoft Corporation
Summary: OLE DB defines a comprehensive set of interfaces for accessing a diverse range of data types located in a variety of relational and nonrelational data stores. OLE DB data providers need to expose only the minimum number of interfaces that reflect the natural functionality of their data stores. This makes OLE DB providers much easier to write. Above that minimal level, common OLE DB service components provide interoperability through generic implementations of extensions such as scrolling or predicate-based positioning. This means that applications consuming OLE DB, directly or through ADO, can achieve a richer level of functionality because the OLE DB service components offer extended functionality that is not necessarily provided by the data store.
Contents
Introduction: OLE DB as a Component Technology Consumers Providers
Introduction: OLE DB as a Component Technology
The purpose of this article is to define the minimal levels of functionality that an OLE DB consumer should expect from a general-purpose OLE DB provider. It describes two levels of functionality. One is the base level, which represents the most basic level of functionality that will enable very simple data transfer between providers and consumers. The other level is the extended level, which represents a higher, value-added level of functionality that most general-purpose providers will want to provide for most common data consumer applications.
The key to making this work is to separate the most essential functions from the more sophisticated functions. This is possible because OLE DB is designed as a component-based technology, building on the framework established by Microsoft's Component Object Model (COM). In COM, an interface is used to collect a group of functions. COM clearly defines the expected behavior of components when requested interfaces are not implemented. In COM, only the IUnknown interface must be implemented. All other interfaces are optional.
This design makes a few very important features possible. Components can be designed to interact correctly at different levels of functionality without limiting new features or requiring an all-or-nothing implementation. Additionally, multiple components can be combined in a seamless fashion, which reduces the work needed to achieve an advanced level of functionality. A component, by supporting aggregration, can appear to inherit interfaces from another component without actually knowing anything about that other component.
As COM components, OLE DB components also allow optional levels of functionality. The OLE DB Programmer's Reference describes all the possible interfaces, and the resulting levels of functionality, that OLE DB developers can use. However, only some of these interfaces are needed for the most basic functionality, and this article identifies those interfaces. Any other interfaces defined by the OLE DB Programmer's Reference are completely optional, and it is the developer's discretion whether to implement them.
Consumers and Providers
OLE DB describes the relationship between two components by labeling one component a data provider and the other a consumer. Even though a single component can be both a data provider and a consumer, what is important is the relationship between two components, and in such cases one component is always acting in the role of the data provider and the other as the consumer.
An OLE DB provider exposes OLE DB interfaces over some type of data. OLE DB providers include everything from a full SQL DBMS to a text file or data stream. Therefore, because data providers have different functionality, it's important not to limit that functionality. However, not all providers should be expected to expose simple tabular data to implement a full-blown query engine. A general-purpose provider exhibits a minimal level of functionality that most consumers can use, regardless of the source of data.
Implementing this minimum level does not limit a provider's ability to expose interfaces that are specific to the type of data it can access. All OLE DB providers, regardless of their level of functionality, have the ability to expose any OLE DB object or interface that the developer thinks is important to the design of the provider.
Note
Custom COM interfaces that are not part of the OLE DB provider specification should not be implemented. Instead, any features that are unique to a provider should be exposed through OLE DB properties and objects. An OLE DB provider that exposes custom COM interfaces will cause it to be tightly coupled to any consumer that uses those interfaces. A design using proprietary COM interfaces contradicts one of the primary goals of OLE DB: to keep the methods used to access data consistent across all providers. To keep training costs and code complexity low, developers writing OLE DB consumers prefer to work with standardized data access techniques.
Consumers
Developers writing OLE DB consumers can choose their level of interoperability with OLE DB providers. Consumers may be written to consume a specific provider, in which case they are designed to be aware of the functionality specific to that provider, such as its ability to update data or its dynamic properties. Consumers may also be written to consume generic providers. This article focuses on the features these generic providers should have. From the consumer's perspective, there are two levels of functionality for generic OLE DB providers: base, and extended. To consume generic providers, the consumer can choose from a range of strategies, such as the following:
- Consume a base level of functionality and work with all OLE DB providers.
- Consume an extended level of functionality and query the provider for support of extended functionality.
- Consume an extended level of functionality and invoke service components to implement missing functionality.
Generic consumers must be prepared for providers that don't support the extended interfaces. Consumers can handle such providers in the following ways:
- The consumer can invoke common services to implement extended functionality, where such service components are available.
- The consumer can implement extra code within the application to compensate for missing functionality.
- The consumer can reduce the functionality available to the user based on missing functionality in the provider.
- The consumer can return an error indicating that the provider is not capable of supporting required functionality.
The following section describes the entire base level of functionality that all generic consumers can expect all generic OLE DB providers to support. In some cases, OLE DB service components implement the functionality rather than the OLE DB providers.
Required Consumer Objects and Interfaces
The following objects and their respective interfaces are always required. Some interfaces and the properties they support are available only if the OLE DB provider supports updating data. If the OLE DB provider does not support these optional interfaces, the provider should be considered a read-only provider.
Root Enumerator Object
The following interfaces for this object are always supported by common components in OLE DB. They are never implemented directly by providers.
IDBInitialize
IParseDisplayName
ISourcesRowset
Data Links Object
The following required interfaces for this object are always supported by common components in OLE DB. They are never implemented directly by providers.
IDataInitialize
IPromptInitialize
Row Position Object
The following required interface for this object is always supported by common components in OLE DB. It is never implemented directly by providers.
IRowPosition
Data Source Object
The following interfaces are required for this object:
IDBCreateSession
IDBInitialize
IDBProperties (see "Required Consumer Properties," below)
IPersist
Session Object
IGetDataSource
IOpenRowset
ISessionProperties
Rowset Object
The following interfaces are required for this object:
IAccessor
IColumnsInfo
IConvertType
IRowset
IRowsetChange (required for updatable providers only)
IRowsetFind
IRowsetIdentity
IRowsetInfo (see "Required Consumer Properties," below)
IRowsetLocate
IRowsetScroll
Required Consumer Properties
Some objects support interfaces that expose dynamic properties for the object. This section lists all the properties that a consumer can expect all OLE DB providers to support at the base level of functionality.
Some properties are available only if the OLE DB provider supports updating data. If the OLE DB provider does not support these optional properties, it should be considered a read-only provider.
IDBProperties Interface
This interface must support the following property:
DBPROP_INIT_PROMPT
The DBPROP_INIT_PROMPT property has a special condition. Support for DBPROP_INIT_PROMPT is required only if any initialization properties are required for connecting to the provider. If calling IDBInitialize::Initialize can succeed on a freshly created data source object, the provider does not need to support this property.
A limited exception to this requirement may be made in specific circumstances. Some providers may require initialization properties for IDBInitialize::Initialize to succeed but for some specific reason, such as memory or user interface requirements, cannot prompt the user. In such a case, the correct action is for the provider to support only the DBPROMPT_NOPROMPT value for the DBPROP_INIT_PROMPT property. This behavior does not bring the provider to the minimum level of functionality and should be used only for exceptional cases. The documentation for the provider should clearly document this exception as well as the actual behavior of the provider when given a value other than DBPROMPT_NOPROMPT. The suggested behavior is that setting DBPROP_INIT_PROMPT to values other than DBPROMPT_NOPROMPT should cause either a DB_S_ERRORSOCCURRED or a DB_E_ERRORSOCCURRED result, with the DBPROPSTATUS value for the DBPROP_INIT_PROMPT property set to DBPROPSTATUS_NOTSUPPORTED.
IRowsetInfo Interface
This interface must support the following properties:
DBPROP_CANFETCHBACKWARDS
DBPROP_CANHOLDROWS
DBPROP_CANSCROLLBACKWARDS
DBPROP_OWNINSERT (for updatable providers)
DBPROP_OWNUPDATEDELETE (for updatable providers)
DBPROP_REMOVEDELETED (for updatable providers)
Providers
Developers of general-purpose OLE DB providers must offer consumers at least the base level of functionality. There are two ways to achieve this. One way is to write the provider to a minimum level and rely upon OLE DB service components to complete the functionality to the base level. The other way is to write the provider to implement the base level of functionality entirely and not use the OLE DB service components.
The levels discussed in the sections that follow are minimal levels. Additional interfaces can always be implemented wherever the developer of the OLE DB provider sees fit. The extended level of functionality represents the level that most common general-purpose OLE DB providers will have.
Data providers written in Microsoft® Visual Basic®, Microsoft Visual J++®, Microsoft Visual C++®, or C, using the OLE DB Simple Provider (OSP) Toolkit, automatically support all of the base level of functionality and more.
General Behavior
All providers must be apartment, rental, or free threaded and must support aggregation of the data source, session, and rowset objects. If applicable, providers must support the aggregation of the command and view objects.
Providers must support data conversions to the types returned in IColumnsInfo::GetColumnsInfo or IColumnsRowset::GetColumnsRowset for rowset columns, and ICommandWithParameters::GetParameterInfo for parameters, as well as DBTYPE_WSTR for all column or parameter values other than those described as objects. Providers that support binding to objects as IStream or ILockBytes must also support binding to those columns as ISequentialStream.
Minimum Level Providers
It is important for provider writers to implement the full set of interfaces that apply to their particular type of data. At a minimum, the provider must implement the objects, interfaces, and behavior listed in this section to be considered a generic OLE DB provider. Providers implementing the minimum provider functionality can rely on OLE DB service components available in the Microsoft Data Access Components (MDAC) SDK to implement the base consumer functionality, as shown in Figure 1.
Figure 1. Base application using service components to talk to a minimum provider
.gif "ms811067.dawp01(en-us,MSDN.10).gif")
Required Provider Objects and Interfaces
OLE DB providers at the minimum level of functionality must support the following objects and their interfaces. Some interfaces are required only for OLE DB providers that support updating data. Providers that do not support these interfaces are considered read-only providers.
Note
Supporting an interface means supporting all methods within that interface. No methods return E_NOTIMPL.
Data Source Object
The following interfaces are required for this object:
IDBCreateSession
IDBInitialize
IDBProperties
IPersist
Session Object
The following interfaces are required for this object:
IGetDataSource
IOpenRowset
ISessionProperties
Rowset Object
The following interfaces are required for this object:
IAccessor
IColumnsInfo
IConnectionPointContainer for IRowsetNotify (for updatable providers)
IConvertType(for updatable providers)
IRowset
IRowsetChange (for updatable providers)
IRowsetIdentity
IRowsetInfo (see "Required Provider Properties," below)
Supporting IConnectionPointContainer for IRowsetNotify is not strictly required for OLE DB providers to satisfy the minimum requirements for an updatable provider. However, updatable providers are strongly encouraged to expose this connection point because some consumers will be forced to treat providers as read-only if they do not support rowset notifications by exposing the connection point for IRowsetNotify.
Required Provider Properties
Some objects support interfaces that expose dynamic properties for the object. This section lists all the properties that all OLE DB providers must support at the minimum level of functionality.
Some properties are required only if the OLE DB provider supports updating data. If the OLE DB provider does not support these optional properties, the provider will be considered a read-only provider.
IRowsetInfo Interface
This interface must support the following properties:
DBPROP_CANHOLDROWS
DBPROP_DATASOURCEREADONLY
DBPROP_IACCESSOR
DBPROP_ICOLUMNSINFO
DBPROP_ICONVERTTYPE
DBPROP_IROWSET
DBPROP_IROWSETIDENTITY
DBPROP_IROWSETINFO
DBPROP_OWNINSERT (for updatable providers)
DBPROP_OWNUPDATEDELETE (for updatable providers)
DBPROP_REMOVEDELETED (for updatable providers)
Supporting the required rowset properties means that setting any combination of the required properties must yield a rowset that reflects at least those properties. It does not mean that those properties are always true for any rowset if the user has not requested the property.
Base Level Providers
Providers that implement the full set of base interfaces can be consumed by general consumers without the support of additional service components, as shown in Figure 2.
Figure 2. Base application talking directly to a base provider
.gif "ms811067.dawp02(en-us,MSDN.10).gif")
Required Objects and Interfaces for Base Level Provider Implementation
OLE DB providers at the base level of functionality must support the following objects and their interfaces. Some interfaces are required only for OLE DB providers that support updating data. Providers that do not support these interfaces are considered read-only providers.
Supporting an interface means supporting all methods within that interface. No methods return E_NOTIMPL.
Rowset Object
The following interfaces are required for this object:
IRowsetFind
IRowsetLocate
IRowsetScroll
Required Properties for Base Level Provider Implementation
Some objects support interfaces that expose dynamic properties for the object. This section lists all the properties that all OLE DB providers must support at the base level of functionality.
Some properties are required only if the OLE DB provider supports updating data. If the OLE DB provider does not support these optional properties, the provider will be considered a read-only provider.
IRowsetInfo Interface
This interface must support the following properties:
DBPROP_CANFETCHBACKWARDS
DBPROP_CANSCROLLBACKWARDS
Providers that support the property DBPROP_CANSCROLLBACKWARDS must also support the property DBPROP_CANFETCHBACKWARDS.
Extended Level Providers
General-purpose providers may support additional functionality. It is advantageous that providers support as many extended interfaces as apply to their particular type of data.
In addition to common interface extensions, providers may expose specialized interfaces for the following sets of extended functionality:
- Rowset processing—Exposed by providers that support native filter or sort capabilities.
- Commands—Exposed by providers that can execute more sophisticated queries or statements.
- Transactions—Exposed by providers that can support transactional capabilities.
- Index navigation—Exposed by providers built on indexed data to expose index functionality to service components such as query processors. OLE DB consumers other than service components generally do not directly consume these interfaces.
Required Objects and Interfaces for Extended Level Provider Implementation
OLE DB providers at the extended level of functionality must support the following objects and their interfaces. Some interfaces are required only for OLE DB providers that support updating data. Providers that do not support these interfaces are considered read-only providers.
Note
Supporting an interface means supporting all methods within that interface. No methods return E_NOTIMPL.
Data Source Object
The following interfaces are required for this object:
IConnectionPointContainer for IDBAsynchNotify
IDBAsynchStatus
IDataSourceAdmin
IDBInfo (extended functionality that may be exposed by providers that support rowset processing; see "Required Properties for Extended Level Provider Implementation," below)
IPersistFile
ISupportErrorInfo
Session Object
The following interfaces are required for this object:
IAlterIndex
IAlterTable
IDBCreateCommand (extended functionality that may be exposed by providers that support commands)
IDBSchemaRowset
IDBView (extended functionality that may be exposed by providers that support rowset processing)
IIndexDefinition
ISupportErrorInfo
ITableDefinition
ITransactionJoin (for transacted providers)
ITransactionLocal (for transacted providers)
ITransactionObject (for transacted providers)
View Object
The view object, available through the IDBView interface, represents extended functionality that may be exposed by providers that support rowset processing. The following interfaces are required for this object:
IAccessor
IColumnsInfo
ISupportErrorInfo
IViewChapter
IViewFilter
IViewRowset
IViewSort
Command Object
The command object, available through the IDBCreateCommand interface, represents extended functionality that may be exposed by providers that support commands. The following interfaces are required for this object:
IAccessor
IColumnsInfo
IColumnsRowset
ICommand
ICommandPersist
ICommandPrepare
ICommandProperties (see "Required Properties for Extended Level Provider Implementation," below)
ICommandText
ICommandWithParameters
IConvertType
ISupportErrorInfo
Custom Error Object
The following interface is required for this object:
ISQLErrorInfo
Multiple Results Object
The following interface is required for this object:
IMultipleResults
Rowset Object
The following interfaces are required for this object:
IChapteredRowset (extended functionality that may be exposed by providers that support rowset processing)
IColumnsRowset
IConnectionPointContainer for IDBAsynchNotify
IConnectionPointContainer for IRowsetNotify
IDBAsynchStatus
IRowsetChapterMember
IRowsetIdentity
IRowsetIndex (extended functionality that may be exposed by providers that support index navigation)
IRowsetLocate
IRowsetResynch (for updatable providers)
IRowsetUpdate (for updatable providers)
IRowsetView (extended functionality that may be exposed by providers that support rowset processing)
ISupportErrorInfo
BLOB Support
The following interfaces are required for this object (applies to rowset objects):
ILockBytes
ISequentialStream
IStorage
IStream
Required Properties for Extended Level Provider Implementation
Some objects support interfaces that expose dynamic properties for the object. This section lists all the properties that all OLE DB providers must support at the extended level of functionality.
Some properties are required only if the OLE DB provider supports updating data. If the OLE DB provider does not support these optional properties, the provider will be considered a read-only provider.
ICommandProperties Interface
This interface must support the following property:
DBPROP_MAXTABLESINSELECT > 1
IRowsetInfo Interface
This interface must support the following properties:
DBPROP_ABORTPRESERVE (for transacted providers)
DBPROP_BOOKMARKSKIPPED
DBPROP_CHANGEINSERTEDROWS (for updatable providers)
DBPROP_COMMITPRESERVE (for transacted providers)
DBPROP_IMMOBILEROWS (for updatable providers)
DBPROP_LITERALBOOKMARKS
DBPROP_LITERALIDENTITY
DBPROP_ORDEREDBOOKMARKS
DBPROP_OTHERINSERT
DBPROP_OTHERUPDATEDELETE
DBPROP_RETURNPENDINGROWS (for updatable providers)
DBPROP_STRONGIDENTITY
BLOB Support
BLOBs must support the following properties (applies to IRowset interface on rowset objects):
DBPROP_BLOCKING STORAGEOBJECTS = FALSE
DBPROP_MULTIPLE STORAGEOBJECTS
DBPROP_OLEOBJECTS
DBPROP_STRUCTURED STORAGE