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:

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:

OLE DB consumers can:

The following table shows the property groups in OLE DB and their GUIDs.

Property group Property group identifier (GUID)
Column DBPROPFLAGS_COLUMN
Data Source DBPROPFLAGS_DATASOURCE
Data Source Creation DBPROPFLAGS_DATASOURCECREATE
Data Source Information DBPROPFLAGS_DATASOURCEINFO
Data Source Initialization DBPROPFLAGS_DBINIT
Index DBPROPFLAGS_INDEX
Rowset DBPROPFLAGS_ROWSET
Session DBPROPFLAGS_SESSION
Table DBPROPFLAGS_TABLE

The following structure contains an array of values of properties from a single property set:

typedef struct  tagDBPROPSET
    {
    DBPROP __RPC_FAR* rgProperties;    // Pointer to an array of 
                                       // DBPROP structures.
    ULONG             cProperties;     // Count of properties
                                       // (DBPROPS) in the array.
    GUID              guidPropertySet; // A GUID that identifies the
                                       // property set to which the 
                                       // properties belong.
    } DBPROPSET;

The following structure contains information about a single property:

typedef struct  tagDBPROP
    {
    DBPROPID          dwPropertyID;    // ID of property within a
                    // property set.
    DBPROPOPTIONS     dwOptions;    // Property is required?
                    // Optional?
    DBPROPSTATUS      dwStatus;    // Status returned by the
                    // provider indicating success 
                    // or failure in setting or 
                    // getting the property. 
                    // Enumerated values are: 
                    //  DBPROPSTATUS_OK
                    //  DBPROPSTATUS_NOTSUPPORTED
                    //  DBPROPSTATUS_BADVALUE
                    //  DBPROPSTATUS_BADOPTION
                    //  DBPROPSTATUS_BADCOLUMN
                    //  DBPROPSTATUS_NOTALLSETTABLE
                    //  DBPROPSTATUS_NOTSET
                    //  DBPROPSTATUS_NOTSETTABLE
                    //  DBPROPSTATUS_CONFLICTING
    DBID              colid;        // Optional, ordinal column
                    // property applies to. If the 
                    // property applies to all 
                    // columns, colid should be set 
                    // to DB_NULLID.
    VARIANT           vValue;        // Value of the property.
    } DBPROP;

The following code example shows how an application sets initialization properties on a data source object. The code sets four properties within a single property group. The general flow of control is:

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

OLE DB rowsets are not only designed to provide a high performance solution for the preceding scenarios but are also designed so that:

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:

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:

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:

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:

In OLE DB, consumers create reusable accessor handles that specify binding information for a set of columns. To use an accessor, the consumer:

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:

It is possible to have columns that are deferred. For a deferred column, the provider is not required to retrieve data until IRowset::GetData is called for that column or, if the column contains an OLE object, until a method used to access the object is called. The advantage of deferred columns is that data retrieval can be postponed until the data is needed, which is very beneficial when the data is large.

The following structure defines the information specified for each column bound by an accessor.

typedef struct tagDBBINDING
    {
    ULONG iOrdinal;            // Ordinal of column or
                    // parameter to bind
    ULONG obValue;            // Offset in consumer's buffer
                    // for value.
    ULONG obLength;            // Offset in consumer's buffer
                    // for length of value.
    ULONG obStatus;            // Offset in consumer's buffer
                    // for status of operation.
    ITypeInfo __RPC_FAR* pTypeInfo;    // Reserved, should be NULL.
    DBOBJECT __RPC_FAR* pObject;    // Pointer to object structure
                    // Structure describes access 
                    // to OLE objects in the 
                    // column.
    DBBINDEXT __RPC_FAR* pBindExt;    // Reserved, should be NULL.
    DBPART dwPart;            // Enumerated parts to bind 
                    // (value/length/status)
    DBMEMOWNER dwMemOwner;        // Memory is owned by consumer?
                    // Provider?
    DBPARAMIO eParamIO;        // Parameter type
                    // (input/output/not a 
                    // parameter)
    ULONG cbMaxLen;            // Size of consumer's value
                    // buffer if memory is consumer 
                    // owned
    DWORD dwFlags;            // Reserved, should be 0.
    DBTYPE wType;            // Data type indicator
    BYTE bPrecision;            // Precision for
                    // numeric/decimal data types
    BYTE bScale;            // Scale for numeric/decimal
                    // data types
    } DBBINDING;

The provider returns column or output parameter data to the consumer and also sets the status value of each column or output parameter.

Status value Meaning
DBSTATUS_S_OK Column/parameter value set/retrieved successfully.
DBSTATUS_S_ISNULL Column/parameter value is the NULL value.
DBSTATUS_S_TRUNCATED Column/parameter truncated.
DBSTATUS_S_DEFAULT The provider should use the parameter's default value.
DBSTATUS_E_BADACCESSOR The accessor used to read/write the value was invalid.
DBSTATUS_E_CANTCONVERT The accessor specified an invalid conversion.
DBSTATUS_E_CANTCREATE The provider could not create a storage object for large data.
DBSTATUS_E_DATAOVERFLOW The conversion failed due to a data overflow.
DBSTATUS_E_INTEGRITYVIOLATION The data value violated an integrity constraint.
DBSTATUS_E_PERMISSIONDENIED The data value could not be set due to insufficient permissions.
DBSTATUS_E_SCHEMAVIOLATION The data value violated the Schema for the column.
DBSTATUS_E_SIGNMISMATCH The data value had the incorrect sign.
DBSTATUS_E_UNAVAILABLE The data was not available.

