Export (0) Print
Expand All

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 http://www.microsoft.com/oledb/. The goal of this paper is to aid Open Database Connectivity (ODBC) programmers in understanding and using OLE DB. It discusses the relationship of ODBC calls to corresponding OLE DB methods and the related programming issues. Basic OLE DB concepts are discussed but advanced OLE DB features, such as notifications, IPersist * objects, transaction objects, and coordinated transactions, are not covered.

OLE DB and ODBC are application programming interfaces (APIs) designed to provide access to a wide range of data sources. A data source consists of the data, its associated database management system (DBMS), the platform on which the DBMS exists, and the network used to access that platform.

ODBC is designed to provide access primarily to SQL data in a multiplatform environment. OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. OLE DB includes the Structured Query Language (SQL) functionality defined in ODBC but also defines interfaces suitable for gaining access to data other than SQL data.

OLE facilitates application integration by defining a set of standard interfaces, groupings of semantically related functions through which one application accesses the services of another. Interfaces are the binary standard for component object interaction. Each interface contains a set of functions that define a contract between the object implementing the interface and the client using it.

OLE DB is designed using OLE COM; for more information about COM, see the Microsoft OLE 2 Programmer's Reference, Volume 1. Each interface consists of a set of related methods. The full functionality of OLE DB is factored into a number of different interfaces. Each data provider will implement some subset of these interfaces.

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.

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:

  1. Initializes the environment.
  2. Connects to a data source.
  3. Creates and executes a command.
  4. Processes results, if any.
  5. 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:

  1. Initializes OLE.
  2. Connects to a data source object.
  3. Creates and executes a command.
  4. Processes the results.
  5. 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:

  1. Call SQLAllocHandle to allocate a connection handle.
  2. Build a connection string containing keywords for authentication information, such as user ID, password, and the name of the data source.
  3. 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:

  1. 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.
  2. Call IDBProperties::SetProperties to set initialization properties. (For more information about properties, see "Getting and Setting Properties," below.)
  3. 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:

  1. Create an instance of the OLE DB Provider (in this case, the ODBC Provider).
  2. Set the initialization properties.
  3. 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 groupProperty group identifier (GUID)
ColumnDBPROPFLAGS_COLUMN
Data SourceDBPROPFLAGS_DATASOURCE
Data Source CreationDBPROPFLAGS_DATASOURCECREATE
Data Source InformationDBPROPFLAGS_DATASOURCEINFO
Data Source InitializationDBPROPFLAGS_DBINIT
IndexDBPROPFLAGS_INDEX
RowsetDBPROPFLAGS_ROWSET
SessionDBPROPFLAGS_SESSION
TableDBPROPFLAGS_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:

  1. Allocate an array of property structures.
  2. Allocate an array of a single property set.
  3. Initialize common property elements for the properties.
  4. 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)
  5. Set the property set to the array of properties and specify that the properties are from the initialization property group.
  6. Get the IDBProperties interface.
  7. 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:

  1. Calls SQLAllocHandle to allocate a statement.
  2. 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).
  3. Calls SQLPrepare if it wants to prepare the statement for repeated execution.
  4. Calls SQLExecute or SQLExecDirect to execute the query.

To use a command, an OLE DB consumer performs these steps:

  1. Calls IDBCreateCommand to create a command.
  2. 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.
  3. 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.
  4. Calls ICommandPrepare::Prepare if it wants to prepare the query for repeated execution.
  5. 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:

  1. Obtain the IDBCreateSession interface.
  2. Call CreateSession to create a session object that scopes the transaction boundaries within the current connection.
  3. Call CreateCommand to create a command object within the transaction.
  4. Call SetCommandText to set the command text.
  5. Call Execute to execute the command.
  6. Release the command object.
  7. 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:

  1. Calls SQLBindCol to bind the columns of the result set to storage locations, if not already done.
  2. Calls SQLFetch to move to the next row and retrieve data for all bound columns.
  3. Calls SQLGetData to retrieve data from unbound columns.

In OLE DB, the consumer performs the following functions to retrieve data:

  1. Calls IAccessor::CreateAccessor to specify binding information if not already done.
  2. Calls IRowset::GetNextRows to retrieve the next set of row handles.
  3. 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:

  1. Get a description of the rowset.
  2. Build binding structures based on the description.
  3. Obtain the IAccessor interface on the rowset object.
  4. Call CreateAccessor to create an accessor.
  5. Call GetNextRows to retrieve the next set of row handles.
  6. Call GetData to access the data for each row.
  7. Release the set of retrieved row handles.
  8. Repeat steps 4 through 6 until all the data has been retrieved.
  9. 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 valueMeaning
DBSTATUS_S_OKColumn/parameter value set/retrieved successfully.
DBSTATUS_S_ISNULLColumn/parameter value is the NULL value.
DBSTATUS_S_TRUNCATEDColumn/parameter truncated.
DBSTATUS_S_DEFAULTThe provider should use the parameter's default value.
DBSTATUS_E_BADACCESSORThe accessor used to read/write the value was invalid.
DBSTATUS_E_CANTCONVERTThe accessor specified an invalid conversion.
DBSTATUS_E_CANTCREATEThe provider could not create a storage object for large data.
DBSTATUS_E_DATAOVERFLOWThe conversion failed due to a data overflow.
DBSTATUS_E_INTEGRITYVIOLATIONThe data value violated an integrity constraint.
DBSTATUS_E_PERMISSIONDENIEDThe data value could not be set due to insufficient permissions.
DBSTATUS_E_SCHEMAVIOLATIONThe data value violated the Schema for the column.
DBSTATUS_E_SIGNMISMATCHThe data value had the incorrect sign.
DBSTATUS_E_UNAVAILABLEThe 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.

PropertyMeaning
DBPROP_CANSCROLLBACKWARDSThe rowset can return to previously read rows.
DBPROP_OWNINSERTThe rowset can see its own inserts.
DBPROP_OTHERINSERTThe rowset can see inserts made outside of the rowset.
DBPROP_OWNUPDATEDELETEThe rowset can see its own changes and deletions.
DBPROP_OTHERUPDATEDELETEThe 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:

  1. Sets the dwType element of the DBBINDING structure for the BLOB column to DBTYPE_IUNKNOWN.
  2. Sets the iid element of the DBOBJECT structure in the binding to IID_ISequentialStream.
  3. Sets the dwFlags element of the DBOBJECT structure in the binding.

To read data from a BLOB column using a storage object, the consumer:

  1. Creates an accessor that includes a binding for the column.
  2. 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.
  3. 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:

  1. Create a binding structure to retrieve the ISequentialStream interface from an OLE storage object.
  2. Obtain the IAccessor interface.
  3. Call CreateAccessor to create the accessor.
  4. Call GetNextRows to retrieve the row handles.
  5. Call GetData to retrieve the storage object from a row.
  6. Call ISequentialStream to read the data from the stream.
  7. 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:

  1. 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.
  2. 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:

  1. Calls SQLBindParameter for each parameter to specify the parameter type and bind buffers for the parameters values.
  2. Calls SQLSetStmtAttr if multiple values are to be specified for each set of parameters.
  3. Places the values in appropriate buffers.
  4. Calls SQLExecute or SQLExecDirect.

To specify parameters in OLE DB, an application uses the ICommandWithParameters interface in the following order:

  1. Creates an accessor describing the binding information for the set of parameters.
  2. Calls ICommandWithParameters::SetParameterInfo to specify the types of the parameters.
  3. 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:

  1. Create bindings describing the parameters.
  2. Obtain the IDBCreateSession interface.
  3. Call CreateSession to create a session object that scopes the transaction boundaries within the current connection.
  4. Call CreateCommand to create a command object within the transaction.
  5. Call SetCommandText to set the command text.
  6. Obtain the ICommandWithParameters interface on the command object.
  7. Call SetParameterInfo to specify the parameter information.
  8. Prepare the command.
  9. Create a parameter accessor.
  10. Build a structure containing the parameter information.
  11. Call Execute, providing the parameter accessor and parameter
  12. information, to execute the command.
  13. 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:

  1. Specify the common binding information for all the parameters.
  2. Specify the specific binding information for each parameter.
  3. Call CreateAccessor to create the parameter accessor.
  4. 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 indicatorsPhysical type
DBTYPE_EMPTY
DBTYPE_NULL 
DBTYPE_RESERVED 
DBTYPE_I1signed char
DBTYPE_I2SHORT
DBTYPE_I4LONG
DBTYPE_I8LARGE_INTEGER
DBTYPE_UI1BYTE
DBTYPE_UI2unsigned short
DBTYPE_UI4unsigned int
DBTYPE_UI8ULARGE_INTEGER
DBTYPE_R4float
DBTYPE_R8double
DBTYPE_CYLARGE_INTEGER
DBTYPE_DECIMALDBDECIMAL
DBTYPE_NUMERICDBNUMERIC
DBTYPE_DATEDATE
DBTYPE_BOOLVARIANT_BOOL
DBTYPE_BYTESBYTE[cbMaxlen]
DBTYPE_BSTRBSTR
DBTYPE_STRchar[cbMaxLen]
DBTYPE_WSTRwchar_t[cbMaxLen]
DBTYPE_VARIANTVARIANT
DBTYPE_IDISPATCHIDispatch *
DBTYPE_IUNKOWNIUnknown *
DBTYPE_GUIDGUID
DBTYPE_ERRORSCODE
DBTYPE_BYREFvoid *
DBTYPE_ARRAYSAFEARRAY *
DBTYPE_VECTORDBVECTOR
DBTYPE_UDTundefined
DBTYPE_DBDATEDBDATE
DBTYPE_DBTIMEDBTIME
DBTYPE_TIMESTAMPDBTIMESTAMP

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 attributeOLE DB property set, schema rowset, or methodOLE DB property
SQL_DESC_AUTO_

UNIQUE_VALUE

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

ISAUTOINCREMENT

SQL_DESC_BASE_

COLUMN_NAME

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

BASECOLUMNNAME

SQL_DESC_BASE_

TABLE_NAME

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

BASETABLENAME

SQL_DESC_CASE_

SENSITIVE

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

ISCASESENSITIVE

SQL_DESC_CATALOG_

NAME

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

BASECATALOGNAME

SQL_DESC_CONCISE_

TYPE

  
SQL_DESC_COUNTIColumnsInfo::GetColumnInfoThe 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::GetColumnsRowsetFIXED_PREC_SCALE column
SQL_DESC_LABELIColumnsRowset::GetColumnsRowsetDBCOLUMN_NAME
SQL_DESC_LENGTHIColumnsInfo::GetColumnInfoDerived from ulColumnSize
SQL_DESC_NAMEIColumnsRowset::GetColumnsRowsetDBCOLUMN_NAME
SQL_DESC_NULLABLEIColumnsRowset::GetColumnsRowset

IColumnsInfo::GetColumnInfo

DBCOLUMN_FLAGS or dwFlags flag DBCOLUMNFLAGS_

ISNULLABLE

SQL_DESC_NUM_PREX_

RADIX

  
SQL_DESC_OCTET_

LENGTH

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

OCTETLENGTH

SQL_DESC_PRECISIONIColumnsRowset::GetColumnsRowset

IColumnsInfo::GetColumnInfo

DBCOLUMN_

PRECISION or bPrecision

SQL_DESC_SCALEIColumnsRowset::GetColumnsRowset

IColumnsInfo::GetColumnInfo

DBCOLUMN_SCALE or bScale
SQL_DESC_SCHEMA_

NAME

IColumnsRowset::GetColumnsRowsetDBCOLUMN_

BASESCHEMANAME

SQL_DESC_SEARCHABLEIColumnsRowset::GetColumnsRowsetDBCOLUMN_

ISSEARCHABLE

SQL_DESC_TABLE_NAMEIColumnsRowset::GetColumnsRowsetDBCOLUMN_

BASETABLENAME

SQL_DESC_TYPEIColumnsRowset::GetColumnsRowset

IColumnsInfo::GetColumnInfo

DBCOLUMN_TYPE or wType
SQL_DESC_TYPE_NAMEIDBSchemaRowset::GetRowset DBSCHEMA_PROVIDER_TYPES rowset restricted by DBCOLUMN_TYPE property from IColumnsRowset::GetColumnsRowsetTYPE_NAME column
SQL_DESC_UNNAMED  
SQL_DESC_UNSIGNEDIDBSchemaRowset::GetRowset DBSCHEMA_PROVIDER_TYPES rowset restricted by DBCOLUMN_TYPE property from IColumnsRowset::GetColumnsRowsetUNSIGNED_

ATTRIBUTE column

SQL_DESC_UPDATABLEIColumnsRowset::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_MODEIDBProperties::SetPropertiesDBPROP_

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::SetPropertiesDBPROP_

CURRENTCATALOG

SQL_ATTR_LOGIN_

TIMEOUT

IDBProperties::SetPropertiesDBPROP_INIT_TIMEOUT
SQL_ATTR_QUIET_MODEIDBProperties::SetPropertiesDBPROP_INIT_HWND
SQL_ATTR_TXN_

ISOLATION

ITransactionLocal::StartTransactionisoLevel

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_ROWSICommandProperties::SetProperties

DBPROPSET_ROWSET

DBPROP_MAXROWS
SQL_ATTR_QUERY_

TIMEOUT

ICommandProperties::SetProperties

DBPROPSET_ROWSET

DBPROP_

COMMANDTIMEOUT

SQL_ATTR_RETRIEVE_

DATA

IRowset::GetDataData is not retrieved until GetData method is called on the rowset
SQL_ATTR_ROW_

ARRAY_SIZE

IRowset::GetNextRowscRows 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_DOMAINIDBInfo::GetKeywords 
SQL_ALTER_TABLEIDBInfo::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_LOCATIONIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

CATALOGLOCATION

SQL_CATALOG_NAMEIDBInfo::GetLiteralInfoDBLITERAL_CATALOG_

NAME

SQL_CATALOG_NAME_

SEPARATOR

IDBInfo::GetLiteralInfoDBLITERAL_CATALOG_

SEPARATOR

SQL_CATALOG_TERMIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_CATALOGTERM
SQL_CATALOG_USAGEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_CATALOGUSAGE
SQL_COLLATION_SEQIDBSchemaRowset::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_ASSERTIONIDBInfo::GetKeywords 
SQL_CREATE_

CHARACTER_SET

IDBInfo::GetKeywords 
SQL_CREATE_COLLATIONIDBInfo::GetKeywords 
SQL_CREATE_DOMAINIDBInfo::GetKeywords 
SQL_CREATE_SCHEMAIDBInfo::GetKeywords 
SQL_CREATE_TABLEIDBInfo::GetKeywords 
SQL_CREATE_

TRANSLATION

IDBInfo::GetKeywords 
SQL_CREATE_VIEWIDBInfo::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_NAMEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

DATASOURCENAME

SQL_DATA_SOURCE_

READ_ONLY

IDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

DATASOURCEREADONLY

SQL_DATABASE_NAMEIDBProperties::GetProperties

DBPROPSET_DATASOURCE

DBPROP_

CURRENTCATALOG

SQL_DATETIME_LITERALS  
SQL_DBMS_NAMEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_DBMSNAME
SQL_DBMS_VERIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_DBMSVER
SQL_DDL_INDEXIDBInfo::GetKeywords 
SQL_DEFAULT_TXN_

ISOLATION

 Transaction isolation is explicitly specified
SQL_DESCRIBE_

PARAMETER

  
SQL_DRIVER_NAMEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_PROVIDERNAME
SQL_DRIVER_ODBC_VERIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

PROVIDEROLEDBVER

SQL_DRIVER_VERIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_PROVIDERVER
SQL_DROP_ASSERTIONIDBInfo::GetKeywords 
SQL_DROP_CHARACTER_

SET

IDBInfo::GetKeywords 
SQL_DROP_COLLATIONIDBInfo::GetKeywords 
SQL_DROP_DOMAINIDBInfo::GetKeywords 
SQL_DROP_SCHEMAIDBInfo::GetKeywords 
SQL_DROP_TABLEIDBInfo::GetKeywords 
SQL_DROP_TRANSLATIONIDBInfo::GetKeywords 
SQL_DROP_VIEWIDBInfo::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_DIRECTIONICommandProperties::

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_BYIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_GROUPBY
SQL_IDENTIFIER_CASEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_IDENTIFIERCASE
SQL_IDENTIFIER_QUOTE_

CHAR

IDBInfo::GetLiteralInfoDBLITERAL_QUOTE
SQL_INDEX_KEYWORDSIDBInfo::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_KEYWORDSIDBInfo::GetKeywords 
SQL_LIKE_ESCAPE_

CLAUSE

IDBInfo::GetLiteralInfoDBLITERAL_ESCAPE_

PERCENT, DBLITERAL_

ESCAPE_UNDERSCORE

SQL_MAX_ASYNC_

CONCURRENT_

STATEMENTS

  
SQL_MAX_BINARY_

LITERAL_LEN

IDBInfo::GetLiteralInfoDBLITERAL_BINARY_

LITERAL

SQL_MAX_CATALOG_

NAME_LEN

IDBInfo::GetLiteralInfoDBLITERAL_CATALOG_

NAME

SQL_MAX_CHAR_

LITERAL_LEN

IDBInfo::GetLiteralInfoDBLITERAL_CHAR_LITERAL
SQL_MAX_COLUMN_

NAME_LEN

IDBInfo::GetLiteralInfoDBLITERAL_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_SIZEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_MAXINDEXSIZE
SQL_MAX_PROCEDURE_

NAME_LEN

IDBInfo::GetLiteralInfoDBLITERAL_PROCEDURE_

NAME

SQL_MAX_ROW_SIZEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_MAXROWSIZE
SQL_MAX_ROW_SIZE_

INCLUDES_LONG

IDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

MAXROWSIZEINCLUDES-BLOB

SQL_MAX_STATEMENT_

LEN

IDBInfo::GetLiteralInfoDBLITERAL_TEXT_

COMMAND

SQL_MAX_TABLE_NAME_

LEN

IDBInfo::GetLiteralInfoDBLITERAL_TABLE_NAME
SQL_MAX_TABLES_IN_

SELECT

IDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

MAXTABLESINSELECT

SQL_MAX_USER_NAME_

LEN

IDBInfo::GetLiteralInfoDBLITERAL_USER_NAME
SQL_MULT_RESULT_SETSIDBProperties::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_COLLATIONIDBProperties::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_TERMIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_SCHEMATERM
SQL_OWNER_USAGEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_SCHEMAUSAGE
SQL_PARAM_ARRAY_

ROW_COUNTS

  
SQL_PARAM_ARRAY_

SELECTS

  
SQL_PROCEDURE_TERMIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

PROCEDURETERM

SQL_PROCEDURESIDBSchemaRowset::GetSchemas returns GUID for PROCEDURES schema rowset 
SQL_QUOTED_IDENTIFIER_

CASE

IDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

QUOTEDIDENTIFIERCASE

SQL_ROW_UPDATESICommandProperties::

GetProperties

DBPROPSET_ROWSET

DBPROP_IRowsetResynch
SQL_SCHEMA_TERMIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_SCHEMATERM
SQL_SCHEMA_USAGEIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_SCHEMAUSAGE
SQL_SCROLL_OPTIONSICommandProperties::

GetProperties

DBPROPSET_ROWSET

DBPROP_

CANSCROLLBACKWARDS, DBPROP_OTHERINSERT, DBPROP_

OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_

OWNUPDATEDELETE

SQL_SEARCH_PATTERN_

ESCAPE

  
SQL_SERVER_NAMEIDBProperties::GetProperties

DBPROPSET_INIT

DBPROP_INIT_LOCATION
SQL_SPECIAL_

CHARACTERS

IDBInfo::GetLiteralInfoValue 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_SUBQUERIESIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_SUBQUERIES
SQL_SYSTEM_FUNCTIONS  
SQL_TABLE_TERMIDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_TABLETERM
SQL_TIMEDATE_ADD_

INTERVALS

  
SQL_TIMEDATE_DIFF_

INTERVALS

  
SQL_TIMEDATE_

FUNCTIONS

  
SQL_TXN_CAPABLEIUnknown::QueryInterface on session for IID_ITransactionLocal 
SQL_TXN_ISOLATION_

OPTION

IDBProperties::GetProperties

DBPROPSET_DATASOURCEINFO

DBPROP_

SUPPORTED-TXNISORETAIN

SQL_UNION  
SQL_USER_NAMEIDBProperties::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 functionOLE DB methodRemarks/property
SQLAllocHandle

Environment

Connection

Descriptor

Statement

OleInitialize

CoCreateInstance

IAccessor::CreateAccessor

IDBCreateCommand::CreateCommand

 
SQLBindColIAccessor::CreateAccessor 
SQLBindParameterIAccessor::CreateAccessor 
SQLBrowseConnectISourcesRowset::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
SQLCancelICommand::Cancel 
SQLCloseCursorIRowset::Release 
SQLColAttributeIColumnsInfo::GetColumnInfo or IColumnsRowset::GetColumnsRowset 
SQLColumnPrivilegesIDBSchemaRowset::GetRowsetDBSCHEMA_

COLUMN_PRIVILEGES

SQLColumnsIDBSchemaRowset::GetRowsetDBSCHEMA_COLUMNS
SQLConnect1. IDBProperties::SetProperties

2. IDBInitialize::Initialize

 
SQLDataSourcesISourcesRowset::GetSourcesRowset 
SQLDescribeColIColumnsInfo::GetColumnInfo 
SQLDescribeParam ICommandWithParameters::

GetParameterInfo

 
SQLDisconnectIDBInitialize::Uninitialize 
SQLDriverConnect1. IDBProperties::SetProperties

2. IDBInitialize::Initialize

 
SQLDrivers ISourcesRowset::GetSourcesRowset 
SQLEndTranITransaction::Commit or ITransaction::Abort 
SQLError1. GetErrorInfo

2. IErrorRecords

3. IErrorInfo

 
SQLExecDirectICommand::Execute 
SQLExecuteICommand::Execute 
SQLFetch 1. IRowset::GetNextRows,

IRowsetLocate::GetRowsAt, or

IRowsetLocate::GetRowsByBookmark

2. IRowset::GetData

 
SQLFetchScroll1. IRowset::GetNextRows,

IRowsetLocate::GetRowsAt, or

IRowsetLocate::GetRowsByBookmark

2. IRowset::GetData

 
SQLForeignKeysIDBSchemaRowset::GetRowsetDBSCHEMA_

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

 
SQLGetConnectAttrIDBProperties::GetProperties 
SQLGetCursorName Not applicableOLE DB performs updates through update methods, as opposed to positioned cursor operations
SQLGetDataIRowset::GetData

(for all data except BLOBs) or

ILockBytes, ISequentialStream, IStorage, or IStream (for BLOB data)

 
SQLGetDescFieldIAccessor::GetBindingsElements within binding structure
SQLGetDescRecIAccessor::GetBindingsElements within binding structure
SQLDiagField1. GetErrorInfo

2. IErrorRecords

3. IErrorInfo

 
SQLDiagRec1. GetErrorInfo

2. IErrorRecords

3. IErrorInfo

 
SQLGetEnvAttrIDBProperties::GetProperties 
SQLGetFunctionsIUnknown::QueryInterface 
SQLGetInfoIDBProperties::GetProperties 
SQLGetStmtAttrICommandProperties::GetProperties or IRowsetInfo::GetProperties 
SQLGetTypeInfoIDBSchemaRowset::GetRowsetDBSCHEMA_

PROVIDER_TYPES

SQLMoreResults IMultipleResults 
SQLNativeSql ICommandText::GetCommandTextWith NULL as pguidDialect
SQLNumParams ICommandWithParams::GetParameterInfo 
SQLNumResultCols IColumnsInfo::GetColumnInfo 
SQLParamData Not applicable 
SQLPrepareICommand::Prepare 
SQLPrimaryKeysIDBSchemaRowset::GetRowsetDBSCHEMA_

PRIMARY_KEYS

SQLProcedureColumnsIDBSchemaRowset::GetRowsetDBSCHEMA_

PROCEDURE_PARAMETERS, DBSCHEMA_

PROCEDURE_COLUMNS

SQLProceduresIDBSchemaRowset::GetRowsetDBSCHEMA_PROCEDURES
SQLPutData IRowsetChange::SetData

(for all data except BLOBs) or

ISequentialStream (for BLOB data)

 
SQLRowCountcRowsAffected in ICommand::Execute 
SQLSetConnectAttrIDBProperties::SetProperties 
SQLSetCursorName Not applicableOLE DB performs updates through update methods, as opposed to positioned cursor operations
SQLSetDescFieldIAccessor::CreateAccessorElements within binding structure
SQLSetDescRecIAccessor::CreateAccessorElements within binding structure
SQLSetEnvAttrIDBProperties::SetProperties 
SQLSetParamICommandWithParameters::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

 
SQLSetStmtAttrICommandProperties::SetProperties 
SQLSpecialColumnsIColumnsInfo::GetColumnInfoDBCOLUMNFLAGS_

ISROWID and DBCOLUMNFLAGS_

ISROWVER

SQLStatisticsIDBSchemaRowset::GetRowsetDBSCHEMA_STATISTICS
SQLTablePrivilegesIDBSchemaRowset::GetRowsetDBSCHEMA_TABLE_

PRIVILEGES

SQLTablesIDBSchemaRowset::GetRowsetDBSCHEMA_TABLES
SQLTransactITransaction::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:

  1. Create a new console application.
  2. Copy the following code to a new .cpp file.
  3. Ensure that your build directory settings reference the OLE DB SDK include directory.
  4. 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:

  1. Create a new console application.
  2. Copy the following code to a new .cpp file.
  3. Ensure that your build directory settings reference the OLE DB SDK include directory.
  4. 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:

  1. Create a new console application.
  2. Copy the following code to a new .cpp file.
  3. Ensure that your build directory settings reference the OLE DB SDK include directory.
  4. 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");
    }

Show:
© 2014 Microsoft