OLE DB for the ODBC Programmer
Michael Pizzo and Jeff Cochran
Microsoft Corporation
March 1997
Updated May 5, 2010
Introduction
This paper presents an introduction to OLE DB programming using the Microsoft® OLE DB Software Developer's Kit (SDK), which can be downloaded from https://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.
OLE and OLE DB
Some of the key OLE concepts that you should be aware of include:
Interface factoring
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.
Interface negotiation
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 counting
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.
Memory management
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.
Unicode
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.
Components of OLE DB
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.
OLE DB Objects
The following illustration shows the core object model of OLE DB.
.gif "ms810892.ole4odbc_1(en-us,MSDN.10).gif")
Data Source Object
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.
Session Object
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.
Command 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.
Rowset Object
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.
Other OLE DB Objects
The following objects are also defined in OLE DB. They provide recursive data source enumeration, enhanced transaction control, and extended error retrieval.
Enumerator Object
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.
Transaction Object
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.
Error Object
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.)
Basic OLE DB
This section describes the basic concepts and procedures defined in OLE DB, such as initializing the environment, locating and connecting to a data source, executing a command, and retrieving data from a rowset.
Application Flow
The application flow in OLE DB is similar to the application flow in ODBC. In both cases, the application:
- Initializes the environment.
- Connects to a data source.
- Creates and executes a command.
- Processes results, if any.
- Cleans up.
A typical OLE DB consumer may look like the following code example. For the complete source code listing, see Appendix A. The individual routines in this sample are described in more detail in the following sections. The flow of control is as follows:
Initializes OLE.
Connects to a data source object.
Creates and executes a command.
Processes the results.
Releases objects and uninitializes OLE.
/******************************************************************** * General OLE DB application main() ********************************************************************/ #define UNICODE #define _UNICODE #define DBINITCONSTANTS // Initialize OLE constants... #define INITGUID // ...once in each app. #include <windows.h> #include <oledb.h> // OLE DB include files #include <oledberr.h> #include <msdaguid.h> // ODBC provider include files #include <msdasql.h> // Global task memory allocator IMalloc* g_pIMalloc = NULL; int main() { IDBInitialize* pIDBInitialize = NULL; IRowset* pIRowset = NULL; // Init OLE and set up the DLLs; see "Initializing the // Environment." CoInitialize(NULL); // Get the task memory allocator. if (FAILED(CoGetMalloc(MEMCTX_TASK, &g_pIMalloc))) goto EXIT; // Connect to the data source; see "Initializing a Data Source." if (FAILED(myInitDSO(&pIDBInitialize))) goto EXIT; // Get a session, set and execute a command; see "Getting a // Session and Executing a Command." if (FAILED(myCommand(pIDBInitialize, &pIRowset))) goto EXIT; // Retrieve data from rowset; see "OLE DB Rowsets." myGetData(pIRowset); EXIT: // Clean up and disconnect. if (pIRowset != NULL) pIRowset->Release(); if (pIDBInitialize != NULL) { if (FAILED(pIDBInitialize->Uninitialize())) { // Uninitialize is not required, but it will fail if an // interface has not been released. We can use it for // debugging. DumpError("Someone forgot to release something!"); } pIDBInitialize->Release(); } if (g_pIMalloc != NULL) g_pIMalloc->Release(); CoUninitialize(); return (0); }
Initializing the Environment
In ODBC, the application generally dynamically links to the ODBC Driver Manager (Odbc32.dll). The Driver Manager loads and directs calls to the appropriate driver.
In OLE DB, initialization of the environment is achieved by a call to OleInitialize, which initializes the OLE library. This is shown in the preceding code example. After the OLE library is initialized, the proper data provider is loaded by the system according to its class ID, and calls are made directly to the provider.
Initializing a Data Source
The data source object exposes the IDBInitialize and IDBProperties interfaces that contain the methods to connect to a data source. The authentication information such as user ID, password, and the name of the data source are specified as properties of the data source object by calling IDBProperties::SetProperties. The method IDBInitialize::Initialize uses the specified properties to connect to the data source.
In ODBC, establishing a connection involves the following steps:
- Call SQLAllocHandle to allocate a connection handle.
- Build a connection string containing keywords for authentication information, such as user ID, password, and the name of the data source.
- Call SQLDriverConnect, providing the connection string and other information, such as level of prompting and the application's window handle where appropriate.
In OLE DB, the equivalent functionality is achieved by the following steps:
- Build an array of property structures describing the authentication information, such as user ID, password, and the name of the data source, as well as the level of prompting and the application's window handle when appropriate.
- Call IDBProperties::SetProperties to set initialization properties. (For more information about properties, see "Getting and Setting Properties," below.)
- Call IDBInitialize::Initialize to initialize the data source object.
As can be seen, the model in OLE DB is similar to the model in ODBC. The primary differences are:
- OLE DB uses a well-defined set of property structures to represent the initialization and connection information, rather than building/parsing keywords within a connection string.
- The set of property structures are used for all of the initialization/connection information.
- The set of available initialization properties can be queried through IDBProperties::GetPropertyInfo.
- Rather than returning a connection string, the user can simply request the current set of initialization property values.
- Setting the initialization properties is separate from actually initializing (connecting) the data source. This allows the consumer to set, persist, and retrieve connection information without initializing (connecting) the data source.
The following code example shows a routine that initializes a data source object. The general flow of control is:
Create an instance of the OLE DB Provider (in this case, the ODBC Provider).
Set the initialization properties.
Call Initialize to initialize the data source object, using the supplied properties.
/******************************************************************** * Initialize the data source. ********************************************************************/ HRESULT myInitDSO ( IDBInitialize** ppIDBInitialize // [out] ) { // Create an instance of the MSDASQL (ODBC) provider CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void**)ppIDBInitialize); if (*ppIDBInitialize == NULL) { return (E_FAIL); } // See "Getting and Setting Properties." if (FAILED(mySetInitProps(*ppIDBInitialize))) { return (E_FAIL); } if (FAILED((*ppIDBInitialize)->Initialize())) { DumpError("IDBInitialze->Initialize failed."); return (E_FAIL); } return (NOERROR); }
Getting and Setting Properties
Properties are used in OLE DB to specify options, such as initialization information on the data source object or supported properties of a rowset, as well as to discover properties of certain objects, such as the updatability of a rowset.
Properties in OLE DB are similar to the environment, connection, and statement attributes in ODBC, with the following exceptions:
- In OLE DB, the provider can be queried for a list of all supported properties.
- In OLE DB, properties are grouped into "Property Groups." Property groups are identified by a GUID. This allows third parties to define properties within their own property group, rather than trying to reserve ranges within a single set of attribute values.
- Instead of setting and retrieving properties individually, multiple properties can be set or retrieved from multiple groups in a single call. This is done by building an array of property sets, where each property set contains an array of property structures from a single property group.
OLE DB consumers can:
- Enumerate, set, and retrieve properties on a data source object using IDBProperties.
- Set or retrieve properties on a session using ISessionProperties.
- Set or retrieve properties on a command using ICommandProperties.
- Retrieve properties and information about a rowset using IRowsetInfo.
The following table shows the property groups in OLE DB and their GUIDs.
| Property group | Property group identifier (GUID) |
|---|---|
| Column | DBPROPFLAGS_COLUMN |
| Data Source | DBPROPFLAGS_DATASOURCE |
| Data Source Creation | DBPROPFLAGS_DATASOURCECREATE |
| Data Source Information | DBPROPFLAGS_DATASOURCEINFO |
| Data Source Initialization | DBPROPFLAGS_DBINIT |
| Index | DBPROPFLAGS_INDEX |
| Rowset | DBPROPFLAGS_ROWSET |
| Session | DBPROPFLAGS_SESSION |
| Table | DBPROPFLAGS_TABLE |
The following structure contains an array of values of properties from a single property set:
typedef struct tagDBPROPSET
{
DBPROP __RPC_FAR* rgProperties; // Pointer to an array of
// DBPROP structures.
ULONG cProperties; // Count of properties
// (DBPROPS) in the array.
GUID guidPropertySet; // A GUID that identifies the
// property set to which the
// properties belong.
} DBPROPSET;
The following structure contains information about a single property:
typedef struct tagDBPROP
{
DBPROPID dwPropertyID; // ID of property within a
// property set.
DBPROPOPTIONS dwOptions; // Property is required?
// Optional?
DBPROPSTATUS dwStatus; // Status returned by the
// provider indicating success
// or failure in setting or
// getting the property.
// Enumerated values are:
// DBPROPSTATUS_OK
// DBPROPSTATUS_NOTSUPPORTED
// DBPROPSTATUS_BADVALUE
// DBPROPSTATUS_BADOPTION
// DBPROPSTATUS_BADCOLUMN
// DBPROPSTATUS_NOTALLSETTABLE
// DBPROPSTATUS_NOTSET
// DBPROPSTATUS_NOTSETTABLE
// DBPROPSTATUS_CONFLICTING
DBID colid; // Optional, ordinal column
// property applies to. If the
// property applies to all
// columns, colid should be set
// to DB_NULLID.
VARIANT vValue; // Value of the property.
} DBPROP;
The following code example shows how an application sets initialization properties on a data source object. The code sets four properties within a single property group. The general flow of control is:
- Allocate an array of property structures.
- Allocate an array of a single property set.
- Initialize common property elements for the properties.
- Fill in the following properties:
- Level of desired prompting (similar to DriverCompletion argument in SQLDriverConnect)
- Data source name (similar to DSN= element of the ODBC connection string)
- User name (similar to the UID= element of the ODBC connection string)
- Password (similar to the PWD= element of the ODBC connection string)
- Set the property set to the array of properties and specify that the properties are from the initialization property group.
- Get the IDBProperties interface.
- Call SetProperties on the interface.
/********************************************************************
* Set initialization properties on a data source.
********************************************************************/
HRESULT mySetInitProps
(
IDBInitialize* pIDBInitialize // [in]
)
{
const ULONG nProps = 4;
IDBProperties* pIDBProperties;
DBPROP InitProperties[nProps];
DBPROPSET rgInitPropSet;
HRESULT hr;
// Initialize common property options.
for (ULONG i = 0; i < nProps; i++ )
{
VariantInit(&InitProperties[i].vValue);
InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[i].colid = DB_NULLID;
}
// Level of prompting that will be done to complete the
// connection process
InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
InitProperties[0].vValue.vt = VT_I2;
InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;
// Data source name--see the sample source code included with the
// OLE DB SDK.
InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal =
SysAllocString(OLESTR("OLE_DB_NWind_Jet"));
// User ID
InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR(""));
// Password
InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
InitProperties[3].vValue.vt = VT_BSTR;
InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR(""));
rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet.cProperties = nProps;
rgInitPropSet.rgProperties = InitProperties;
// Set initialization properties.
pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)
&pIDBProperties);
hr = pIDBProperties->SetProperties(1, &rgInitPropSet);
SysFreeString(InitProperties[1].vValue.bstrVal);
SysFreeString(InitProperties[2].vValue.bstrVal);
SysFreeString(InitProperties[3].vValue.bstrVal);
pIDBProperties->Release();
if (FAILED(hr))
{
DumpError("Set properties failed.");
}
return (hr);
}
Getting a Session and Executing a Command
The data source object exposes the IDBCreateSession interface through which a session object can be created. A session defines transaction scope and acts mainly as a command generator by supporting the IDBCreateCommand interface. Commands contain a DML query or a DDL definition. The execution of a row-returning command yields a rowset object.
The session object in OLE DB is similar to the ODBC connection handle. However, the connection handle in ODBC is used for establishing connections as well as scoping transactions, so the application must allocate and connect a separate connection handle for each concurrent transaction. In OLE DB you can have multiple session objects on one initialized data source object, which means you can have multiple concurrent transactions without having to make multiple connections (where necessary, the provider makes additional connections using the connection information provided in the initialization of the data source object).
The command object in OLE DB is similar to the ODBC statement handle in the unexecuted state. Like the ODBC connection handle, which can have several statement handles, a session object can have several command objects.
An ODBC application performs the following steps to execute a command:
- Calls SQLAllocHandle to allocate a statement.
- Calls SQLSetStmtAttr to set various attributes that affect how the command is executed (such as query time-out) and how the cursor is opened (such as scrollability, updatability, and so on).
- Calls SQLPrepare if it wants to prepare the statement for repeated execution.
- Calls SQLExecute or SQLExecDirect to execute the query.
To use a command, an OLE DB consumer performs these steps:
- Calls IDBCreateCommand to create a command.
- Calls ICommandProperties::SetProperties to set various attributes that affect how the command is executed (such as query time-out), as well as requesting properties to be supported by the resulting rowset. Typical properties include scrollability, updatability, the number of active row handles a consumer can hold at one time, sensitivity to changes outside the rowset, and so on.
- Calls ICommandText::SetCommandText to specify the command text, along with a GUID representing the command's dialect. The standard dialect for ANSI SQL commands is DBGUID_DBSQL.
- Calls ICommandPrepare::Prepare if it wants to prepare the query for repeated execution.
- Calls ICommandText::Execute to execute the command.
The following code example shows setting and executing a command, and retrieving a rowset. The general flow of control is:
Obtain the IDBCreateSession interface.
Call CreateSession to create a session object that scopes the transaction boundaries within the current connection.
Call CreateCommand to create a command object within the transaction.
Call SetCommandText to set the command text.
Call Execute to execute the command.
Release the command object.
Return the rowset object.
/******************************************************************** * Execute a command, retrieve a rowset interface pointer. ********************************************************************/ HRESULT myCommand ( IDBInitialize* pIDBInitialize, // [in] IRowset** ppIRowset // [out] ) { IDBCreateSession* pIDBCreateSession; IDBCreateCommand* pIDBCreateCommand; IRowset* pIRowset; ICommandText* pICommandText; LPCTSTR wSQLString = OLESTR("SELECT CompanyName, City, Phone, Fax") OLESTR(" FROM Customers") OLESTR(" ORDER BY CompanyName, City"); LONG cRowsAffected; HRESULT hr; // Get the DB session object. if (FAILED(pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**) &pIDBCreateSession))) { DumpError("Session initialization failed."); return (E_FAIL); } // Create the session, getting an interface for command creation. hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown**) &pIDBCreateCommand); pIDBCreateSession->Release(); if (FAILED(hr)) { DumpError("Create session failed."); return (hr); } // Create the command object. hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &pICommandText); if (FAILED(hr)) { DumpError("Create command failed."); return (hr); } pIDBCreateCommand->Release(); // The command requires the actual text as well as an indicator // of its language and dialect. pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString); // Execute the command. hr = pICommandText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown**) &pIRowset); if (FAILED(hr)) { DumpError("Command execution failed."); } pICommandText->Release(); *ppIRowset = pIRowset; return (hr); }
OLE DB Rowsets
A rowset provides a standard way to work with a multiset of rows where each row contains one or more columns of data. This provides a standard way for all OLE DB data providers to expose data in tabular form.
A rowset can be obtained in several ways. The first method, supported by all providers, uses the IOpenRowset interface. IOpenRowset provides for the simple case of retrieving all the data from the table. This is equivalent to executing SELECT * FROM table. The second method, supported by SQL providers, involves creating and executing a command to get a rowset that meets a specific criteria, such as SELECT * FROM table WHERE condition. The ICommandProperties interface on the command can be used to specify the interfaces and properties that must be supported by the rowsets returned by ICommand::Execute.
Conceptually, rowsets are similar to result sets in ODBC; their implementation, however, is different.
In ODBC, when the application calls SQLFetch or SQLGetData to retrieve data, the data is read from the database into the application's memory. At that point, the application owns the data; neither the ODBC driver nor other components have access to that data. This model works fine provided that:
- The application is the only client of the data.
- A single component provides all of the functionality needed by the application.
- The application is in an environment in which it can maintain a connection to the database that keeps the state of the application's position within the data, concurrency information for data retrieved, and so on.
OLE DB rowsets are not only designed to provide a high performance solution for the preceding scenarios but are also designed so that:
- Multiple controls can work together on the same data. For example, a grid control and a chart control can model the same results: If the grid control updates the data, the chart control gets a notification and can redraw the graph accordingly.
- Multiple components can work together to add incremental functionality to a single instance of the data. The data does not have to be copied to multiple components, and method calls don't have to go through a hierarchy of components. Each component can operate directly on the data.
- The application can work in a disconnected environment with a stand-alone data object that maintains state, concurrency, and other information.
The OLE DB rowset basically takes the memory buffer out of the application and puts it in a stand-alone, shared data object. Rather than the application buffering data in its own memory, components access data in this shared memory through high-performance binding descriptions known as accessors. Because, in many cases, the application merely retrieves pointers to existing data and multiple components can access the same copy of the data, data access can often be faster than copying the data into the application's own buffers. The rowset object also allows multiple components to coordinate their activities on this shared data object through notifications, and because the components are all sharing the same data, they can immediately see the changes made by other components. The rowset object exposes bookmarks, which let each individual component keep track of its own position, and the rowset object keeps track of concurrency information for deferred updating and optimistic concurrency control.
Retrieving data from a rowset
The main differences between how data is retrieved in ODBC and how data is retrieved in OLE DB are a direct result of the differences between the application-owned data model of ODBC and the shared-data model of OLE DB.
The most basic rowset in OLE DB exposes the following interfaces:
- IColumnsInfo. Provides information about the columns of the rowset (metadata). This is similar to SQLDescribeCol in ODBC.
- IRowsetInfo. Provides information about the rowset. This is similar to SQLGetStmtAttr in ODBC.
- IAccessor. Permits definition of groups of column bindings describing the way in which tabular data is bound to program variables. This is similar to the bindings specified through descriptors by calling SQLSetDescField in ODBC version 3.0.
- IRowset. Contains methods for iterating through the rows in the rowset sequentially. The methods in IRowset are similar to SQLFetch in ODBC.
Retrieving rows
The provider manages the row buffers on behalf of the consumer. Rows are fetched from the data source into this row buffer using methods such as IRowset::GetNextRows, IRowsetLocate::GetRowsAt, and IRowsetLocate::GetRowsByBookmark. These methods are similar to SQLExtendedFetch in ODBC, except that instead of reading the data into the applications buffers, these functions read the data into the shared data object and return row handles (hRows) to the fetched data.
The consumer accesses the data from these row handles using IRowset::GetData. IRowset::GetData takes an accessor that maps fields of the row to and/or from fields in a structure on the consumer side. The types of the fields in the consumer's structure are indicated by the bindings in the accessor, and IRowset::GetData makes any necessary conversions between the buffered data and the consumer's data structure. If GetData encounters an error, it sets the status value for the column to the appropriate error.
To retrieve a row of data from the result set in ODBC, the application:
- Calls SQLBindCol to bind the columns of the result set to storage locations, if not already done.
- Calls SQLFetch to move to the next row and retrieve data for all bound columns.
- Calls SQLGetData to retrieve data from unbound columns.
In OLE DB, the consumer performs the following functions to retrieve data:
- Calls IAccessor::CreateAccessor to specify binding information if not already done.
- Calls IRowset::GetNextRows to retrieve the next set of row handles.
- Calls IRowset::GetData to retrieve the data from the row handles according to the bindings specified by the accessor.
IRowset::GetData is similar to SQLGetData in ODBC, except that IRowset::GetData:
- Can be called for any held row handle, not just the current row.
- Allows the retrieval of multiple columns in a single call.
- Can return an interface to a live OLE object, not just a binary large object (BLOB).
- Can be used to return a pointer to data within the provider rather than a copy of the data.
- Returns an interface to a stream over the object rather than retrieve long data through multiple calls to SQLGetData.
The following code example demonstrates data retrieval in OLE DB. The general flow of control is:
Get a description of the rowset.
Build binding structures based on the description.
Obtain the IAccessor interface on the rowset object.
Call CreateAccessor to create an accessor.
Call GetNextRows to retrieve the next set of row handles.
Call GetData to access the data for each row.
Release the set of retrieved row handles.
Repeat steps 4 through 6 until all the data has been retrieved.
Release the accessor.
/******************************************************************** * Retrieve data from a rowset. ********************************************************************/ void myGetData ( IRowset* pIRowset // [in] ) { ULONG nCols; DBCOLUMNINFO* pColumnsInfo = NULL; OLECHAR* pColumnStrings = NULL; ULONG nCol; ULONG cRowsObtained; // Count of rows // obtained ULONG iRow; // Row count HROW rghRows[NUMROWS_CHUNK]; // Row handles HROW* pRows = &rghRows[0]; // Pointer to the row // handles IAccessor* pIAccessor; // Pointer to the // accessor HACCESSOR hAccessor; // Accessor handle DBBINDSTATUS* pDBBindStatus = NULL; DBBINDING* pDBBindings = NULL; char* pRowValues; // Get the description of the rowset for use in binding structure // creation; see "Describing Query Results." if (FAILED(myGetColumnsInfo(pIRowset, &nCols, &pColumnsInfo, &pColumnStrings))) { return; } // Create the binding structures; see "Elements of an Accessor." myCreateDBBindings(nCols, pColumnsInfo, &pDBBindings, &pRowValues); pDBBindStatus = new DBBINDSTATUS[nCols]; // Create the accessor; see "Creating Accessors." pIRowset->QueryInterface(IID_IAccessor, (void**) &pIAccessor); pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA,// Accessor will be used to retrieve row // data nCols, // Number of columns being bound pDBBindings, // Structure containing bind info 0, // Not used for row accessors &hAccessor, // Returned accessor handle pDBBindStatus // Information about binding validity ); // Process all the rows, NUMROWS_CHUNK rows at a time. while (TRUE) { pIRowset->GetNextRows( 0, // Reserved 0, // cRowsToSkip NUMROWS_CHUNK, // cRowsDesired &cRowsObtained, // cRowsObtained &pRows ); // Filled in w/ row handles. // All done; there are no more rows left to get. if (cRowsObtained == 0) break; // Loop over rows obtained, getting data for each. for (iRow=0; iRow < cRowsObtained; iRow++) { pIRowset->GetData(rghRows[iRow], hAccessor, pRowValues); for (nCol = 0; nCol < nCols; nCol++) { wprintf(OLESTR("%s%s:"), pColumnsInfo[nCol].pwszName, wcslen(pColumnsInfo[nCol].pwszName) > 10 ? OLESTR("\t") : OLESTR("\t\t")); printf("\t%s\n", &pRowValues[pDBBindings[nCol].obValue]); } printf("\n"); } // Release row handles. pIRowset->ReleaseRows(cRowsObtained, rghRows, NULL, NULL, NULL); } // End while // Release the accessor. pIAccessor->ReleaseAccessor(hAccessor, NULL); pIAccessor->Release(); delete [] pDBBindings; delete [] pDBBindStatus; g_pIMalloc->Free( pColumnsInfo ); g_pIMalloc->Free( pColumnStrings ); return; }
Describing Query Results
In ODBC, to describe the results of a query, an application:
- Calls SQLNumResultCols to find out the number of columns in the result set.
- Calls SQLDescribeCol or SQLColAttribute to describe each column in the result set.
In OLE DB, to describe the results of a query, an application calls IColumnsInfo or IColumnsRowset to describe the columns in the rowset.
In OLE DB, the consumer can also call IRowsetInfo to get information about properties supported on the rowset. This is similar to calling SQLGetStmtAttr on an executed statement handle in ODBC.
The following code example shows getting the metadata from the result set. Note that the ColumnsInfo interface passes off ownership of both the DBCOLUMNINFO structure array and the OLECHAR string that holds strings that are part of the rowset's metadata. These strings are freed using the task memory allocation interface, IMalloc.
/********************************************************************
* Get the characteristics of the rowset (the ColumnsInfo interface).
********************************************************************/
HRESULT myGetColumnsInfo
(
IRowset* pIRowset, // [in]
ULONG* pnCols, // [out]
DBCOLUMNINFO** ppColumnsInfo, // [out]
OLECHAR** ppColumnStrings // [out]
)
{
IColumnsInfo* pIColumnsInfo;
HRESULT hr;
if (FAILED(pIRowset->QueryInterface(IID_IColumnsInfo, (void**)
&pIColumnsInfo)))
{
DumpError("Query rowset interface for IColumnsInfo failed");
return (E_FAIL);
}
hr = pIColumnsInfo->GetColumnInfo(pnCols, ppColumnsInfo,
ppColumnStrings);
if (FAILED(hr))
{
DumpError("GetColumnInfo failed.");
*pnCols = 0;
}
pIColumnsInfo->Release();
return (hr);
}
OLE DB Accessors
An accessor contains the binding information for one or more columns or parameters. The bindings supplied by the caller describe a data structure with offsets for each value. The consumer is responsible for correctly calculating the offsets. The consumer specifies the desired data type within the structure and the provider agrees to the binding if the conversion is legal and supported. The accessors are created on the rowset or command and their implementation is specific to the provider. They contain information or code to pack and unpack rows held by the provider. The consumer uses them like handles on the access functions of the row or column. Consumers can use more than one accessor, or a single accessor can define the mapping between a structure and multiple columns or parameters.
Accessors in OLE DB are similar to descriptor handles in ODBC. Each represents a reusable, extensible set of binding information for multiple columns of a result set or parameters to a statement. Multiple accessors can be created for a single rowset, just as multiple descriptors can be used on a single result set. Accessors can be created on a command object so that they can be shared between multiple rowsets the same way that descriptors can be shared between multiple result sets (hStmts).
Creating accessors
In ODBC version 3.0, applications can create reusable descriptor handles that specify extendable binding information for a set of columns. To use a descriptor, the ODBC application:
- Calls SQLAllocHandle to allocate a descriptor handle.
- Calls SQLSetDescField for each piece of binding information on each column.
In OLE DB, consumers create reusable accessor handles that specify binding information for a set of columns. To use an accessor, the consumer:
- Creates an array of binding structures, each describing the binding information for a single column.
- Calls CreateAccessor to create an accessor handle using the binding information.
The main difference between descriptors and accessors is that SQLSetDescField is called multiple times to set individual properties for each column represented by the descriptor, while all of the binding information for an accessor is built into an array of binding structures that is passed in a single call to CreateAccessor. This is partially addressed in ODBC by the presence of "concise" functions, such as SQLBindCol and SQLSetDescRec, which allow the application to specify the most common subset of binding information in a single call per column.
Elements of an accessor
The section of memory bound to a column or parameter must have at least one and can have up to three of the following parts:
- Value. The actual column or parameter value.
- Length. The actual length of the column or parameter data stored in the value part, in bytes.
- Status. This serves a dual purpose. First, it indicates if the value is NULL. Second, it is used to return information about whether the value was successfully passed to or returned from the provider.
It is possible to have columns that are deferred. For a deferred column, the provider is not required to retrieve data until IRowset::GetData is called for that column or, if the column contains an OLE object, until a method used to access the object is called. The advantage of deferred columns is that data retrieval can be postponed until the data is needed, which is very beneficial when the data is large.
The following structure defines the information specified for each column bound by an accessor.
typedef struct tagDBBINDING
{
ULONG iOrdinal; // Ordinal of column or
// parameter to bind
ULONG obValue; // Offset in consumer's buffer
// for value.
ULONG obLength; // Offset in consumer's buffer
// for length of value.
ULONG obStatus; // Offset in consumer's buffer
// for status of operation.
ITypeInfo __RPC_FAR* pTypeInfo; // Reserved, should be NULL.
DBOBJECT __RPC_FAR* pObject; // Pointer to object structure
// Structure describes access
// to OLE objects in the
// column.
DBBINDEXT __RPC_FAR* pBindExt; // Reserved, should be NULL.
DBPART dwPart; // Enumerated parts to bind
// (value/length/status)
DBMEMOWNER dwMemOwner; // Memory is owned by consumer?
// Provider?
DBPARAMIO eParamIO; // Parameter type
// (input/output/not a
// parameter)
ULONG cbMaxLen; // Size of consumer's value
// buffer if memory is consumer
// owned
DWORD dwFlags; // Reserved, should be 0.
DBTYPE wType; // Data type indicator
BYTE bPrecision; // Precision for
// numeric/decimal data types
BYTE bScale; // Scale for numeric/decimal
// data types
} DBBINDING;
The provider returns column or output parameter data to the consumer and also sets the status value of each column or output parameter.
| Status value | Meaning |
|---|---|
| DBSTATUS_S_OK | Column/parameter value set/retrieved successfully. |
| DBSTATUS_S_ISNULL | Column/parameter value is the NULL value. |
| DBSTATUS_S_TRUNCATED | Column/parameter truncated. |
| DBSTATUS_S_DEFAULT | The provider should use the parameter's default value. |
| DBSTATUS_E_BADACCESSOR | The accessor used to read/write the value was invalid. |
| DBSTATUS_E_CANTCONVERT | The accessor specified an invalid conversion. |
| DBSTATUS_E_CANTCREATE | The provider could not create a storage object for large data. |
| DBSTATUS_E_DATAOVERFLOW | The conversion failed due to a data overflow. |
| DBSTATUS_E_INTEGRITYVIOLATION | The data value violated an integrity constraint. |
| DBSTATUS_E_PERMISSIONDENIED | The data value could not be set due to insufficient permissions. |
| DBSTATUS_E_SCHEMAVIOLATION | The data value violated the Schema for the column. |
| DBSTATUS_E_SIGNMISMATCH | The data value had the incorrect sign. |
| DBSTATUS_E_UNAVAILABLE | The data was not available. |
The following code example shows traversing a DBCOLUMNINFO structure array obtained from a rowset and creating a set of bindings based on that information. An accessor is created from the binding structure array in the previous code sample.
/********************************************************************
* Create binding structures from column information. Binding
* structures will be used to create an accessor that allows row value
* retrieval.
********************************************************************/
void myCreateDBBindings
(
ULONG nCols, // [in]
DBCOLUMNINFO* pColumnsInfo, // [in]
DBBINDING** ppDBBindings, // [out]
char** ppRowValues // [out]
)
{
ULONG nCol;
ULONG cbRow = 0;
DBBINDING* pDBBindings;
char* pRowValues;
pDBBindings = new DBBINDING[nCols];
for (nCol = 0; nCol < nCols; nCol++)
{
pDBBindings[nCol].iOrdinal = nCol+1;
pDBBindings[nCol].obValue = cbRow;
pDBBindings[nCol].obLength = 0;
pDBBindings[nCol].obStatus = 0;
pDBBindings[nCol].pTypeInfo = NULL;
pDBBindings[nCol].pObject = NULL;
pDBBindings[nCol].pBindExt = NULL;
pDBBindings[nCol].dwPart = DBPART_VALUE;
pDBBindings[nCol].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
pDBBindings[nCol].eParamIO = DBPARAMIO_NOTPARAM;
pDBBindings[nCol].cbMaxLen = pColumnsInfo[nCol].ulColumnSize;
pDBBindings[nCol].dwFlags = 0;
pDBBindings[nCol].wType = pColumnsInfo[nCol].wType;
pDBBindings[nCol].bPrecision = pColumnsInfo[nCol].bPrecision;
pDBBindings[nCol].bScale = pColumnsInfo[nCol].bScale;
cbRow += pDBBindings[nCol].cbMaxLen;
}
pRowValues = new char[cbRow];
*ppDBBindings = pDBBindings;
*ppRowValues = pRowValues;
return;
}
Advanced Rowset Topics
OLE DB rowset interfaces and properties expose database cursor and bookmark support to the developer. In addition, OLE DB exposes support for client use of database resources such as scrollbar interface support.
The OLE DB cursor model is derived from that implemented in ODBC and includes support for cursor type, cursor scrolling capability, and transaction isolation levels.
Block cursors
OLE DB supports block cursors by specifying the number of rows desired in the IRowset::GetNextRows method. In ODBC, a block cursor is defined by specifying the rowset size using the SQL_ATTR_ROW_ARRAY_SIZE attribute in a call to SQLSetStmtAttr prior to calling SQLFetchScroll.
Sequential rowsets
Sequential rowsets do not support locating specific rows, or fetching rows already retrieved. GetNextRows can retrieve one or more rows, but the rows must all be released before another GetNextRows call is permitted. This restriction is known as strict sequential cursor. This is similar to the forward-only cursor supported by ODBC. A more flexible model, relaxed sequential, allows prior rows to be held, but GetNextRows is still sequential, and the consumer cannot revisit earlier parts of the rowset. The relaxed sequential model also allows changes and updates to rows that have already been passed but have been held. Sequential rowsets do not implement the IRowsetLocate interface.
GetNextRows is positional; that is, it fetches rows sequentially, at all times keeping track of the previous position. It can move forward, or backward if the rowset supports reversible direction. IRowset::RestartPosition repositions the next fetch position to the start of the rowset. GetNextRows keeps track of the next fetch position so that a sequence of calls to this method with no skips and no change of direction reads the entire rowset. This position is not connected to or disturbed by any other method that gets rows. Rowsets that support the property DBPROP_CANFETCHBACKWARDS can take a negative number for the count of requested rows. If the count of requested rows is negative, then the fetch direction is backwards. GetNextRows increases the reference count of each row for which it returns a handle. ReleaseRows must be called to release the handles that have been retrieved.
GetNextRows is the same as calling SQLFetch in ODBC with no columns bound, except that it can move forward or backward relative to the current position and can return multiple row handles.
Scrollable rowsets
Scrollable rowsets support IRowsetLocate. IRowsetLocate is equivalent to ODBC SQLFetchScroll. Scrollable rowsets are not positional; the consumer uses bookmarks to fetch relative to a previous position.
IRowsetLocate supports the following scrolling options:
- Forward or backward
- To the beginning or end of the rowset
- Relative to a specific row
Bookmarks
A bookmark is a value that the consumer can use to quickly and easily reposition to a row in the result set without incurring the overhead of holding on to the row. Bookmarks are valid only during the lifetime of the rowset to which they refer. An application retrieves a bookmark as column zero, in the same way it retrieves data from any other column in the rowset.
Bookmark columns have the DBCOLUMNFLAG_ISBOOKMARK flag set in their column information. A bookmark may have a data type indicator of DBTYPE_I4 or DBTYPE_STR.
OLE DB provides some standard bookmarks:
- DBBMK_INVALID. The bookmark is invalid. This is generally used for initializing variables.
- DBBMK_FIRST. Indicates the first row of the rowset.
- DBBMK_LAST. Indicates the last row of the rowset.
A bookmark becomes invalid if the row it points to is deleted, if it is based on a primary key and some of those key values were changed, or if the row it points to is no longer in the keyset. The validity of bookmarks after a transaction has been committed or aborted depends on the property DBPROP_COMMIT_PRESERVE. If this is set to true, then bookmarks remain valid; otherwise, they do not.
Scrollbar support
IRowsetScroll is the interface for moving to approximate locations in a moveable rowset. This method can be used for cases where precise positioning is not critical. IRowsetScroll supports the following interfaces:
- IRowsetScroll::GetApproximatePosition. Gets the position of the row corresponding to the specified bookmark.
- IRowsetScroll::GetRowsAtRatio. Gets rows starting from a fractional position in the rowset.
Cursor types
OLE DB support for scrollable cursors is provided and determined by the values of the rowset properties.
| Property | Meaning |
|---|---|
| DBPROP_CANSCROLLBACKWARDS | The rowset can return to previously read rows. |
| DBPROP_OWNINSERT | The rowset can see its own inserts. |
| DBPROP_OTHERINSERT | The rowset can see inserts made outside of the rowset. |
| DBPROP_OWNUPDATEDELETE | The rowset can see its own changes and deletions. |
| DBPROP_OTHERUPDATEDELETE | The rowset can see changes and deletions made outside of the rowset. |
These options are used to specify the characteristics of the static, keyset, and dynamic cursors defined in ODBC as follows:
Static cursor
In a static cursor, the membership, ordering, and values of the rowset is fixed after the rowset is opened. Rows updated, deleted, or inserted after the rowset is opened are not visible to the rowset until the command is re-executed.
To obtain a static cursor, the application sets the properties:
- DBPROP_CANSCROLLBACKWARDS to VARIANT_TRUE
- DBPROP_OTHERINSERT to VARIANT_FALSE
- DBPROP_OTHERUPDATEDELETE to VARIANT_FALSE
In ODBC, this is equivalent to specifying SQL_CURSOR_STATIC for the SQL_ATTR_CURSOR_TYPE attribute in a call to SQLSetStmtAttr.
Keyset-driven cursor
In a keyset-driven cursor, the membership and ordering of rows in the rowset are fixed after the rowset is opened. However, values within the rows can change after the rowset is opened, including the entire row that is being deleted. Updates to a row are visible the next time the row is fetched, but rows inserted after the rowset is opened are not visible to the rowset until the command is reexecuted.
To obtain a keyset-driven cursor, the application sets the properties:
- DBPROP_CANSCROLLBACKWARDS to VARIANT_TRUE
- DBPROP_OTHERINSERT to VARIANT_FALSE
- DBPROP_OTHERUPDATEDELETE to VARIANT_TRUE
In ODBC, this is equivalent to specifying SQL_CURSOR_KEYSET_DRIVEN for the SQL_ATTR_CURSOR_TYPE attribute in a call to SQLSetStmtAttr.
Dynamic cursor
In a dynamic cursor, the membership, ordering, and values of the rowset can change after the rowset is opened. The row updated, deleted, or inserted after the rowset is opened is visible to the rowset the next time the row is fetched.
To obtain a dynamic cursor, the application sets the properties:
- DBPROP_CANSCROLLBACKWARDS to VARIANT_TRUE
- DBPROP_OTHERINSERT to VARIANT_TRUE
- DBPROP_OTHERUPDATEDELETE to VARIANT_TRUE
In ODBC, this is equivalent to specifying SQL_CURSOR_DYNAMIC for the SQL_ATTR_CURSOR_TYPE attribute in the call to SQLSetStmtAttr.
Cursor sensitivity
If the rowset property DBPROP_OWNINSERT is set to VARIANT_TRUE, the rowset can see its own inserts; if the rowset property DBPROP_OWNUPDATEDELETE is set to VARIANT_TRUE, the rowset can see its own updates and deletes. These are equivalent to the presence of the SQL_CASE_SENSITIVITY_ADDITIONS bit and a combination of the SQL_CASE_SENSITIVITY_UPDATES and SQL_CASE_SENSITIVITY_DELETIONS bits that are returned in the ODBC SQL_STATIC_CURSOR_ATTRIBUTES2 SQLGetInfo request.
Handling Long Data Types
To a rowset, a BLOB or OLE storage object is a large sequence of uninterpreted bytes that a consumer stores in a column. It is the consumer's responsibility to interpret this sequence of bytes. BLOBs and OLE storage objects are categorized as:
- BLOB. Uninterpreted sequence of bytes. The type indicator of a BLOB column is DBTYPE_BYTES, DBTYPE_STR, or DBTYPE_WSTR.
- IPersist * Object. An OLE object supporting either IPersistStream, IPersistStreamInit, or IPersistStorage. The type indicator is DBTYPE_UNKNOWN.
A consumer can determine what types of BLOBs, OLE storage objects, and other types of OLE objects that a provider supports by calling IDBProperties with the DBPROP_OLEOBJECTS property.
BLOBs as Long Data
If the entire BLOB can be held in memory, it is treated as long in-memory data. To read the BLOB data, the consumer binds the column with a type identifier DBTYPE_BYTES, DBTYPE_STR, or DBTYPE_WSTR, and calls IRowset::GetData with an accessor containing this binding. The provider then returns the entire contents of the BLOB to the consumer.
BLOBs as Storage Objects
If a BLOB is too large to hold in memory, the consumer manipulates it through the ISequentialStream storage interface. The rows in the rowset are containers of the storage objects.
On retrieval, BLOB columns are deferred by default. Their data is not retrieved and storage objects are not created until GetData is called. In particular, methods that retrieve rows, such as GetNextRows, do not return data for BLOB columns in the data cache.
A storage object created by the provider remains valid until one of the following occurs:
- The consumer calls IRowset::ReleaseRows to release the row containing the storage object, or IRowset::Release to release the rowset containing the row.
- The consumer calls ITransaction::Commit or ITransaction::Abort, and the retaining flag is not set.
It is the consumer's responsibility to release the storage object, even if the containing row has been released.
Accessing BLOB data with storage objects
To bind to a BLOB data as a storage object, a consumer creates an accessor that includes a binding to the BLOB column. The consumer:
- Sets the dwType element of the DBBINDING structure for the BLOB column to DBTYPE_IUNKNOWN.
- Sets the iid element of the DBOBJECT structure in the binding to IID_ISequentialStream.
- Sets the dwFlags element of the DBOBJECT structure in the binding.
To read data from a BLOB column using a storage object, the consumer:
- Creates an accessor that includes a binding for the column.
- Calls IRowset::GetData with this accessor. The provider creates a storage object over the BLOB's data and returns a pointer to the requested storage interface (ISequentialStream) on this object.
- Calls methods on the storage interface to read the BLOB's data (ISequentialStream::Read).
If the consumer calls GetData, GetVisibleData, or GetOriginalData multiple times for the BLOB column, the provider returns distinct pointers to storage interfaces on each call. This is similar to opening a file a number of times and returning a different file handle each time. It is the consumer's responsibility to call Release on each of these storage interfaces.
For example, the following code example binds to a BLOB column and uses ISequentialStream::Read to read the data. For the complete source code listing, see Appendix B. The general flow of control is:
- Create a binding structure to retrieve the ISequentialStream interface from an OLE storage object.
- Obtain the IAccessor interface.
- Call CreateAccessor to create the accessor.
- Call GetNextRows to retrieve the row handles.
- Call GetData to retrieve the storage object from a row.
- Call ISequentialStream to read the data from the stream.
- Repeat steps 4, 5, and 6 to retrieve new storage objects and get the data.
/********************************************************************
* Retrieve data from an ODBC LONG_VARCHAR column (Notes in
* Employees).
********************************************************************/
void myGetBLOBData
(
IRowset* pIRowset // [in]
)
{
DBOBJECT ObjectStruct; // For binding, retrieve
// an object pointer.
DBBINDING rgBinding[1]; // Bind a single column.
IAccessor* pIAccessor = NULL; // Accessor creation
HACCESSOR hAccessor = NULL;
ULONG ulErrorBinding;
void* pData; // Bound consumer buffer
HROW rghRows[1];
HROW* pRows = &rghRows[0];
ULONG cRows;
char szNotes[BLOCK_SIZE + 1]; // Text data from
// "Notes"
ULONG cbRead; // Count of bytes read
// Set up the object structure for accessor creation. Ask the
// provider to return an ISequentialStream interface for reading.
ObjectStruct.dwFlags = STGM_READ;
ObjectStruct.iid = IID_ISequentialStream;
// Set up the binding structure for the accessor.
rgBinding[0].iOrdinal = 1; // Only one column
rgBinding[0].obValue = 0; // Offset to data
rgBinding[0].obLength = 0; // Ignore length
rgBinding[0].obStatus = sizeof(IUnknown*); // Offset to status
rgBinding[0].pTypeInfo = NULL; // Reserved
rgBinding[0].pObject = &ObjectStruct; // Our interface
// request
rgBinding[0].pBindExt = NULL; // Reserved
rgBinding[0].dwPart = DBPART_VALUE | // Get both VALUE...
DBPART_STATUS; // ...and STATUS
// parts.
rgBinding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBinding[0].eParamIO = DBPARAMIO_NOTPARAM;
rgBinding[0].cbMaxLen = 0; // Not applicable
rgBinding[0].dwFlags = 0; // Reserved
rgBinding[0].wType = DBTYPE_IUNKNOWN; // Type
// DBTYPE_IUNKNOWN
rgBinding[0].bPrecision = 0; // Not applicable
rgBinding[0].bScale = 0; // Not applicable
// Get the accessor interface and create the accessor.
pIRowset->QueryInterface(IID_IAccessor, (void**) &pIAccessor);
if (FAILED(pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1,
rgBinding, sizeof(IUnknown*) + sizeof(ULONG), &hAccessor,
&ulErrorBinding)))
{
DumpError("CreateAccessor failed.");
return;
}
// Allocate memory for the returned pointer and the status
// field. The first sizeof(IUnknown*) bytes are for the pointer
// to the object; the next sizeof(ULONG) bytes are for the
// status.
pData = new BYTE[sizeof(IUnknown*) + sizeof(ULONG)];
while (TRUE)
{
// Get the next row.
if (FAILED(pIRowset->GetNextRows(NULL, 0, 1, &cRows,
&pRows)))
{
DumpError("GetNextRows failed.\n");
break;
}
if (cRows == 0)
{
break;
}
// Get the row data, the pointer to an ISequentialStream*.
if (FAILED(pIRowset->GetData(*pRows, hAccessor, pData)))
{
DumpError("GetData failed.\n");
break;
}
// Read and process BLOCK_SIZE bytes at a time.
if ((ULONG)((BYTE*)pData)[rgBinding[0].obStatus] ==
DBSTATUS_S_ISNULL)
{
// Process NULL data.
printf("<null>");
}
else if ((ULONG)((BYTE*)pData)[rgBinding[0].obStatus] ==
DBSTATUS_S_OK)
{
do
{
(*((ISequentialStream**) pData))->Read(szNotes,
BLOCK_SIZE, &cbRead);
if (cbRead > 0)
{
// Process data.
szNotes[cbRead] = (char) NULL;
printf(szNotes);
}
}
while (cbRead >= BLOCK_SIZE);
(*((ISequentialStream**) pData))->Release();
printf("\n\n");
}
pIRowset->ReleaseRows(cRows, pRows, NULL, NULL, NULL);
}
// Clean up.
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
delete [] pData;
}
To write data to a BLOB column using a storage object, the consumer first creates an accessor that includes a binding for the column and then:
- Calls IRowset::GetData with the accessor that binds the BLOB column. The provider creates a storage object over the BLOB's data and returns a pointer to the requested storage interface (ISequentialStream) on this object.
- Calls a method on the storage interface to write data (ISequentialStream::Write).
OLE's structured storage model supports both transacted and direct modes. In transacted mode, all changes are buffered, and the buffered changes are persisted or discarded only when an explicit commit or abort request is done. In direct mode, every change is followed by an automatic commit. If the storage object is transacted (that is, the STGM_TRANSACTED flag is set in the dwFlags element of the DBOBJECT structure in the binding), the storage object does not publish the changes to the containing rowset until the consumer calls Commit on the storage interface. If the storage object is not transacted (that is, the STGM_DIRECT flag is set), the storage object publishes the changes to the containing rowset when the consumer calls a method on the storage interface to write the changes.
Alternately, the consumer calls IRowsetChange::SetData or IRowsetChange::InsertRow with the accessor that binds the BLOB column, passing a pointer to a storage interface on a separate storage object.
Data Manipulation
Data manipulation in OLE DB can be executed using SQL queries in commands or using the IRowsetChange interface.
IRowsetChange allows the consumer to:
- Update columns of a row by calling IRowsetChange::SetData.
- Delete a row or rows by calling IRowsetChange::DeleteRows.
- Insert a new row by calling IRowsetChange::InsertRow.
Delayed Updates
OLE DB supports delayed updates. With delayed updates, changes made to the rowset are not transmitted to the data source until IRowsetUpdate::Update is called.
A rowset can be in delayed or immediate update mode, depending on whether it exposes the IRowsetUpdate interface. The consumer specifies whether IRowsetUpdate should be supported prior to opening the rowset.
If the IRowsetUpdate interface is not included on the rowset, then the rowset is said to be in immediate update mode, and the changes are immediately transmitted to the data source. If IRowsetUpdate is present, then the changes are not transmitted to the data source until IRowsetUpdate::Update is called.
For rowsets in delayed update mode, IRowsetUpdate allows the consumer to:
- Retrieve the initial value read for the row by calling IRowsetUpdate::GetOriginalData.
- Get a list of pending changes by calling IRowsetUpdate::GetPendingRows.
- Get the pending status of a given set of rows by calling IRowsetUpdate::GetRowStatus.
- Undo some, or all, of the pending changes by calling IRowsetUpdate::Undo.
- Commit some, or all, of the changes to the data source by calling IRowsetUpdate::Update.
Prepared commands
If a command is to be executed multiple times, it is often more efficient to prepare it. Command preparation tells the query processor to save the execution plan so that it doesn't have to be rebuilt for each execution.
The ICommandPrepare interface supports methods for preparing a command. Calling ICommandPrepare::Prepare in OLE DB is equivalent to calling SQLPrepare in ODBC.
Commands with parameters
Parameters are used to insert scalar values into a command at execute time. Parameters are generally used in conjunction with a prepared command so that the command can be executed multiple times, each time with a different value.
To specify parameters in ODBC, an application uses SQLBindParameter and SQLParamData in the following order:
- Calls SQLBindParameter for each parameter to specify the parameter type and bind buffers for the parameters values.
- Calls SQLSetStmtAttr if multiple values are to be specified for each set of parameters.
- Places the values in appropriate buffers.
- Calls SQLExecute or SQLExecDirect.
To specify parameters in OLE DB, an application uses the ICommandWithParameters interface in the following order:
- Creates an accessor describing the binding information for the set of parameters.
- Calls ICommandWithParameters::SetParameterInfo to specify the types of the parameters.
- Calls ICommand::Execute to execute the command, passing a structure containing the accessor, number of parameter sets, and a pointer to data for the parameters.
Binding parameters
The consumer specifies parameter descriptions by setting information in the DBPARAMBINDINFO structure passed to ICommandWithParameters::SetParameterInfo. This is similar to the type, precision, and scale information specified in SQLBindParameter in ODBC.
The DBPARAMBINDINFO structure is:
typedef struct tagDBPARAMBINDINFO
{
LPOLESTR pwszDataSourceType; // Data type name (OLESTR)
LPOLESTR pwszName; // Name of the parameter
ULONG ulParamSize; // Maximum length of data
// accepted
DBPARAMFLAGS dwFlags; // Input/output/signed/nullable/object
BYTE bPrecision; // Precision for numeric data
// types
BYTE bScale; // Scale for numeric data types
} DBPARAMBINDINFO;
The ICommand::Execute method takes a pointer to the DBPARAMS structure as an argument. This structure includes a pointer to the data as well as an accessor that describes the layout of the data. Bindings described in the accessor are similar to the bindings specified in SQLBindParameter in ODBC. OLE DB allows the specification of multiple sets of parameters in a single call by specifying the number of sets of parameters in the cParamSets element of the DBPARAMS structure. This is similar to calling SQLSetStmtAttr in ODBC.
The DBPARAMS structure is:
typedef struct tagDBPARAMS
{
void __RPC_FAR* pData; // Data, array containing
// parameter values
ULONG cParamSets; // Count of sets of parameter
// values in the data array
HACCESSOR hAccessor; // Handle of parameter
// describing accessor
} DBPARAMS;
The following code example shows parameter passing in OLE DB. For the complete source code listing, see Appendix C. The general flow of control is:
Create bindings describing the parameters.
Obtain the IDBCreateSession interface.
Call CreateSession to create a session object that scopes the transaction boundaries within the current connection.
Call CreateCommand to create a command object within the transaction.
Call SetCommandText to set the command text.
Obtain the ICommandWithParameters interface on the command object.
Call SetParameterInfo to specify the parameter information.
Prepare the command.
Create a parameter accessor.
Build a structure containing the parameter information.
Call Execute, providing the parameter accessor and parameter
information, to execute the command.
Release the command object.
/******************************************************************** * Execute a prepared INSERT statement with parameters. ********************************************************************/ HRESULT myInsertWithParameters ( IDBInitialize* pIDBInitialize ) { IDBCreateSession* pIDBCreateSession; IDBCreateCommand* pIDBCreateCommand; ICommandText* pICommandText; ICommandPrepare* pICommandPrepare; ICommandWithParameters* pICmdWithParams; IAccessor* pIAccessor; WCHAR wSQLString[] = OLESTR("insert into Customers (CustomerID, CompanyName, ContactName,") OLESTR(" ContactTitle, Address, City, Region, PostalCode, Country,") OLESTR(" Phone, Fax)") OLESTR(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); DBPARAMS Params; long cRowsAffected; HACCESSOR hParamAccessor; NEWCUSTOMERDATA aNewCustomers[] = { "YOUME", "You and Me Grocers", "William Smith", "General Manager", "383 15th Ave. N.", "New York", "NY", "10018", "USA", "(212) 555-8847", "(212) 555-9988", "YORBL", "Yorbalinda's", "Mary Jones", "Owner", "22 Sunny Vale Rd.", "San Diego", "CA", "93122", "USA", "(605) 555-4322", "(605) 555-4323" }; NEWCUSTOMER NewCustomer; ULONG nParams = 11; DBPARAMBINDINFO rgParamBindInfo[] = { OLESTR("DBTYPE_CHAR"), OLESTR("CustomerID"), 5, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("CompanyName"), 40, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("ContactName"), 30, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("ContactTitle"), 30, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("Address"), 60, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("City"), 15, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("Region"), 15, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("PostalCode"), 10, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("Country"), 15, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("Phone"), 24, DBPARAMFLAGS_ISINPUT, 0, 0, OLESTR("DBTYPE_VARCHAR"), OLESTR("FAX"), 24, DBPARAMFLAGS_ISINPUT, 0, 0, }; ULONG rgParamOrdinals[] = {1,2,3,4,5,6,7,8,9,10,11}; // Get the session. pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&pIDBCreateSession); pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown**) &pIDBCreateCommand); pIDBCreateSession->Release(); // Create the command. pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &pICommandText); pIDBCreateCommand->Release(); // The command requires the actual text as well as an indicator // of its language. pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString); // Set parameter information. pICommandText->QueryInterface(IID_ICommandWithParameters, (void**)&pICmdWithParams); pICmdWithParams->SetParameterInfo(nParams, rgParamOrdinals, rgParamBindInfo); pICmdWithParams->Release(); // Prepare the command. pICommandText->QueryInterface(IID_ICommandPrepare, (void**)&pICommandPrepare); if (FAILED(pICommandPrepare->Prepare(0))) { pICommandPrepare->Release(); pICommandText->Release(); return (E_FAIL); } pICommandPrepare->Release(); // Create parameter accessors. if (FAILED(myCreateParamAccessor(pICommandText, &hParamAccessor, &pIAccessor))) { pICommandText->Release(); return (E_FAIL); } Params.pData = &NewCustomer; // pData is the buffer pointer Params.cParamSets = 1; // Number of sets of parameters Params.hAccessor = hParamAccessor;// Accessor to the parameters // Specify the parameter information. for (UINT nCust = 0; nCust < 2; nCust++) { strcpy(NewCustomer.acCustomerID, aNewCustomers[nCust].szCustID); strcpy(NewCustomer.acCompanyName, aNewCustomers[nCust].szCompanyName); strcpy(NewCustomer.acContactName, aNewCustomers[nCust].szContactName); strcpy(NewCustomer.acContactTitle, aNewCustomers[nCust].szContactTitle); strcpy(NewCustomer.acAddress, aNewCustomers[nCust].szAddress); strcpy(NewCustomer.acCity, aNewCustomers[nCust].szCity); strcpy(NewCustomer.acRegion, aNewCustomers[nCust].szRegion); strcpy(NewCustomer.acPostalCode, aNewCustomers[nCust].szPostalCode); strcpy(NewCustomer.acCountry, aNewCustomers[nCust].szCountry); strcpy(NewCustomer.acPhone, aNewCustomers[nCust].szPhone); strcpy(NewCustomer.acFAX, aNewCustomers[nCust].szFAX); // Execute the command. pICommandText->Execute(NULL, IID_NULL, &Params, &cRowsAffected, NULL); printf("%ld rows inserted.\n", cRowsAffected); } pIAccessor->ReleaseAccessor(hParamAccessor, NULL); pIAccessor->Release(); pICommandText->Release(); return (NOERROR); }
Creating parameter accessors
Input parameter data is read from, and output parameter data is written to, the specified locations within Params.pData according to the bindings specified by the accessor. An array of parameter sets can be passed in pParamData. cParamSets indicates the number of elements of the array.
The following code example shows parameter binding for the NEWCUSTOMER structure used in the previous example. The general flow of control is:
Specify the common binding information for all the parameters.
Specify the specific binding information for each parameter.
Call CreateAccessor to create the parameter accessor.
Return the accessor handle along with the interface used to create it (so that it can be freed later).
/******************************************************************** * Create parameter accessor. ********************************************************************/ HRESULT myCreateParamAccessor ( ICommand* pICmd, // [in] HACCESSOR* phAccessor, // [out] IAccessor** ppIAccessor // [out] ) { IAccessor* pIAccessor; HACCESSOR hAccessor; const ULONG nParams = 11; DBBINDING Bindings[nParams]; DBBINDSTATUS rgStatus[nParams]; // Returns information for // individual binding // validity. HRESULT hr; ULONG acbLengths[] = {5, 40, 30, 30, 60, 15, 15, 10, 15, 24, 24}; for (ULONG i = 0; i < nParams; i++) { Bindings[i].iOrdinal = i + 1; Bindings[i].obLength = 0; Bindings[i].obStatus = 0; Bindings[i].pTypeInfo = NULL; Bindings[i].pObject = NULL; Bindings[i].pBindExt = NULL; Bindings[i].dwPart = DBPART_VALUE; Bindings[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED; Bindings[i].eParamIO = DBPARAMIO_INPUT; Bindings[i].cbMaxLen = acbLengths[i]; Bindings[i].dwFlags = 0; Bindings[i].wType = DBTYPE_STR; Bindings[i].bPrecision = 0; Bindings[i].bScale = 0; } Bindings[0].obValue = offsetof(NEWCUSTOMER, acCustomerID); Bindings[1].obValue = offsetof(NEWCUSTOMER, acCompanyName); Bindings[2].obValue = offsetof(NEWCUSTOMER, acContactName); Bindings[3].obValue = offsetof(NEWCUSTOMER, acContactTitle); Bindings[4].obValue = offsetof(NEWCUSTOMER, acAddress); Bindings[5].obValue = offsetof(NEWCUSTOMER, acCity); Bindings[6].obValue = offsetof(NEWCUSTOMER, acRegion); Bindings[7].obValue = offsetof(NEWCUSTOMER, acPostalCode); Bindings[8].obValue = offsetof(NEWCUSTOMER, acCountry); Bindings[9].obValue = offsetof(NEWCUSTOMER, acPhone); Bindings[10].obValue = offsetof(NEWCUSTOMER, acFAX); pICmd->QueryInterface(IID_IAccessor, (void**)&pIAccessor); hr = pIAccessor->CreateAccessor( DBACCESSOR_PARAMETERDATA, // Accessor that will be used // to specify parameter data nParams, // Number of parameters being // bound Bindings, // Structure containing bind // information sizeof(NEWCUSTOMER), // Size of parameter structure &hAccessor, // Returned accessor handle rgStatus // Information about binding // validity ); if (FAILED(hr)) { DumpError("Parameter accessor creation failed."); } else { *ppIAccessor = pIAccessor; *phAccessor = hAccessor; } return (hr); }
Advanced OLE DB Topics
This section describes advanced OLE DB topics, such as data source location, catalog and schema functions, transactions, error handling, and data types.
Locating a Data Source
In ODBC, the application generally calls SQLDataSources to enumerate the different ODBC data sources installed on the computer. The application can also call SQLDrivers to enumerate the specific drivers and connect to them directly, without a data source. ODBC version 3.0 defines the concept of a file DSN, which is a data source definition that lives as a file in the file system.
In OLE DB, consumers can browse for data sources using an enumerator. Enumerators provide a recursive, hierarchical model for browsing data sources. This model is similar to browsing for files in the file system, where a folder is represented by an enumerator and a file is represented by a data source. Just as folders can enumerate other folders as well as files, an enumerator can enumerate other enumerators as well as data sources.
A root enumerator is provided as part of the OLE DB SDK that generates a rowset of available data providers and enumerators. Binding to a data source returns an uninitialized data source object for that data source.
Once created, a data source object can be persisted as a file in the file system. Binding a file moniker to one of these persisted data sources generates an uninitialized data source object with a particular state, such as a data source name or location. Persisted data sources are equivalent to file DSNs in ODBC version 3.0. The consumer must still initialize the data source object after it is loaded.
Catalog and Schema Functions
Schema information in OLE DB is retrieved using rowsets in the same manner as other types of data. The schema information specified in OLE DB is based on the ANSI SQL concepts of catalog and schema. A schema is a collection of database objects that are owned or have been created by a particular user, and a catalog contains one or more schemas.
The IDBSchemaRowset interface provides a mechanism to the consumer for retrieving information about the database without knowing its structure. Schema rowsets are identified by GUIDs. Each schema GUID has a set of restrictions that can be specified to reduce the result set to the desired information. For example, to get a list of columns in a particular table, the schema name, database name, and table name can be specified as restrictions. The order and data type of these restrictions are fixed and are listed in the Microsoft OLE DB Programmer's Reference. It is not necessary to specify all the restrictions; however, the unspecified restrictions must be set to type DBTYPE_EMPTY or VT_EMPTY.
The IDBSchemaRowset interface has two methods:
- IDBSchemaRowset::GetRowset. Returns one of the standard schema information rowsets.
- IDBSchemaRowset::GetSchema. Returns a list of schema information views accessible through the GetRowset method.
The IDBSchemaRowset interface encapsulates the functionality of the catalog functions in ODBC, such as SQLTables, SQLColumns, SQLProcedures, SQLProcedureColumns, SQLStatistics, SQLPrimaryKeys, SQLForeignKeys, and SQLGetTypeInfo.
Transactions
A session can be inside or outside of a transaction at any point in time. When a session is created, it is outside of a transaction, and all work done under the scope of that session is immediately committed on each method call. This is referred to as the autocommit or implicit commit mode.
If the provider supports transactions, the session supports the ITransactionLocal interface. Calling ITransactionLocal::StartTransaction begins a transaction on the session. ITransactionLocal inherits from the ITransaction interface, which supports the Commit and Abort methods. When a session enters a transaction, all work done by the session, its command and rowsets, are part of that transaction.
The following code example shows calling commit or abort on a session:
ITransactionLocal* pITransactionlocal;
pIDBCreateSession->QueryInterface(IID_ITransactionLocal,
(IUnknown**) &pITransactionlocal);
pITransactionlocal->StartTransaction(
ISOLATIONLEVEL_READCOMMITTED, 0, NULL, NULL);
// Do some work...setting bSave for the following.
if (bSave) // For commit
pITransactionlocal->Commit(FALSE, XACTTC_SYNC_PHASEONE, 0);
else // For abort
pITransactionlocal->Abort(NULL, FALSE, FALSE); // For abort
pITransactionlocal->Release();
Transactions have two important properties: retention and preservation. Retention indicates whether another transaction is implicitly started on committing or aborting the transaction. Preservation indicates whether the rowset capabilities are preserved on committing or aborting the transaction. Committing or aborting a transaction with the fRetention flag set to TRUE will implicitly start another transaction. After a commit or abort, the full functionality of the rowset is preserved or is zombied, depending on whether the rowset properties DBPROP_COMMITPRESERVE and DBPROP_ABORTPRESERVE are set to TRUE or FALSE. A zombied rowset is an object whose functionality is virtually lost. It supports only IUnknown operations and releasing row handles. A preserved rowset has all its capabilities intact.
Isolation Levels
OLE DB defines the following isolation levels. These are similar to the isolation levels in ODBC.
- Read Uncommitted. The values in a record being accessed can change at any time.
- Read Committed. The values in a record do not change from the time the record is read until it is rewritten or passed over.
- Repeatable Read. The relationship between values in different records doesn't change while the transaction is executing.
- Serializable. The existence of reference records does not change while the transaction is executing.
Error Handling
Methods return error information in two ways. The code returned by a method indicates the overall success or failure of a method while error objects provide detailed information. Success and warning return codes begin with S_ or DB_S_ and indicate that the method completed successfully. If a single warning condition occurs, the method returns the return code for that condition. If multiple warning conditions occur, a hierarchy is defined to determine which code is returned. Error return codes begin with E_ or DB_E_ and indicate that the method failed completely. Error objects in OLE DB are an extension of the error objects in OLE Automation.
- The ISupportErrorInfo interface, defined by OLE Automation, determines whether an object can return OLE DB error objects and which interfaces on that object can return OLE DB error objects.
- The IErrorInfo interface, defined by OLE Automation, returns the error message, the name of the component and the GUID of the interface in which the error occurred, and the name and topic of the help file that applies to the error.
- The IErrorLookup interface is exposed by a provider supporting OLE DB error objects. The provider creates an error lookup service and the lookup service exposes the IErrorLookup interface. This service is used by IErrorRecords and IErrorInfo interfaces.
- The IErrorRecords interface is the main interface through which OLE DB error objects are accessed.
- The ISQLErrorInfo interface is used to return the SQLSTATE and native error. All ODBC-related providers can expose this interface.
Data Types
OLE DB uses standard OLE- and Windows-based data types, and uses DBTYPE as a type indicator. A type indicator denotes the data type of a piece of memory. A type indicator is used to describe data types in metadata, method calls, and data and parameter binding.
| Type indicators | Physical type |
|---|---|
| DBTYPE_EMPTY | |
| DBTYPE_NULL | |
| DBTYPE_RESERVED | |
| DBTYPE_I1 | signed char |
| DBTYPE_I2 | SHORT |
| DBTYPE_I4 | LONG |
| DBTYPE_I8 | LARGE_INTEGER |
| DBTYPE_UI1 | BYTE |
| DBTYPE_UI2 | unsigned short |
| DBTYPE_UI4 | unsigned int |
| DBTYPE_UI8 | ULARGE_INTEGER |
| DBTYPE_R4 | float |
| DBTYPE_R8 | double |
| DBTYPE_CY | LARGE_INTEGER |
| DBTYPE_DECIMAL | DBDECIMAL |
| DBTYPE_NUMERIC | DBNUMERIC |
| DBTYPE_DATE | DATE |
| DBTYPE_BOOL | VARIANT_BOOL |
| DBTYPE_BYTES | BYTE[cbMaxlen] |
| DBTYPE_BSTR | BSTR |
| DBTYPE_STR | char[cbMaxLen] |
| DBTYPE_WSTR | wchar_t[cbMaxLen] |
| DBTYPE_VARIANT | VARIANT |
| DBTYPE_IDISPATCH | IDispatch * |
| DBTYPE_IUNKOWN | IUnknown * |
| DBTYPE_GUID | GUID |
| DBTYPE_ERROR | SCODE |
| DBTYPE_BYREF | void * |
| DBTYPE_ARRAY | SAFEARRAY * |
| DBTYPE_VECTOR | DBVECTOR |
| DBTYPE_UDT | undefined |
| DBTYPE_DBDATE | DBDATE |
| DBTYPE_DBTIME | DBTIME |
| DBTYPE_TIMESTAMP | DBTIMESTAMP |
Each OLE DB provider chooses a default type indicator for each of its native types and documents them in the PROVIDER_TYPES schema rowset. Providers are required to transfer data in the C type that corresponds to that type indicator and to convert data to and from WCHAR, if such a conversion is defined in the conversion tables. For the conversion table and guidelines, see Microsoft OLE DB Programmer's Reference, Appendix A.
ODBC Attributes and Corresponding OLE DB Properties
OLE DB and ODBC are different interfaces. A one-to-one correspondence between the properties of one interface and the attributes of the other does not exist. OLE DB contains a rich definition of property sets and interface method parameters that provide services significantly different from those provided by ODBC. For more information, see the OLE DB documentation of schema rowsets, property sets, and methods.
The following tables show ODBC attributes and their OLE DB property equivalents. Where a one-to-one mapping cannot be made easily, the OLE DB property set and property columns are left blank.
Column Attributes
ODBC developers use SQLDescribeCol, SQLColAttribute, or SQLGetDescField to retrieve a given attribute. The interfaces and retrieval methods in OLE DB are also shown.
| ODBC attribute | OLE DB property set, schema rowset, or method | OLE DB property |
|---|---|---|
| SQL_DESC_AUTO_
UNIQUE_VALUE |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
ISAUTOINCREMENT |
| SQL_DESC_BASE_
COLUMN_NAME |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
BASECOLUMNNAME |
| SQL_DESC_BASE_
TABLE_NAME |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
BASETABLENAME |
| SQL_DESC_CASE_
SENSITIVE |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
ISCASESENSITIVE |
| SQL_DESC_CATALOG_
NAME |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
BASECATALOGNAME |
| SQL_DESC_CONCISE_
TYPE |
||
| SQL_DESC_COUNT | IColumnsInfo::GetColumnInfo | The value returned in the pcColumns argument |
| SQL_DESC_DISPLAY_SIZE | ||
| SQL_DESC_FIXED_
PREC_SCALE |
IDBSchemaRowset::GetRowset DBSCHEMA_PROVIDER_TYPES rowset restricted by DBCOLUMN_TYPE property from IColumnsRowset::GetColumnsRowset | FIXED_PREC_SCALE column |
| SQL_DESC_LABEL | IColumnsRowset::GetColumnsRowset | DBCOLUMN_NAME |
| SQL_DESC_LENGTH | IColumnsInfo::GetColumnInfo | Derived from ulColumnSize |
| SQL_DESC_NAME | IColumnsRowset::GetColumnsRowset | DBCOLUMN_NAME |
| SQL_DESC_NULLABLE | IColumnsRowset::GetColumnsRowset
IColumnsInfo::GetColumnInfo |
DBCOLUMN_FLAGS or dwFlags flag DBCOLUMNFLAGS_
ISNULLABLE |
| SQL_DESC_NUM_PREX_
RADIX |
||
| SQL_DESC_OCTET_
LENGTH |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
OCTETLENGTH |
| SQL_DESC_PRECISION | IColumnsRowset::GetColumnsRowset
IColumnsInfo::GetColumnInfo |
DBCOLUMN_
PRECISION or bPrecision |
| SQL_DESC_SCALE | IColumnsRowset::GetColumnsRowset
IColumnsInfo::GetColumnInfo |
DBCOLUMN_SCALE or bScale |
| SQL_DESC_SCHEMA_
NAME |
IColumnsRowset::GetColumnsRowset | DBCOLUMN_
BASESCHEMANAME |
| SQL_DESC_SEARCHABLE | IColumnsRowset::GetColumnsRowset | DBCOLUMN_
ISSEARCHABLE |
| SQL_DESC_TABLE_NAME | IColumnsRowset::GetColumnsRowset | DBCOLUMN_
BASETABLENAME |
| SQL_DESC_TYPE | IColumnsRowset::GetColumnsRowset
IColumnsInfo::GetColumnInfo |
DBCOLUMN_TYPE or wType |
| SQL_DESC_TYPE_NAME | IDBSchemaRowset::GetRowset DBSCHEMA_PROVIDER_TYPES rowset restricted by DBCOLUMN_TYPE property from IColumnsRowset::GetColumnsRowset | TYPE_NAME column |
| SQL_DESC_UNNAMED | ||
| SQL_DESC_UNSIGNED | IDBSchemaRowset::GetRowset DBSCHEMA_PROVIDER_TYPES rowset restricted by DBCOLUMN_TYPE property from IColumnsRowset::GetColumnsRowset | UNSIGNED_
ATTRIBUTE column |
| SQL_DESC_UPDATABLE | IColumnsRowset::GetColumnsRowset
IColumnsInfo::GetColumnInfo |
DBCOLUMN_FLAGS or dwFlags flag DBCOLUMNFLAGS_
WRITE |
Connection Attributes
ODBC connection attributes that control the behavior of the ODBC drivers, such as SQL_ATTR_ASYNC_ENABLE, are not included. OLE DB database and schema properties are indicated.
ODBC attribute |
OLE DB property set, schema rowset, or method | OLE DB property or flag |
|---|---|---|
| SQL_ATTR_ACCESS_MODE | IDBProperties::SetProperties | DBPROP_
DATASOURCEREADONLY |
| SQL_ATTR_AUTOCOMMIT | Autocommit mode is on if the session is outside of a transaction | |
| SQL_ATTR_CONNECTION_
TIMEOUT |
||
| SQL_ATTR_CURRENT_
CATALOG |
IDBProperties::SetProperties | DBPROP_
CURRENTCATALOG |
| SQL_ATTR_LOGIN_
TIMEOUT |
IDBProperties::SetProperties | DBPROP_INIT_TIMEOUT |
| SQL_ATTR_QUIET_MODE | IDBProperties::SetProperties | DBPROP_INIT_HWND |
| SQL_ATTR_TXN_
ISOLATION |
ITransactionLocal::StartTransaction | isoLevel |
Statement Attributes
OLE DB command properties and rowset methods are indicated.
ODBC statement attributes that control driver behaviors, such as SQL_ATTR_NOSCAN, are not included in the table. Statement attributes that are used to set bound parameter and column behaviors are also not included. OLE DB uses the DBBINDING structure together with data accessors to control arrays of parameters and bound columns. For more information, see "Getting and Setting Data" in the OLE DB documentation.
ODBC attribute |
OLE DB property set, schema rowset, or method | OLE DB property or flag |
|---|---|---|
| SQL_ATTR_ASYNC_
ENABLE |
Multiple concurrent operations are performed by different threads | |
| SQL_ATTR_
CONCURRENCY |
ITransactionLocal::StartTransaction
ISessionProperties::SetProperties DBPROPSET_SESSION |
isoLevel value for the StartTransaction interface, DBPROP_SESS_
AUTOCOMMIT_ISOLEVELS when set for auto commit mode on the session |
| SQL_ATTR_CURSOR_
SCROLLABLE |
ICommandProperties::SetProperties
DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS |
| SQL_ATTR_CURSOR_
SENSITIVITY |
ICommandProperties::SetProperties
DBPROPSET_ROWSET |
DBPROP_OTHERINSERT and DBPROP_
OTHERUPDATEDELETE |
| SQL_ATTR_CURSOR_
TYPE |
ICommandProperties::SetProperties
DBPROPSET_ROWSET |
DBPROP_CANSCROLL-BACKWARDS, DBPROP_OTHER-INSERT, and DBPROP_
OTHERUPDATEDELETE |
| SQL_ATTR_KEYSET_
SIZE |
||
| SQL_ATTR_MAX_
LENGTH |
||
| SQL_ATTR_MAX_ROWS | ICommandProperties::SetProperties
DBPROPSET_ROWSET |
DBPROP_MAXROWS |
| SQL_ATTR_QUERY_
TIMEOUT |
ICommandProperties::SetProperties
DBPROPSET_ROWSET |
DBPROP_
COMMANDTIMEOUT |
| SQL_ATTR_RETRIEVE_
DATA |
IRowset::GetData | Data is not retrieved until GetData method is called on the rowset |
| SQL_ATTR_ROW_
ARRAY_SIZE |
IRowset::GetNextRows | cRows value |
| SQL_ATTR_ROW_
NUMBER |
SQLGetInfo
SQLGetInfo returns information about a specific ODBC data source. OLE DB offers provider property sets and other entry points to expose provider-specific behaviors.
OLE DB allows providers to define additional property sets. For example, the ODBC provider shipped with the OLE DB SDK defines the DBPROPSET_PROVIDERROWSET and DBPROPSET_PROVIDERDATASOURCEINFO property sets, and some SQLGetInfo InfoType parameters are supported through these provider-specific property sets.
In the following table, SQLGetInfo InfoType parameters that have no meaning in OLE DB have been eliminated (for example, SQL_DM_VER, which returns the ODBC Driver Manager version number).
Where a one-to-one match does not exist, a tip is provided to help you discover the information. For example, SQLGetInfo InfoType SQL_ALTER_DOMAIN has no direct support in OLE DB. However, you can use the provider's IDBInfo::GetKeywords method to determine whether or not the provider recognizes the SQL ALTER statement.
SQLGetInfo InfoType value |
OLE DB property set, schema rowset, or method | OLE DB property or flag |
|---|---|---|
| SQL_ACCESSIBLE_
PROCEDURES |
||
| SQL_ACCESSIBLE_TABLES | ||
| SQL_ACTIVE_
ENVIRONMENTS |
||
| SQL_AGGREGATE_
FUNCTIONS |
IDBInfo::GetKeywords | |
| SQL_ALTER_DOMAIN | IDBInfo::GetKeywords | |
| SQL_ALTER_TABLE | IDBInfo::GetKeywords | |
| SQL_ASYNC_MODE | ||
| SQL_BATCH_ROW_
COUNT |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
MULTIPLEPARAMSETS |
| SQL_BATCH_SUPPORT | ||
| SQL_BOOKMARK_
PERSISTENCE |
Bookmarks are valid for the lifetime of the rowset to which they apply | |
| SQL_CATALOG_LOCATION | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
CATALOGLOCATION |
| SQL_CATALOG_NAME | IDBInfo::GetLiteralInfo | DBLITERAL_CATALOG_
NAME |
| SQL_CATALOG_NAME_
SEPARATOR |
IDBInfo::GetLiteralInfo | DBLITERAL_CATALOG_
SEPARATOR |
| SQL_CATALOG_TERM | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_CATALOGTERM |
| SQL_CATALOG_USAGE | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_CATALOGUSAGE |
| SQL_COLLATION_SEQ | IDBSchemaRowset::GetRowset
COLLATIONS |
COLLATION_NAME column |
| SQL_COLUMN_ALIAS | ||
| SQL_CONCAT_NULL_
BEHAVIOR |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
CONCATNULLBEHAVIOR |
| SQL_CONVERT (all types) | IConvertType::CanConvertType | |
| SQL_CONVERT_
FUNCTIONS |
IDBInfo::GetKeywords | |
| SQL_CORRELATION_NAME | ||
| SQL_CREATE_ASSERTION | IDBInfo::GetKeywords | |
| SQL_CREATE_
CHARACTER_SET |
IDBInfo::GetKeywords | |
| SQL_CREATE_COLLATION | IDBInfo::GetKeywords | |
| SQL_CREATE_DOMAIN | IDBInfo::GetKeywords | |
| SQL_CREATE_SCHEMA | IDBInfo::GetKeywords | |
| SQL_CREATE_TABLE | IDBInfo::GetKeywords | |
| SQL_CREATE_
TRANSLATION |
IDBInfo::GetKeywords | |
| SQL_CREATE_VIEW | IDBInfo::GetKeywords | |
| SQL_CURSOR_COMMIT_
BEHAVIOR |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO ICommandProperties:: GetProperties DBPROPSET_ROWSET |
DBPROP_PREPARE-COMMITBEHAVIOR
DBPROP_ COMMITPRESERVE |
| SQL_CURSOR_ROLLBACK_
BEHAVIOR |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO ICommandProperties:: GetProperties DBPROPSET_ROWSET |
DBPROP_PREPARE-ABORTTBEHAVIOR
DBPROP_ ABORTPRESERVE |
| SQL_CURSOR_SENSITIVITY | ||
| SQL_DATA_SOURCE_NAME | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
DATASOURCENAME |
| SQL_DATA_SOURCE_
READ_ONLY |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
DATASOURCEREADONLY |
| SQL_DATABASE_NAME | IDBProperties::GetProperties
DBPROPSET_DATASOURCE |
DBPROP_
CURRENTCATALOG |
| SQL_DATETIME_LITERALS | ||
| SQL_DBMS_NAME | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_DBMSNAME |
| SQL_DBMS_VER | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_DBMSVER |
| SQL_DDL_INDEX | IDBInfo::GetKeywords | |
| SQL_DEFAULT_TXN_
ISOLATION |
Transaction isolation is explicitly specified | |
| SQL_DESCRIBE_
PARAMETER |
||
| SQL_DRIVER_NAME | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_PROVIDERNAME |
| SQL_DRIVER_ODBC_VER | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
PROVIDEROLEDBVER |
| SQL_DRIVER_VER | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_PROVIDERVER |
| SQL_DROP_ASSERTION | IDBInfo::GetKeywords | |
| SQL_DROP_CHARACTER_
SET |
IDBInfo::GetKeywords | |
| SQL_DROP_COLLATION | IDBInfo::GetKeywords | |
| SQL_DROP_DOMAIN | IDBInfo::GetKeywords | |
| SQL_DROP_SCHEMA | IDBInfo::GetKeywords | |
| SQL_DROP_TABLE | IDBInfo::GetKeywords | |
| SQL_DROP_TRANSLATION | IDBInfo::GetKeywords | |
| SQL_DROP_VIEW | IDBInfo::GetKeywords | |
| SQL_DYNAMIC_CURSOR_
ATTRIBUTES1 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OTHERINSERT, DBPROP_ OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_DYNAMIC_CURSOR_
ATTRIBUTES2 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OTHERINSERT, DBPROP_ OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_EXPRESSIONS_IN_
ORDERBY |
||
| SQL_FETCH_DIRECTION | ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS |
| SQL_FILE_USAGE | ||
| SQL_FORWARD_ONLY_
CURSOR_ATTRIBUTES1 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_OTHERINSERT, DBPROP_
OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_FORWARD_ONLY_
CURSOR_ATTRIBUTES2 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_OTHERINSERT, DBPROP_
OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_GETDATA_
EXTENSIONS |
||
| SQL_GROUP_BY | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_GROUPBY |
| SQL_IDENTIFIER_CASE | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_IDENTIFIERCASE |
| SQL_IDENTIFIER_QUOTE_
CHAR |
IDBInfo::GetLiteralInfo | DBLITERAL_QUOTE |
| SQL_INDEX_KEYWORDS | IDBInfo::GetKeywords | |
| SQL_INFO_SCHEMA_VIEWS | ||
| SQL_INSERT_STATEMENT | ||
| SQL_INTEGRITY | ||
| SQL_KEYSET_CURSOR_
ATTRIBUTES1 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_KEYSET_CURSOR_
ATTRIBUTES2 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_KEYWORDS | IDBInfo::GetKeywords | |
| SQL_LIKE_ESCAPE_
CLAUSE |
IDBInfo::GetLiteralInfo | DBLITERAL_ESCAPE_
PERCENT, DBLITERAL_ ESCAPE_UNDERSCORE |
| SQL_MAX_ASYNC_
CONCURRENT_ STATEMENTS |
||
| SQL_MAX_BINARY_
LITERAL_LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_BINARY_
LITERAL |
| SQL_MAX_CATALOG_
NAME_LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_CATALOG_
NAME |
| SQL_MAX_CHAR_
LITERAL_LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_CHAR_LITERAL |
| SQL_MAX_COLUMN_
NAME_LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_COLUMN_
NAME |
| SQL_MAX_COLUMNS_
IN_GROUP_BY |
||
| SQL_MAX_COLUMNS_
IN_INDEX |
||
| SQL_MAX_COLUMNS_
IN_ORDER_BY |
||
| SQL_MAX_COLUMNS_
IN_SELECT |
||
| SQL_MAX_COLUMNS_
IN_TABLE |
||
| SQL_MAX_CONCURRENT_
ACTIVITIES |
||
| SQL_MAX_CURSOR_NAME_
LEN |
||
| SQL_MAX_DRIVER_
CONNECTIONS |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_ACTIVESESSIONS |
| SQL_MAX_IDENTIFIER_
LEN |
||
| SQL_MAX_INDEX_SIZE | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_MAXINDEXSIZE |
| SQL_MAX_PROCEDURE_
NAME_LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_PROCEDURE_
NAME |
| SQL_MAX_ROW_SIZE | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_MAXROWSIZE |
| SQL_MAX_ROW_SIZE_
INCLUDES_LONG |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
MAXROWSIZEINCLUDES-BLOB |
| SQL_MAX_STATEMENT_
LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_TEXT_
COMMAND |
| SQL_MAX_TABLE_NAME_
LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_TABLE_NAME |
| SQL_MAX_TABLES_IN_
SELECT |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
MAXTABLESINSELECT |
| SQL_MAX_USER_NAME_
LEN |
IDBInfo::GetLiteralInfo | DBLITERAL_USER_NAME |
| SQL_MULT_RESULT_SETS | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
MULTIPLERESULTS |
| SQL_MULTIPLE_ACTIVE_
TXN |
Provider is responsible for spawning connections, if necessary, to provide multiple transactions from one DSO | |
| SQL_NEED_LONG_DATA_
LEN |
||
| SQL_NON_NULLABLE_
COLUMNS |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
COLUMNDEFINITION |
| SQL_NULL_COLLATION | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_NULLCOLLATION |
| SQL_NUMERIC_FUNCTIONS | ||
| SQL_OJ_CAPABILITIES | ||
| SQL_ORDER_BY_
COLUMNS_IN_SELECT |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
ORDERBYCOLUMNSIN-SELECT |
| SQL_OUTER_JOINS | ||
| SQL_OWNER_TERM | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_SCHEMATERM |
| SQL_OWNER_USAGE | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_SCHEMAUSAGE |
| SQL_PARAM_ARRAY_
ROW_COUNTS |
||
| SQL_PARAM_ARRAY_
SELECTS |
||
| SQL_PROCEDURE_TERM | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
PROCEDURETERM |
| SQL_PROCEDURES | IDBSchemaRowset::GetSchemas returns GUID for PROCEDURES schema rowset | |
| SQL_QUOTED_IDENTIFIER_
CASE |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
QUOTEDIDENTIFIERCASE |
| SQL_ROW_UPDATES | ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_IRowsetResynch |
| SQL_SCHEMA_TERM | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_SCHEMATERM |
| SQL_SCHEMA_USAGE | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_SCHEMAUSAGE |
| SQL_SCROLL_OPTIONS | ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OTHERINSERT, DBPROP_ OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_SEARCH_PATTERN_
ESCAPE |
||
| SQL_SERVER_NAME | IDBProperties::GetProperties
DBPROPSET_INIT |
DBPROP_INIT_LOCATION |
| SQL_SPECIAL_
CHARACTERS |
IDBInfo::GetLiteralInfo | Value of pwszInvalidChars when inquiring about identifiers, and so on |
| SQL_STATIC_CURSOR_
ATTRIBUTES1 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_STATIC_CURSOR_
ATTRIBUTES2 |
ICommandProperties::
GetProperties DBPROPSET_ROWSET |
DBPROP_
CANSCROLLBACKWARDS, DBPROP_OWNINSERT, DBPROP_ OWNUPDATEDELETE |
| SQL_STRING_FUNCTIONS | ||
| SQL_SUBQUERIES | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_SUBQUERIES |
| SQL_SYSTEM_FUNCTIONS | ||
| SQL_TABLE_TERM | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_TABLETERM |
| SQL_TIMEDATE_ADD_
INTERVALS |
||
| SQL_TIMEDATE_DIFF_
INTERVALS |
||
| SQL_TIMEDATE_
FUNCTIONS |
||
| SQL_TXN_CAPABLE | IUnknown::QueryInterface on session for IID_ITransactionLocal | |
| SQL_TXN_ISOLATION_
OPTION |
IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_
SUPPORTED-TXNISORETAIN |
| SQL_UNION | ||
| SQL_USER_NAME | IDBProperties::GetProperties
DBPROPSET_DATASOURCEINFO |
DBPROP_USERNAME |
The following interfaces also support setting and getting of property information:
- ICommandProperties. Registers with the command the properties from the Rowset Property Group. The consumer can specify whether these properties specify optional or required support on the returned rowset.
- IRowsetInfo::GetProperties. Returns the current settings of all properties supported by the rowset. The value returned by this method can be different from the value returned by ICommandProperties::GetProperties for a property. This is because the provider's ability to support the property might be affected by the current transaction or current query.
Mapping ODBC Functions to OLE DB Methods
Because a direct mapping between ODBC functions and OLE DB methods does not exist in all cases, only similar functionality is provided. When more than one method can be used to achieve the same thing, the methods are separated with the word "or," for example, MethodA or MethodB. When more than one method must be called in sequence, the methods are numbered, for example, 1. MethodA, 2. MethodB, and so on.
| ODBC function | OLE DB method | Remarks/property |
|---|---|---|
| SQLAllocHandle
Environment Connection Descriptor Statement |
OleInitialize CoCreateInstance IAccessor::CreateAccessor IDBCreateCommand::CreateCommand |
|
| SQLBindCol | IAccessor::CreateAccessor | |
| SQLBindParameter | IAccessor::CreateAccessor | |
| SQLBrowseConnect | ISourcesRowset::GetSourcesRowset | |
| SQLBulkOperations
Insertions Deletions Updates Retrieve by bookmark |
IRowsetChange::InsertRows IRowsetChange::DeleteRows IRowsetChange::SetData IRowsetLocate::GetRowsByBookmark |
If IRowsetUpdate is requested, all operations are batched until IRowsetUpdate::Update is called |
| SQLCancel | ICommand::Cancel | |
| SQLCloseCursor | IRowset::Release | |
| SQLColAttribute | IColumnsInfo::GetColumnInfo or IColumnsRowset::GetColumnsRowset | |
| SQLColumnPrivileges | IDBSchemaRowset::GetRowset | DBSCHEMA_
COLUMN_PRIVILEGES |
| SQLColumns | IDBSchemaRowset::GetRowset | DBSCHEMA_COLUMNS |
| SQLConnect | 1. IDBProperties::SetProperties
2. IDBInitialize::Initialize |
|
| SQLDataSources | ISourcesRowset::GetSourcesRowset | |
| SQLDescribeCol | IColumnsInfo::GetColumnInfo | |
| SQLDescribeParam | ICommandWithParameters::
GetParameterInfo |
|
| SQLDisconnect | IDBInitialize::Uninitialize | |
| SQLDriverConnect | 1. IDBProperties::SetProperties
2. IDBInitialize::Initialize |
|
| SQLDrivers | ISourcesRowset::GetSourcesRowset | |
| SQLEndTran | ITransaction::Commit or ITransaction::Abort | |
| SQLError | 1. GetErrorInfo
2. IErrorRecords 3. IErrorInfo |
|
| SQLExecDirect | ICommand::Execute | |
| SQLExecute | ICommand::Execute | |
| SQLFetch | 1. IRowset::GetNextRows,
IRowsetLocate::GetRowsAt, or IRowsetLocate::GetRowsByBookmark 2. IRowset::GetData |
|
| SQLFetchScroll | 1. IRowset::GetNextRows,
IRowsetLocate::GetRowsAt, or IRowsetLocate::GetRowsByBookmark 2. IRowset::GetData |
|
| SQLForeignKeys | IDBSchemaRowset::GetRowset | DBSCHEMA_
FOREIGN_KEYS |
| SQLFreeHandle
Environment Connection Descriptor Statement |
OleUninitialize IDBInitialize::Release IAccessor::ReleaseAccessor IRowset::Release |
|
| SQLFreeStmt
SQL_DROP SQL_CLOSE SQL_UNBIND SQL_RESET_ PARAMS |
ICommand::Release IRowset::Release IAccessor::ReleaseAccessor IAccessor::ReleaseAccessor |
|
| SQLGetConnectAttr | IDBProperties::GetProperties | |
| SQLGetCursorName | Not applicable | OLE DB performs updates through update methods, as opposed to positioned cursor operations |
| SQLGetData | IRowset::GetData
(for all data except BLOBs) or ILockBytes, ISequentialStream, IStorage, or IStream (for BLOB data) |
|
| SQLGetDescField | IAccessor::GetBindings | Elements within binding structure |
| SQLGetDescRec | IAccessor::GetBindings | Elements within binding structure |
| SQLDiagField | 1. GetErrorInfo
2. IErrorRecords 3. IErrorInfo |
|
| SQLDiagRec | 1. GetErrorInfo
2. IErrorRecords 3. IErrorInfo |
|
| SQLGetEnvAttr | IDBProperties::GetProperties | |
| SQLGetFunctions | IUnknown::QueryInterface | |
| SQLGetInfo | IDBProperties::GetProperties | |
| SQLGetStmtAttr | ICommandProperties::GetProperties or IRowsetInfo::GetProperties | |
| SQLGetTypeInfo | IDBSchemaRowset::GetRowset | DBSCHEMA_
PROVIDER_TYPES |
| SQLMoreResults | IMultipleResults | |
| SQLNativeSql | ICommandText::GetCommandText | With NULL as pguidDialect |
| SQLNumParams | ICommandWithParams::GetParameterInfo | |
| SQLNumResultCols | IColumnsInfo::GetColumnInfo | |
| SQLParamData | Not applicable | |
| SQLPrepare | ICommand::Prepare | |
| SQLPrimaryKeys | IDBSchemaRowset::GetRowset | DBSCHEMA_
PRIMARY_KEYS |
| SQLProcedureColumns | IDBSchemaRowset::GetRowset | DBSCHEMA_
PROCEDURE_PARAMETERS, DBSCHEMA_ PROCEDURE_COLUMNS |
| SQLProcedures | IDBSchemaRowset::GetRowset | DBSCHEMA_PROCEDURES |
| SQLPutData | IRowsetChange::SetData
(for all data except BLOBs) or ISequentialStream (for BLOB data) |
|
| SQLRowCount | cRowsAffected in ICommand::Execute | |
| SQLSetConnectAttr | IDBProperties::SetProperties | |
| SQLSetCursorName | Not applicable | OLE DB performs updates through update methods, as opposed to positioned cursor operations |
| SQLSetDescField | IAccessor::CreateAccessor | Elements within binding structure |
| SQLSetDescRec | IAccessor::CreateAccessor | Elements within binding structure |
| SQLSetEnvAttr | IDBProperties::SetProperties | |
| SQLSetParam | ICommandWithParameters::SetParameterInfo | |
| SQLSetPos
SQL_POSITION SQL_REFRESH SQL_UPDATE SQL_DELETE SQL_ADD |
Not applicable IRowset::GetData or IRowsetResynch IRowsetChange::SetData IRowsetChange::Delete IRowsetChange::InsertRow IRowsetUpdate::Update |
|
| SQLSetStmtAttr | ICommandProperties::SetProperties | |
| SQLSpecialColumns | IColumnsInfo::GetColumnInfo | DBCOLUMNFLAGS_
ISROWID and DBCOLUMNFLAGS_ ISROWVER |
| SQLStatistics | IDBSchemaRowset::GetRowset | DBSCHEMA_STATISTICS |
| SQLTablePrivileges | IDBSchemaRowset::GetRowset | DBSCHEMA_TABLE_
PRIVILEGES |
| SQLTables | IDBSchemaRowset::GetRowset | DBSCHEMA_TABLES |
| SQLTransact | ITransaction::Abort and ITransaction::Commit |
Conclusion
ODBC and OLE DB each provide a rich application programming interface for the database developer. OLE and the component object model present the ODBC developer with an entirely new way of writing. The ODBC developer, familiar with procedural programming, must become comfortable with the world of OLE objects and the interface negotiation required to manipulate those objects.
In addition, OLE DB often presents a new implementation of familiar development concepts like tabular data structures. The ODBC developer will need to rethink application structure to make effective use of OLE DB's sessions, property sets, rowsets, and accessors.
Becoming comfortable with OLE objects is a first step toward successful OLE application development. In this paper, tasks typically executed by an ODBC application are implemented through OLE DB object creation and manipulation. The information presented builds on the ODBC database developer's existing knowledge to smooth the introduction to this new and different technology.
Finding More Information
For more information about OLE DB and ODBC, see the following publications:
Brockschmidt, Kraig. Inside OLE. Microsoft Press, 1995.
Geiger, Kyle. Inside ODBC. Microsoft Press, 1995.
Microsoft ODBC 3.0 Programmer's Reference and SDK Guide. Microsoft Press, 1997.
Microsoft OLE 2 Programmer's Reference. Volume 1. Microsoft Press, 1994.
Microsoft OLE DB Programmer's Reference. Version 1.1, 1996.
Appendix A
The following is a complete source code listing for the example presented in "Basic OLE DB." The example selects the CompanyName, City, Phone and FAX columns from the Customers table of the Access Northwind sample database. The database is shipped with the OLE DB SDK and is installed as the ODBC data source OLE_DB_NWind_Jet.
To build the file using Microsoft Visual C++ ® 4.0 or later:
- Create a new console application.
- Copy the following code to a new .cpp file.
- Ensure that your build directory settings reference the OLE DB SDK include directory.
- Alter the build link settings to include linking to Oledb.lib.
/********************************************************************
* OLE DB
********************************************************************/
#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS // Initialize OLE constants...
#define INITGUID // ...once in each app
#include <windows.h>
#include <stdio.h>
#include <oledb.h> // OLE DB include files
#include <oledberr.h>
#include <msdaguid.h> // ODBC provider include files
#include <msdasql.h>
// Macros--number of row identifiers to retrieve
#define NUMROWS_CHUNK 35
// Prototypes
HRESULT myInitDSO(IDBInitialize** ppIDBI);
HRESULT mySetInitProps(IDBInitialize* pIDBInitialize);
HRESULT myCommand(IDBInitialize* pIDBI, IRowset** ppIRowset);
void myGetData(IRowset* pIRowset);
void DumpError(LPSTR lpStr);
HRESULT myGetColumnsInfo(IRowset* pIRowset, ULONG* pnCols,
DBCOLUMNINFO** ppColumnsInfo, OLECHAR** ppColumnStrings);
void myCreateDBBindings(ULONG nCols, DBCOLUMNINFO* pColumnsInfo,
DBBINDING** ppDBBindings, char** ppRowValues);
// Global task memory allocator
IMalloc* g_pIMalloc = NULL;
/********************************************************************
* General OLE DB application main()
********************************************************************/
int main()
{
IDBInitialize* pIDBInitialize = NULL;
IRowset* pIRowset = NULL;
// Init OLE and set up the DLLs
CoInitialize(NULL);
// Get the task memory allocator.
if (FAILED(CoGetMalloc(MEMCTX_TASK, &g_pIMalloc)))
goto EXIT;
// Connect to the data source.
if (FAILED(myInitDSO(&pIDBInitialize)))
goto EXIT;
// Get a session, set and execute a command.
if (FAILED(myCommand(pIDBInitialize, &pIRowset)))
goto EXIT;
// Retrieve data from rowset.
myGetData(pIRowset);
EXIT:
// Clean up and disconnect.
if (pIRowset != NULL)
pIRowset->Release();
if (pIDBInitialize != NULL)
{
if (FAILED(pIDBInitialize->Uninitialize()))
{
// Uninitialize is not required, but it will fail if an
// interface has not been released; we can use it for
// debugging.
DumpError("Someone forgot to release something!");
}
pIDBInitialize->Release();
}
if (g_pIMalloc != NULL)
g_pIMalloc->Release();
CoUninitialize();
return (0);
}
/********************************************************************
* Initialize the data source.
********************************************************************/
HRESULT myInitDSO
(
IDBInitialize** ppIDBInitialize // [out]
)
{
// Create an instance of the MSDASQL (ODBC) provider.
CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void**)ppIDBInitialize);
if (*ppIDBInitialize == NULL)
{
return (E_FAIL);
}
if (FAILED(mySetInitProps(*ppIDBInitialize)))
{
return (E_FAIL);
}
if (FAILED((*ppIDBInitialize)->Initialize()))
{
DumpError("IDBInitialze->Initialize failed.");
return (E_FAIL);
}
return (NOERROR);
}
/********************************************************************
* Set initialization properties on a data source.
********************************************************************/
HRESULT mySetInitProps
(
IDBInitialize* pIDBInitialize // [in]
)
{
const ULONG nProps = 4;
IDBProperties* pIDBProperties;
DBPROP InitProperties[nProps];
DBPROPSET rgInitPropSet;
HRESULT hr;
// Initialize common property options.
for (ULONG i = 0; i < nProps; i++ )
{
VariantInit(&InitProperties[i].vValue);
InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[i].colid = DB_NULLID;
}
// Level of prompting that will be done to complete the
// connection process
InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
InitProperties[0].vValue.vt = VT_I2;
InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;
// Data source name--see the sample source included with the OLE
// DB SDK.
InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal =
SysAllocString(OLESTR("OLE_DB_NWind_Jet"));
// User ID
InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR(""));
// Password
InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
InitProperties[3].vValue.vt = VT_BSTR;
InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR(""));
rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet.cProperties = nProps;
rgInitPropSet.rgProperties = InitProperties;
// Set initialization properties.
pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)
&pIDBProperties);
hr = pIDBProperties->SetProperties(1, &rgInitPropSet);
SysFreeString(InitProperties[1].vValue.bstrVal);
SysFreeString(InitProperties[2].vValue.bstrVal);
SysFreeString(InitProperties[3].vValue.bstrVal);
pIDBProperties->Release();
if (FAILED(hr))
{
DumpError("Set properties failed.");
}
return (hr);
}
/********************************************************************
* Execute a command, retrieve a rowset interface pointer.
********************************************************************/
HRESULT myCommand
(
IDBInitialize* pIDBInitialize, // [in]
IRowset** ppIRowset // [out]
)
{
IDBCreateSession* pIDBCreateSession;
IDBCreateCommand* pIDBCreateCommand;
IRowset* pIRowset;
ICommandText* pICommandText;
LPCTSTR wSQLString = OLESTR("SELECT CompanyName,
City, Phone, Fax")
OLESTR(" FROM Customers")
OLESTR(" ORDER BY CompanyName,
City");
LONG cRowsAffected;
HRESULT hr;
// Get the DB session object.
if (FAILED(pIDBInitialize->QueryInterface(IID_IDBCreateSession,
(void**) &pIDBCreateSession)))
{
DumpError("Session initialization failed.");
return (E_FAIL);
}
// Create the session, getting an interface for command creation.
hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand);
pIDBCreateSession->Release();
if (FAILED(hr))
{
DumpError("Create session failed.");
return (hr);
}
// Create the command object.
hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(IUnknown**) &pICommandText);
if (FAILED(hr))
{
DumpError("Create command failed.");
return (hr);
}
pIDBCreateCommand->Release();
// The command requires the actual text as well as an indicator
// of its language and dialect.
pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);
// Execute the command.
hr = pICommandText->Execute(NULL, IID_IRowset, NULL,
&cRowsAffected, (IUnknown**) &pIRowset);
if (FAILED(hr))
{
DumpError("Command execution failed.");
}
pICommandText->Release();
*ppIRowset = pIRowset;
return (hr);
}
/********************************************************************
* Get the characteristics of the rowset (the ColumnsInfo interface).
********************************************************************/
HRESULT myGetColumnsInfo
(
IRowset* pIRowset, // [in]
ULONG* pnCols, // [out]
DBCOLUMNINFO** ppColumnsInfo, // [out]
OLECHAR** ppColumnStrings // [out]
)
{
IColumnsInfo* pIColumnsInfo;
HRESULT hr;
if (FAILED(pIRowset->QueryInterface(IID_IColumnsInfo, (void**) &pIColumnsInfo)))
{
DumpError("Query rowset interface for IColumnsInfo failed");
return (E_FAIL);
}
hr = pIColumnsInfo->GetColumnInfo(pnCols, ppColumnsInfo, ppColumnStrings);
if (FAILED(hr))
{
DumpError("GetColumnInfo failed.");
*pnCols = 0;
}
pIColumnsInfo->Release();
return (hr);
}
/********************************************************************
* Create binding structures from column information. Binding
* structures will be used to create an accessor that allows row value
* retrieval.
********************************************************************/
void myCreateDBBindings
(
ULONG nCols, // [in]
DBCOLUMNINFO* pColumnsInfo, // [in]
DBBINDING** ppDBBindings, // [out]
char** ppRowValues // [out]
)
{
ULONG nCol;
ULONG cbRow = 0;
DBBINDING* pDBBindings;
char* pRowValues;
pDBBindings = new DBBINDING[nCols];
for (nCol = 0; nCol < nCols; nCol++)
{
pDBBindings[nCol].iOrdinal = nCol+1;
pDBBindings[nCol].obValue = cbRow;
pDBBindings[nCol].obLength = 0;
pDBBindings[nCol].obStatus = 0;
pDBBindings[nCol].pTypeInfo = NULL;
pDBBindings[nCol].pObject = NULL;
pDBBindings[nCol].pBindExt = NULL;
pDBBindings[nCol].dwPart = DBPART_VALUE;
pDBBindings[nCol].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
pDBBindings[nCol].eParamIO = DBPARAMIO_NOTPARAM;
pDBBindings[nCol].cbMaxLen = pColumnsInfo[nCol].ulColumnSize;
pDBBindings[nCol].dwFlags = 0;
pDBBindings[nCol].wType = pColumnsInfo[nCol].wType;
pDBBindings[nCol].bPrecision = pColumnsInfo[nCol].bPrecision;
pDBBindings[nCol].bScale = pColumnsInfo[nCol].bScale;
cbRow += pDBBindings[nCol].cbMaxLen;
}
pRowValues = new char[cbRow];
*ppDBBindings = pDBBindings;
*ppRowValues = pRowValues;
return;
}
/********************************************************************
* Retrieve data from a rowset.
********************************************************************/
void myGetData
(
IRowset* pIRowset // [in]
)
{
ULONG nCols;
DBCOLUMNINFO* pColumnsInfo = NULL;
OLECHAR* pColumnStrings = NULL;
ULONG nCol;
ULONG cRowsObtained; // Number of rows obtained
ULONG iRow; // Row count
HROW rghRows[NUMROWS_CHUNK];// Row handles
HROW* pRows = &rghRows[0]; // Pointer to the row
// handles
IAccessor* pIAccessor; // Pointer to the accessor
HACCESSOR hAccessor; // Accessor handle
DBBINDSTATUS* pDBBindStatus = NULL;
DBBINDING* pDBBindings = NULL;
char* pRowValues;
// Get the description of the rowset for use in binding structure
// creation.
if (FAILED(myGetColumnsInfo(pIRowset, &nCols, &pColumnsInfo,
&pColumnStrings)))
{
return;
}
// Create the binding structures.
myCreateDBBindings(nCols, pColumnsInfo, &pDBBindings,
&pRowValues);
pDBBindStatus = new DBBINDSTATUS[nCols];
// Create the accessor.
pIRowset->QueryInterface(IID_IAccessor, (void**) &pIAccessor);
pIAccessor->CreateAccessor(
DBACCESSOR_ROWDATA, // Accessor will be used to retrieve row
// data.
nCols, // Number of columns being bound
pDBBindings, // Structure containing bind info
0, // Not used for row accessors
&hAccessor, // Returned accessor handle
pDBBindStatus // Information about binding validity
);
// Process all the rows, NUMROWS_CHUNK rows at a time
while (TRUE)
{
pIRowset->GetNextRows(
0, // Reserved
0, // cRowsToSkip
NUMROWS_CHUNK, // cRowsDesired
&cRowsObtained, // cRowsObtained
&pRows ); // Filled in w/ row handles.
// All done; there is no more rows left to get.
if (cRowsObtained == 0)
break;
// Loop over rows obtained, getting data for each.
for (iRow=0; iRow < cRowsObtained; iRow++)
{
pIRowset->GetData(rghRows[iRow], hAccessor, pRowValues);
for (nCol = 0; nCol < nCols; nCol++)
{
wprintf(OLESTR("%s%s:"), pColumnsInfo[nCol].pwszName,
wcslen(pColumnsInfo[nCol].pwszName) > 10 ?
OLESTR("\t") :
OLESTR("\t\t"));
printf("\t%s\n",
&pRowValues[pDBBindings[nCol].obValue]);
}
printf("\n");
}
// Release row handles.
pIRowset->ReleaseRows(cRowsObtained, rghRows, NULL, NULL,
NULL);
} // End while
// Release the accessor.
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
delete [] pDBBindings;
delete [] pDBBindStatus;
g_pIMalloc->Free( pColumnsInfo );
g_pIMalloc->Free( pColumnStrings );
return;
}
/********************************************************************
* Dump an error to the console.
********************************************************************/
void DumpError(LPSTR lpStr)
{
printf(lpStr);
printf("\n");
}
Appendix B
The following is a complete source code listing for the example presented in "Handling Long Data Types." The example selects the Notes column (LONG_VARCHAR) from the Employees table of the Access Northwind sample database. The database is shipped with the OLE DB SDK and is installed as the ODBC data source OLE_DB_NWind_Jet.
To build the file using Microsoft Visual C++ 4.0 or later:
- Create a new console application.
- Copy the following code to a new .cpp file.
- Ensure that your build directory settings reference the OLE DB SDK include directory.
- Alter the build link settings to include linking to Oledb.lib.
/********************************************************************
* Using ISequentialStream to retrieve LONG_VARCHAR/ LONG_VARBINARY
* (BLOB) data
********************************************************************/
#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID
#include <windows.h>
#include <stdio.h>
#include <oledb.h> // OLE DB include files
#include <oledberr.h>
#include <msdasql.h>
#define BLOCK_SIZE 250
// Prototypes
HRESULT myInitDSO(IDBInitialize** ppIDBI);
HRESULT mySetInitProps(IDBInitialize* pIDBInitialize);
HRESULT myCommand(IDBInitialize* pIDBI, IRowset** ppIRowset);
void myGetBLOBData(IRowset* pIRowset);
void DumpError(LPSTR lpStr);
IMalloc* g_pIMalloc = NULL;
/********************************************************************
* OLE DB application main()
********************************************************************/
int main()
{
IDBInitialize* pIDBInitialize = NULL;
IRowset* pIRowset = NULL;
//Init OLE and set up the DLLs.
CoInitialize(NULL);
// Get the task memory allocator.
if (FAILED(CoGetMalloc(MEMCTX_TASK, &g_pIMalloc)))
goto EXIT;
// Connect to the data source.
if (FAILED(myInitDSO(&pIDBInitialize)))
goto EXIT;
// Get a session, set and execute a command.
if (FAILED(myCommand(pIDBInitialize, &pIRowset)))
goto EXIT;
myGetBLOBData(pIRowset);
EXIT:
if (pIRowset != NULL)
pIRowset->Release();
if (pIDBInitialize != NULL)
{
if (FAILED(pIDBInitialize->Uninitialize()))
{
// Uninitialize is not required, but it will fail if an
// interface has not been released. We can use it for
// debugging.
DumpError("Someone forgot to release something!");
}
pIDBInitialize->Release();
}
if (g_pIMalloc != NULL)
g_pIMalloc->Release();
CoUninitialize();
return (0);
}
/********************************************************************
* Initialize the data source.
********************************************************************/
HRESULT myInitDSO
(
IDBInitialize** ppIDBInitialize // [out]
)
{
// Create an instance of the MSDASQL (ODBC) provider.
CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void**)ppIDBInitialize);
if (*ppIDBInitialize == NULL)
{
return (E_FAIL);
}
if (FAILED(mySetInitProps(*ppIDBInitialize)))
{
return (E_FAIL);
}
if (FAILED((*ppIDBInitialize)->Initialize()))
{
DumpError("IDBInitialze->Initialize failed.");
return (E_FAIL);
}
return (NOERROR);
}
/********************************************************************
* Set initialization properties on a data source.
********************************************************************/
HRESULT mySetInitProps
(
IDBInitialize* pIDBInitialize // [in]
)
{
const ULONG nProps = 4;
IDBProperties* pIDBProperties;
DBPROP InitProperties[nProps];
DBPROPSET rgInitPropSet;
HRESULT hr;
// Initialize common property options.
for (ULONG i = 0; i < nProps; i++ )
{
VariantInit(&InitProperties[i].vValue);
InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[i].colid = DB_NULLID;
}
// Level of prompting that will be done to complete the
// connection process
InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
InitProperties[0].vValue.vt = VT_I2;
InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;
// Data source name--see the sample source included with the OLE
// DB SDK.
InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal =
SysAllocString(OLESTR("OLE_DB_NWind_Jet"));
// User ID
InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR(""));
// Password
InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
InitProperties[3].vValue.vt = VT_BSTR;
InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR(""));
rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet.cProperties = nProps;
rgInitPropSet.rgProperties = InitProperties;
// Set initialization properties.
pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)
&pIDBProperties);
hr = pIDBProperties->SetProperties(1, &rgInitPropSet);
SysFreeString(InitProperties[1].vValue.bstrVal);
SysFreeString(InitProperties[2].vValue.bstrVal);
SysFreeString(InitProperties[3].vValue.bstrVal);
pIDBProperties->Release();
if (FAILED(hr))
{
DumpError("Set properties failed.");
}
return (hr);
}
/********************************************************************
* Execute a command selecting Notes from Employees.
********************************************************************/
HRESULT myCommand
(
IDBInitialize* pIDBInitialize, // [in]
IRowset** ppIRowset // [out]
)
{
IDBCreateSession* pIDBCreateSession;
IDBCreateCommand* pIDBCreateCommand;
IRowset* pIRowset;
ICommandText* pICommandText;
LPCTSTR wSQLString = OLESTR("SELECT Notes ")
OLESTR("FROM Employees");
LONG cRowsAffected;
HRESULT hr;
// Set for failure
*ppIRowset = NULL;
// Get the DB session object.
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,
(void**) &pIDBCreateSession);
if (FAILED(hr))
{
DumpError("Session initialization failed.");
return (hr);
}
// Create the session.
hr = pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand);
pIDBCreateSession->Release();
if (FAILED(hr))
{
DumpError("Create session failed.");
return (hr);
}
// Create the command object.
hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(IUnknown**) &pICommandText);
if (FAILED(hr))
{
DumpError("Create command failed.");
return (hr);
}
pIDBCreateCommand->Release();
// The command requires the actual text as well as an indicator
// of its language and dialect.
pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);
// Execute the command.
hr = pICommandText->Execute(NULL, IID_IRowset, NULL,
&cRowsAffected, (IUnknown**) &pIRowset);
if (FAILED(hr))
{
DumpError("Command execution failed.");
}
pICommandText->Release();
*ppIRowset = pIRowset;
return (hr);
}
/********************************************************************
* Retrieve data from an ODBC LONG_VARCHAR column (Notes in
* Employees).
********************************************************************/
void myGetBLOBData
(
IRowset* pIRowset // [in]
)
{
DBOBJECT ObjectStruct; // For binding, retrieve an
// object pointer.
DBBINDING rgBinding[1]; // Bind a single column.
IAccessor* pIAccessor = NULL; // Accessor creation
HACCESSOR hAccessor = NULL;
ULONG ulErrorBinding;
void* pData; // Bound consumer buffer
HROW rghRows[1];
HROW* pRows = &rghRows[0];
ULONG cRows;
char szNotes[BLOCK_SIZE + 1];// Text data from "Notes"
ULONG cbRead; // Count of bytes read
// Set up the object structure for accessor creation. Ask the
// provider to return an ISequentialStream interface for reading.
ObjectStruct.dwFlags = STGM_READ;
ObjectStruct.iid = IID_ISequentialStream;
// Set up the binding struct for the accessor.
rgBinding[0].iOrdinal = 1; // Only one column
rgBinding[0].obValue = 0; // Offset to data
rgBinding[0].obLength = 0; // Ignore length
rgBinding[0].obStatus = sizeof(IUnknown*); // Offset to status
rgBinding[0].pTypeInfo = NULL; // Reserved
rgBinding[0].pObject = &ObjectStruct; // Our interface
// request
rgBinding[0].pBindExt = NULL; // Reserved
rgBinding[0].dwPart = DBPART_VALUE | // Get both VALUE
DBPART_STATUS; // and STATUS
// parts.
rgBinding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
rgBinding[0].eParamIO = DBPARAMIO_NOTPARAM;
rgBinding[0].cbMaxLen = 0; // Not applicable
rgBinding[0].dwFlags = 0; // Reserved
rgBinding[0].wType = DBTYPE_IUNKNOWN; // Type
// DBTYPE_IUNKNOWN
rgBinding[0].bPrecision = 0; // Not applicable
rgBinding[0].bScale = 0; // Not applicable
// Get the accessor interface and create the accessor.
pIRowset->QueryInterface(IID_IAccessor, (void**) &pIAccessor);
if (FAILED(pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1,
rgBinding, sizeof(IUnknown*) + sizeof(ULONG), &hAccessor,
&ulErrorBinding)))
{
DumpError("CreateAccessor failed.");
return;
}
// Allocate memory for the returned pointer and the status field.
// The first sizeof(IUnknown*) bytes are for the pointer to the
// object; the next sizeof(ULONG) bytes are for the status.
pData = new BYTE[sizeof(IUnknown*) + sizeof(ULONG)];
while (TRUE)
{
// Get the next row.
if (FAILED(pIRowset->GetNextRows(NULL, 0, 1, &cRows,
&pRows)))
{
DumpError("GetNextRows failed.\n");
break;
}
if (cRows == 0)
{
break;
}
// Get the row data, the pointer to an ISequentialStream*.
if (FAILED(pIRowset->GetData(*pRows, hAccessor, pData)))
{
DumpError("GetData failed.\n");
break;
}
// Read and process BLOCK_SIZE bytes at a time.
if ((ULONG)((BYTE*)pData)[rgBinding[0].obStatus] ==
DBSTATUS_S_ISNULL)
{
// Process NULL data.
printf("<null>");
}
else if ((ULONG)((BYTE*)pData)[rgBinding[0].obStatus] ==
DBSTATUS_S_OK)
{
do
{
(*((ISequentialStream**) pData))->Read(szNotes,
BLOCK_SIZE, &cbRead);
if (cbRead > 0)
{
// process data
szNotes[cbRead] = (char) NULL;
printf(szNotes);
}
}
while (cbRead >= BLOCK_SIZE);
(*((ISequentialStream**) pData))->Release();
printf("\n\n");
}
pIRowset->ReleaseRows(cRows, pRows, NULL, NULL, NULL);
}
// Clean up.
pIAccessor->ReleaseAccessor(hAccessor, NULL);
pIAccessor->Release();
delete [] pData;
}
/********************************************************************
* Dump an error to the console.
********************************************************************/
void DumpError(LPSTR lpStr)
{
printf(lpStr);
printf("\n");
}
Appendix C
The following is a complete source code listing for the example presented in "Data Manipulation." The example inserts two new rows into the Customers table of the Access Northwind sample database. The database is shipped with the OLE DB SDK and is installed as the ODBC data source OLE_DB_NWind_Jet.
To build the file using Microsoft Visual C++ 4.0 or later:
- Create a new console application.
- Copy the following code to a new .cpp file.
- Ensure that your build directory settings reference the OLE DB SDK include directory.
- Alter the build link settings to include linking to Oledb.lib.
/********************************************************************
* Parameterized execution in OLE DB
********************************************************************/
#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID
#include <windows.h>
#include <stdio.h>
#include <stddef.h> // For offset of macro
#include <cguid.h> // IID_NULL
#include <oledb.h> // OLE DB include files
#include <oledberr.h>
#include <msdaguid.h>
#include <msdasql.h>
// Types--new customer struct
typedef struct tagNEWCUSTOMER
{
char acCustomerID[6];
char acCompanyName[40];
char acContactName[30];
char acContactTitle[30];
char acAddress[60];
char acCity[15];
char acRegion[15];
char acPostalCode[10];
char acCountry[15];
char acPhone[24];
char acFAX[24];
} NEWCUSTOMER;
// Types--new customer data struct
typedef struct tagNEWCUSTOMERDATA
{
PSTR szCustID;
PSTR szCompanyName;
PSTR szContactName;
PSTR szContactTitle;
PSTR szAddress;
PSTR szCity;
PSTR szRegion;
PSTR szPostalCode;
PSTR szCountry;
PSTR szPhone;
PSTR szFAX;
} NEWCUSTOMERDATA;
// Prototypes
HRESULT myInitDSO(IDBInitialize** ppIDBI);
HRESULT mySetInitProps(IDBInitialize* pIDBInitialize);
HRESULT myCreateParamAccessor(ICommand* pICmd, HACCESSOR* phAccessor,
IAccessor** ppIAccessor);
HRESULT myInsertWithParameters(IDBInitialize* pIDBInitialize);
void myDelete(IDBInitialize* pIDBInitialize);
void DumpError(LPSTR lpStr);
IMalloc* g_pIMalloc = NULL;
/********************************************************************
* main()--control flow
********************************************************************/
int main()
{
IDBInitialize* pIDBInitialize = NULL;
IRowset* pIRowset = NULL;
HRESULT hr;
// Init OLE and set up the DLLs.
CoInitialize(NULL);
// Get the task memory allocator.
if (FAILED(CoGetMalloc(MEMCTX_TASK, &g_pIMalloc)))
goto EXIT;
if (FAILED(myInitDSO(&pIDBInitialize)))
goto EXIT;
// Execute a prepared statement with parameters.
if (FAILED(myInsertWithParameters(pIDBInitialize)))
goto EXIT;
// Delete rows just added.
myDelete(pIDBInitialize);
EXIT:
// Clean up and disconnect.
if (pIRowset != NULL)
pIRowset->Release();
if (pIDBInitialize != NULL)
{
hr = pIDBInitialize->Uninitialize();
pIDBInitialize->Release();
}
if (g_pIMalloc != NULL)
g_pIMalloc->Release();
CoUninitialize();
return (0);
}
/********************************************************************
* Execute a prepared INSERT statement with parameters.
********************************************************************/
HRESULT myInsertWithParameters
(
IDBInitialize* pIDBInitialize
)
{
IDBCreateSession* pIDBCreateSession;
IDBCreateCommand* pIDBCreateCommand;
ICommandText* pICommandText;
ICommandPrepare* pICommandPrepare;
ICommandWithParameters* pICmdWithParams;
IAccessor* pIAccessor;
WCHAR wSQLString[] =
OLESTR("insert into Customers (CustomerID,
CompanyName, ContactName,")
OLESTR(" ContactTitle, Address, City, Region,
PostalCode, Country,")
OLESTR(" Phone, Fax)")
OLESTR(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
DBPARAMS Params;
long cRowsAffected;
HACCESSOR hParamAccessor;
NEWCUSTOMERDATA aNewCustomers[] =
{
"YOUME",
"You and Me Grocers",
"William Smith",
"General Manager",
"383 15th Ave. N.",
"New York",
"NY",
"10018",
"USA",
"(212) 555-8847",
"(212) 555-9988",
"YORBL",
"Yorbalinda's",
"Mary Jones",
"Owner",
"22 Sunny Vale Rd.",
"San Diego",
"CA",
"93122",
"USA",
"(605) 555-4322",
"(605) 555-4323"
};
NEWCUSTOMER NewCustomer;
ULONG nParams = 11;
DBPARAMBINDINFO rgParamBindInfo[] =
{
OLESTR("DBTYPE_CHAR"), OLESTR("CustomerID"), 5,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("CompanyName"), 40,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("ContactName"), 30,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("ContactTitle"), 30,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("Address"), 60,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("City"), 15,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("Region"), 15,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("PostalCode"), 10,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("Country"), 15,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("Phone"), 24,
DBPARAMFLAGS_ISINPUT, 0, 0,
OLESTR("DBTYPE_VARCHAR"), OLESTR("FAX"), 24,
DBPARAMFLAGS_ISINPUT, 0, 0,
};
ULONG rgParamOrdinals[] =
{1,2,3,4,5,6,7,8,9,10,11};
// Get the session.
pIDBInitialize->QueryInterface(IID_IDBCreateSession,
(void**)&pIDBCreateSession);
pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand);
pIDBCreateSession->Release();
// Create the command.
pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,
(IUnknown**) &pICommandText);
pIDBCreateCommand->Release();
// The command requires the actual text as well as an indicator
// of its language.
pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);
// Set parameter information.
pICommandText->QueryInterface(IID_ICommandWithParameters,
(void**)&pICmdWithParams);
pICmdWithParams->SetParameterInfo(nParams, rgParamOrdinals,
rgParamBindInfo);
pICmdWithParams->Release();
// Prepare the command.
pICommandText->QueryInterface(IID_ICommandPrepare, (void**)&pICommandPrepare);
if (FAILED(pICommandPrepare->Prepare(0)))
{
pICommandPrepare->Release();
pICommandText->Release();
return (E_FAIL);
}
pICommandPrepare->Release();
// Create parameter accessors.
if (FAILED(myCreateParamAccessor(pICommandText, &hParamAccessor,
&pIAccessor)))
{
pICommandText->Release();
return (E_FAIL);
}
Params.pData = &NewCustomer; // pData is the buffer pointer
Params.cParamSets = 1; // Number of sets of parameters
Params.hAccessor = hParamAccessor; // Accessor to the parameters
// Specify the parameter information.
for (UINT nCust = 0; nCust < 2; nCust++)
{
strcpy(NewCustomer.acCustomerID,
aNewCustomers[nCust].szCustID);
strcpy(NewCustomer.acCompanyName,
aNewCustomers[nCust].szCompanyName);
strcpy(NewCustomer.acContactName,
aNewCustomers[nCust].szContactName);
strcpy(NewCustomer.acContactTitle,
aNewCustomers[nCust].szContactTitle);
strcpy(NewCustomer.acAddress,
aNewCustomers[nCust].szAddress);
strcpy(NewCustomer.acCity, aNewCustomers[nCust].szCity);
strcpy(NewCustomer.acRegion, aNewCustomers[nCust].szRegion);
strcpy(NewCustomer.acPostalCode,
aNewCustomers[nCust].szPostalCode);
strcpy(NewCustomer.acCountry,
aNewCustomers[nCust].szCountry);
strcpy(NewCustomer.acPhone, aNewCustomers[nCust].szPhone);
strcpy(NewCustomer.acFAX, aNewCustomers[nCust].szFAX);
// Execute the command.
pICommandText->Execute(NULL, IID_NULL, &Params,
&cRowsAffected, NULL);
printf("%ld rows inserted.\n", cRowsAffected);
}
pIAccessor->ReleaseAccessor(hParamAccessor, NULL);
pIAccessor->Release();
pICommandText->Release();
return (NOERROR);
}
/********************************************************************
* Delete rows just added using simple execution.
********************************************************************/
void myDelete
(
IDBInitialize* pIDBInitialize
)
{
IDBCreateSession* pIDBCreateSession;
IDBCreateCommand* pIDBCreateCommand;
ICommandText* pICommandText;
WCHAR wSQLDelete1[] =
OLESTR("delete from Customers where CustomerID = 'YOYOM'");
WCHAR wSQLDelete2[] =
OLESTR("delete from Customers where CustomerID = 'YORBL'");
long cRowsAffected;
// Get the session.
pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&pIDBCreateSession);
pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
(IUnknown**) &pIDBCreateCommand);
pIDBCreateSession->Release();
// Create the command.
pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &pICommandText);
pIDBCreateCommand->Release();
// Set the command text for first delete statement and execute
// the command
pICommandText->SetCommandText(DBGUID_DBSQL, wSQLDelete1);
pICommandText->Execute(NULL, IID_NULL, NULL, &cRowsAffected, NULL);
printf("%ld rows deleted.\n", cRowsAffected);
// Do it again.
pICommandText->SetCommandText(DBGUID_DBSQL, wSQLDelete2);
pICommandText->Execute(NULL, IID_NULL, NULL, &cRowsAffected, NULL);
printf("%ld rows deleted.\n", cRowsAffected);
pICommandText->Release();
return;
}
/********************************************************************
* Create parameter accessor.
********************************************************************/
HRESULT myCreateParamAccessor
(
ICommand* pICmd, // [in]
HACCESSOR* phAccessor, // [out]
IAccessor** ppIAccessor // [out]
)
{
IAccessor* pIAccessor;
HACCESSOR hAccessor;
const ULONG nParams = 11;
DBBINDING Bindings[nParams];
DBBINDSTATUS rgStatus[nParams]; // Returns information for
// individual binding
// validity.
HRESULT hr;
ULONG acbLengths[] = {5, 40, 30, 30, 60, 15, 15, 10,
15, 24, 24};
for (ULONG i = 0; i < nParams; i++)
{
Bindings[i].iOrdinal = i + 1;
Bindings[i].obLength = 0;
Bindings[i].obStatus = 0;
Bindings[i].pTypeInfo = NULL;
Bindings[i].pObject = NULL;
Bindings[i].pBindExt = NULL;
Bindings[i].dwPart = DBPART_VALUE;
Bindings[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
Bindings[i].eParamIO = DBPARAMIO_INPUT;
Bindings[i].cbMaxLen = acbLengths[i];
Bindings[i].dwFlags = 0;
Bindings[i].wType = DBTYPE_STR;
Bindings[i].bPrecision = 0;
Bindings[i].bScale = 0;
}
Bindings[0].obValue = offsetof(NEWCUSTOMER, acCustomerID);
Bindings[1].obValue = offsetof(NEWCUSTOMER, acCompanyName);
Bindings[2].obValue = offsetof(NEWCUSTOMER, acContactName);
Bindings[3].obValue = offsetof(NEWCUSTOMER, acContactTitle);
Bindings[4].obValue = offsetof(NEWCUSTOMER, acAddress);
Bindings[5].obValue = offsetof(NEWCUSTOMER, acCity);
Bindings[6].obValue = offsetof(NEWCUSTOMER, acRegion);
Bindings[7].obValue = offsetof(NEWCUSTOMER, acPostalCode);
Bindings[8].obValue = offsetof(NEWCUSTOMER, acCountry);
Bindings[9].obValue = offsetof(NEWCUSTOMER, acPhone);
Bindings[10].obValue = offsetof(NEWCUSTOMER, acFAX);
pICmd->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
hr = pIAccessor->CreateAccessor(
DBACCESSOR_PARAMETERDATA, // Accessor that will be used
// to specify parameter data
nParams, // Number of parameters being
// bound
Bindings, // Structure containing bind
// information
sizeof(NEWCUSTOMER), // Size of parameter structure
&hAccessor, // Returned accessor handle
rgStatus // Information about binding
// validity
);
if (FAILED(hr))
{
DumpError("Parameter accessor creation failed.");
}
else
{
*ppIAccessor = pIAccessor;
*phAccessor = hAccessor;
}
return (hr);
}
/********************************************************************
* Initialize the Data Source.
********************************************************************/
HRESULT myInitDSO
(
IDBInitialize** ppIDBInitialize // [out]
)
{
// Create an instance of the MSDASQL (ODBC) provider.
CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void**)ppIDBInitialize);
if (*ppIDBInitialize == NULL)
{
return (E_FAIL);
}
if (FAILED(mySetInitProps(*ppIDBInitialize)))
{
return (E_FAIL);
}
if (FAILED((*ppIDBInitialize)->Initialize()))
{
DumpError("IDBInitialze->Initialize failed.");
return (E_FAIL);
}
return (NOERROR);
}
/********************************************************************
* Set initialization properties on a data source.
_*******************************************************************_
HRESULT mySetInitProps
(
IDBInitialize* pIDBInitialize // [in]
)
{
const ULONG nProps = 4;
IDBProperties* pIDBProperties;
DBPROP InitProperties[nProps];
DBPROPSET rgInitPropSet;
HRESULT hr;
// Initialize common property options.
for (ULONG i = 0; i < nProps; i++ )
{
VariantInit(&InitProperties[i].vValue);
InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties[i].colid = DB_NULLID;
}
// Level of prompting that will be done to complete the
// connection process
InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
InitProperties[0].vValue.vt = VT_I2;
InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;
// Data source name--see the sample source included with the OLE
// DB SDK.
InitProperties[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[1].vValue.vt = VT_BSTR;
InitProperties[1].vValue.bstrVal =
SysAllocString(OLESTR("OLE_DB_NWind_Jet"));
// User ID
InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
InitProperties[2].vValue.vt = VT_BSTR;
InitProperties[2].vValue.bstrVal = SysAllocString(OLESTR(""));
// Password
InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
InitProperties[3].vValue.vt = VT_BSTR;
InitProperties[3].vValue.bstrVal = SysAllocString(OLESTR(""));
rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
rgInitPropSet.cProperties = nProps;
rgInitPropSet.rgProperties = InitProperties;
// Set initialization properties.
pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)
&pIDBProperties);
hr = pIDBProperties->SetProperties(1, &rgInitPropSet);
SysFreeString(InitProperties[1].vValue.bstrVal);
SysFreeString(InitProperties[2].vValue.bstrVal);
SysFreeString(InitProperties[3].vValue.bstrVal);
pIDBProperties->Release();
if (FAILED(hr))
{
DumpError("Set properties failed.");
}
return (hr);
}
/********************************************************************
* Dump an error to the console.
********************************************************************/
void DumpError(LPSTR lpStr)
{
printf(lpStr);
printf("\n");
}