The following code example shows traversing a DBCOLUMNINFO structure array obtained from a rowset and creating a set of bindings based on that information. An accessor is created from the binding structure array in the previous code sample.

/********************************************************************
* Create binding structures from column information. Binding
* structures will be used to create an accessor that allows row value 
* retrieval.
********************************************************************/
void myCreateDBBindings
    (
    ULONG nCols,                 // [in]
    DBCOLUMNINFO* pColumnsInfo,  // [in]
    DBBINDING** ppDBBindings,    // [out]
    char** ppRowValues           // [out]
    )
    {
    ULONG       nCol;
    ULONG       cbRow = 0;
    DBBINDING*  pDBBindings;
    char*       pRowValues;

    pDBBindings = new DBBINDING[nCols];

    for (nCol = 0; nCol < nCols; nCol++)
        {
        pDBBindings[nCol].iOrdinal = nCol+1;
        pDBBindings[nCol].obValue = cbRow;
        pDBBindings[nCol].obLength = 0;
        pDBBindings[nCol].obStatus = 0;
        pDBBindings[nCol].pTypeInfo = NULL;
        pDBBindings[nCol].pObject = NULL;
        pDBBindings[nCol].pBindExt = NULL;
        pDBBindings[nCol].dwPart = DBPART_VALUE;
        pDBBindings[nCol].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        pDBBindings[nCol].eParamIO = DBPARAMIO_NOTPARAM;
        pDBBindings[nCol].cbMaxLen = pColumnsInfo[nCol].ulColumnSize;
        pDBBindings[nCol].dwFlags = 0;
        pDBBindings[nCol].wType = pColumnsInfo[nCol].wType;
        pDBBindings[nCol].bPrecision = pColumnsInfo[nCol].bPrecision;
        pDBBindings[nCol].bScale = pColumnsInfo[nCol].bScale;

        cbRow += pDBBindings[nCol].cbMaxLen;
        }

    pRowValues = new char[cbRow];

    *ppDBBindings = pDBBindings;
    *ppRowValues = pRowValues;

    return;
    }

Advanced Rowset Topics

OLE DB rowset interfaces and properties expose database cursor and bookmark support to the developer. In addition, OLE DB exposes support for client use of database resources such as scrollbar interface support.

The OLE DB cursor model is derived from that implemented in ODBC and includes support for cursor type, cursor scrolling capability, and transaction isolation levels.

Block cursors

OLE DB supports block cursors by specifying the number of rows desired in the IRowset::GetNextRows method. In ODBC, a block cursor is defined by specifying the rowset size using the SQL_ATTR_ROW_ARRAY_SIZE attribute in a call to SQLSetStmtAttr prior to calling SQLFetchScroll.

Sequential rowsets

Sequential rowsets do not support locating specific rows, or fetching rows already retrieved. GetNextRows can retrieve one or more rows, but the rows must all be released before another GetNextRows call is permitted. This restriction is known as strict sequential cursor. This is similar to the forward-only cursor supported by ODBC. A more flexible model, relaxed sequential, allows prior rows to be held, but GetNextRows is still sequential, and the consumer cannot revisit earlier parts of the rowset. The relaxed sequential model also allows changes and updates to rows that have already been passed but have been held. Sequential rowsets do not implement the IRowsetLocate interface.

GetNextRows is positional; that is, it fetches rows sequentially, at all times keeping track of the previous position. It can move forward, or backward if the rowset supports reversible direction. IRowset::RestartPosition repositions the next fetch position to the start of the rowset. GetNextRows keeps track of the next fetch position so that a sequence of calls to this method with no skips and no change of direction reads the entire rowset. This position is not connected to or disturbed by any other method that gets rows. Rowsets that support the property DBPROP_CANFETCHBACKWARDS can take a negative number for the count of requested rows. If the count of requested rows is negative, then the fetch direction is backwards. GetNextRows increases the reference count of each row for which it returns a handle. ReleaseRows must be called to release the handles that have been retrieved.

GetNextRows is the same as calling SQLFetch in ODBC with no columns bound, except that it can move forward or backward relative to the current position and can return multiple row handles.

Scrollable rowsets

Scrollable rowsets support IRowsetLocate. IRowsetLocate is equivalent to ODBC SQLFetchScroll. Scrollable rowsets are not positional; the consumer uses bookmarks to fetch relative to a previous position.

IRowsetLocate supports the following scrolling options:

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:

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:

Cursor types

OLE DB support for scrollable cursors is provided and determined by the values of the rowset properties.

Property Meaning
DBPROP_CANSCROLLBACKWARDS The rowset can return to previously read rows.
DBPROP_OWNINSERT The rowset can see its own inserts.
DBPROP_OTHERINSERT The rowset can see inserts made outside of the rowset.
DBPROP_OWNUPDATEDELETE The rowset can see its own changes and deletions.
DBPROP_OTHERUPDATEDELETE The rowset can see changes and deletions made outside of the rowset.

These options are used to specify the characteristics of the static, keyset, and dynamic cursors defined in ODBC as follows: