Data Manipulation

Data manipulation in OLE DB can be executed using SQL queries in commands or using the IRowsetChange interface.

IRowsetChange allows the consumer to:

Update columns of a row by calling IRowsetChange::SetData.
Delete a row or rows by calling IRowsetChange::DeleteRows.
Insert a new row by calling IRowsetChange::InsertRow.

Delayed Updates

OLE DB supports delayed updates. With delayed updates, changes made to the rowset are not transmitted to the data source until IRowsetUpdate::Update is called.

A rowset can be in delayed or immediate update mode, depending on whether it exposes the IRowsetUpdate interface. The consumer specifies whether IRowsetUpdate should be supported prior to opening the rowset.

If the IRowsetUpdate interface is not included on the rowset, then the rowset is said to be in immediate update mode, and the changes are immediately transmitted to the data source. If IRowsetUpdate is present, then the changes are not transmitted to the data source until IRowsetUpdate::Update is called.

For rowsets in delayed update mode, IRowsetUpdate allows the consumer to:

Retrieve the initial value read for the row by calling IRowsetUpdate::GetOriginalData.
Get a list of pending changes by calling IRowsetUpdate::GetPendingRows.
Get the pending status of a given set of rows by calling IRowsetUpdate::GetRowStatus.
Undo some, or all, of the pending changes by calling IRowsetUpdate::Undo.
Commit some, or all, of the changes to the data source by calling IRowsetUpdate::Update.

Prepared commands

If a command is to be executed multiple times, it is often more efficient to prepare it. Command preparation tells the query processor to save the execution plan so that it doesn't have to be rebuilt for each execution.

The ICommandPrepare interface supports methods for preparing a command. Calling ICommandPrepare::Prepare in OLE DB is equivalent to calling SQLPrepare in ODBC.

Commands with parameters

Parameters are used to insert scalar values into a command at execute time. Parameters are generally used in conjunction with a prepared command so that the command can be executed multiple times, each time with a different value.

To specify parameters in ODBC, an application uses SQLBindParameter and SQLParamData in the following order:

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

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

Creates an accessor describing the binding information for the set of parameters.
Calls ICommandWithParameters::SetParameterInfo to specify the types of the parameters.
Calls ICommand::Execute to execute the command, passing a structure containing the accessor, number of parameter sets, and a pointer to data for the parameters.

Binding parameters

The consumer specifies parameter descriptions by setting information in the DBPARAMBINDINFO structure passed to ICommandWithParameters::SetParameterInfo. This is similar to the type, precision, and scale information specified in SQLBindParameter in ODBC.

The DBPARAMBINDINFO structure is:

typedef struct tagDBPARAMBINDINFO
    {
    LPOLESTR      pwszDataSourceType;    // Data type name (OLESTR)
    LPOLESTR      pwszName;        // Name of the parameter
    ULONG         ulParamSize;    // Maximum length of data
                    // accepted
    DBPARAMFLAGS  dwFlags;    // Input/output/signed/nullable/object
    BYTE          bPrecision;    // Precision for numeric data
                // types
    BYTE          bScale;    // Scale for numeric data types
    } DBPARAMBINDINFO;

The ICommand::Execute method takes a pointer to the DBPARAMS structure as an argument. This structure includes a pointer to the data as well as an accessor that describes the layout of the data. Bindings described in the accessor are similar to the bindings specified in SQLBindParameter in ODBC. OLE DB allows the specification of multiple sets of parameters in a single call by specifying the number of sets of parameters in the cParamSets element of the DBPARAMS structure. This is similar to calling SQLSetStmtAttr in ODBC.

The DBPARAMS structure is:

typedef struct tagDBPARAMS
    {
    void __RPC_FAR*  pData;        // Data, array containing
                    // parameter values
    ULONG            cParamSets;    // Count of sets of parameter
                    // values in the data array
    HACCESSOR        hAccessor;    // Handle of parameter
                    // describing accessor
    } DBPARAMS;

The following code example shows parameter passing in OLE DB. For the complete source code listing, see Appendix C. The general flow of control is:

Create bindings describing the parameters.
Obtain the IDBCreateSession interface.
Call CreateSession to create a session object that scopes the transaction boundaries within the current connection.
Call CreateCommand to create a command object within the transaction.
Call SetCommandText to set the command text.
Obtain the ICommandWithParameters interface on the command object.
Call SetParameterInfo to specify the parameter information.
Prepare the command.
Create a parameter accessor.
Build a structure containing the parameter information.
Call Execute, providing the parameter accessor and parameter
information, to execute the command.

Release the command object.

/********************************************************************
*  Execute a prepared INSERT statement with parameters.
********************************************************************/
HRESULT myInsertWithParameters
    (
    IDBInitialize*  pIDBInitialize
    ) 
    {
    IDBCreateSession*   pIDBCreateSession;
    IDBCreateCommand*   pIDBCreateCommand;
    ICommandText*       pICommandText;
    ICommandPrepare*    pICommandPrepare;
    ICommandWithParameters* pICmdWithParams;
    IAccessor*          pIAccessor;
    WCHAR               wSQLString[] = 
                OLESTR("insert into Customers (CustomerID,
                      CompanyName, ContactName,")
                OLESTR(" ContactTitle, Address, City, Region, 
                      PostalCode, Country,")
                OLESTR(" Phone, Fax)")
                OLESTR(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    DBPARAMS            Params;
    long                cRowsAffected;
    HACCESSOR           hParamAccessor;

    NEWCUSTOMERDATA     aNewCustomers[] =
        {
        "YOUME",
        "You and Me Grocers",
        "William Smith",
        "General Manager",
        "383 15th Ave. N.",
        "New York",
        "NY",
        "10018",
        "USA",
        "(212) 555-8847",
        "(212) 555-9988",

        "YORBL",
        "Yorbalinda's",
        "Mary Jones",
        "Owner",
        "22 Sunny Vale Rd.",
        "San Diego",
        "CA",
        "93122",
        "USA",
        "(605) 555-4322",
        "(605) 555-4323"
        };
    NEWCUSTOMER         NewCustomer;

    ULONG               nParams = 11;
    DBPARAMBINDINFO     rgParamBindInfo[] = 
        {
        OLESTR("DBTYPE_CHAR"),    OLESTR("CustomerID"),    5, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("CompanyName"),  40,
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("ContactName"),  30, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("ContactTitle"), 30, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("Address"),      60, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("City"),         15, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("Region"),       15, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("PostalCode"),   10, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("Country"),      15, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("Phone"),        24, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        OLESTR("DBTYPE_VARCHAR"), OLESTR("FAX"),          24, 
             DBPARAMFLAGS_ISINPUT, 0, 0,
        };
    ULONG               rgParamOrdinals[] = 
                             {1,2,3,4,5,6,7,8,9,10,11};

    // Get the session.
    pIDBInitialize->QueryInterface(IID_IDBCreateSession,
        (void**)&pIDBCreateSession);
    pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
        (IUnknown**) &pIDBCreateCommand);
    pIDBCreateSession->Release();

    // Create the command.
    pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, 
        (IUnknown**) &pICommandText);
    pIDBCreateCommand->Release();

    // The command requires the actual text as well as an indicator 
    // of its language.
    pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);

    // Set parameter information.
    pICommandText->QueryInterface(IID_ICommandWithParameters, 
        (void**)&pICmdWithParams);
    pICmdWithParams->SetParameterInfo(nParams, rgParamOrdinals, 
        rgParamBindInfo);
    pICmdWithParams->Release();

    // Prepare the command.
    pICommandText->QueryInterface(IID_ICommandPrepare, 
        (void**)&pICommandPrepare);
    if (FAILED(pICommandPrepare->Prepare(0)))
        {
        pICommandPrepare->Release();
        pICommandText->Release();
        return (E_FAIL);
        }
    pICommandPrepare->Release();

    // Create parameter accessors.
    if (FAILED(myCreateParamAccessor(pICommandText, &hParamAccessor, 
        &pIAccessor)))
        {
        pICommandText->Release();
        return (E_FAIL);
        }

    Params.pData = &NewCustomer;      // pData is the buffer pointer
    Params.cParamSets = 1;            // Number of sets of parameters
    Params.hAccessor = hParamAccessor;// Accessor to the parameters
    
    // Specify the parameter information.
    for (UINT nCust = 0; nCust < 2; nCust++)
        {
        strcpy(NewCustomer.acCustomerID, 
             aNewCustomers[nCust].szCustID);
        strcpy(NewCustomer.acCompanyName, 
             aNewCustomers[nCust].szCompanyName);
        strcpy(NewCustomer.acContactName, 
             aNewCustomers[nCust].szContactName);
        strcpy(NewCustomer.acContactTitle, 
             aNewCustomers[nCust].szContactTitle);
        strcpy(NewCustomer.acAddress, 
             aNewCustomers[nCust].szAddress);
        strcpy(NewCustomer.acCity, aNewCustomers[nCust].szCity);
        strcpy(NewCustomer.acRegion, aNewCustomers[nCust].szRegion);
        strcpy(NewCustomer.acPostalCode, 
             aNewCustomers[nCust].szPostalCode);
        strcpy(NewCustomer.acCountry, 
             aNewCustomers[nCust].szCountry);
        strcpy(NewCustomer.acPhone, aNewCustomers[nCust].szPhone);
        strcpy(NewCustomer.acFAX, aNewCustomers[nCust].szFAX);

        // Execute the command.
        pICommandText->Execute(NULL, IID_NULL, &Params, 
             &cRowsAffected, NULL);

        printf("%ld rows inserted.\n", cRowsAffected);
        }

    pIAccessor->ReleaseAccessor(hParamAccessor, NULL);
    pIAccessor->Release();
    pICommandText->Release();

    return (NOERROR);
    }

Creating parameter accessors

Input parameter data is read from, and output parameter data is written to, the specified locations within Params.pData according to the bindings specified by the accessor. An array of parameter sets can be passed in pParamData. cParamSets indicates the number of elements of the array.

The following code example shows parameter binding for the NEWCUSTOMER structure used in the previous example. The general flow of control is:

Specify the common binding information for all the parameters.
Specify the specific binding information for each parameter.
Call CreateAccessor to create the parameter accessor.

Return the accessor handle along with the interface used to create it (so that it can be freed later).

/********************************************************************
*  Create parameter accessor.
********************************************************************/
HRESULT myCreateParamAccessor
    (
    ICommand*   pICmd,      // [in]
    HACCESSOR*  phAccessor, // [out]
    IAccessor** ppIAccessor // [out]
    )
    {
    IAccessor*      pIAccessor;
    HACCESSOR       hAccessor;
    const ULONG     nParams = 11;
    DBBINDING       Bindings[nParams];
    DBBINDSTATUS    rgStatus[nParams]; // Returns information for 
                                       // individual binding
                                       // validity.
    HRESULT         hr;

    ULONG           acbLengths[] = {5, 40, 30, 30, 60, 15, 15, 10, 
                                    15, 24, 24};

    for (ULONG i = 0; i < nParams; i++)
        {
        Bindings[i].iOrdinal = i + 1;
        Bindings[i].obLength = 0;
        Bindings[i].obStatus = 0;
        Bindings[i].pTypeInfo = NULL;
        Bindings[i].pObject = NULL;
        Bindings[i].pBindExt = NULL;
        Bindings[i].dwPart = DBPART_VALUE;
        Bindings[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        Bindings[i].eParamIO = DBPARAMIO_INPUT;
        Bindings[i].cbMaxLen = acbLengths[i];
        Bindings[i].dwFlags = 0;
        Bindings[i].wType = DBTYPE_STR;
        Bindings[i].bPrecision = 0;
        Bindings[i].bScale = 0;
        }

    Bindings[0].obValue = offsetof(NEWCUSTOMER, acCustomerID);
    Bindings[1].obValue = offsetof(NEWCUSTOMER, acCompanyName);
    Bindings[2].obValue = offsetof(NEWCUSTOMER, acContactName);
    Bindings[3].obValue = offsetof(NEWCUSTOMER, acContactTitle);
    Bindings[4].obValue = offsetof(NEWCUSTOMER, acAddress);
    Bindings[5].obValue = offsetof(NEWCUSTOMER, acCity);
    Bindings[6].obValue = offsetof(NEWCUSTOMER, acRegion);
    Bindings[7].obValue = offsetof(NEWCUSTOMER, acPostalCode);
    Bindings[8].obValue = offsetof(NEWCUSTOMER, acCountry);
    Bindings[9].obValue = offsetof(NEWCUSTOMER, acPhone);
    Bindings[10].obValue = offsetof(NEWCUSTOMER, acFAX);

    pICmd->QueryInterface(IID_IAccessor, (void**)&pIAccessor);

    hr = pIAccessor->CreateAccessor(
        DBACCESSOR_PARAMETERDATA,    // Accessor that will be used 
                    // to specify parameter data
        nParams,            // Number of parameters being
                    // bound
        Bindings,            // Structure containing bind
                    // information
        sizeof(NEWCUSTOMER),        // Size of parameter structure
        &hAccessor,            // Returned accessor handle
        rgStatus            // Information about binding
                    // validity
        );

    if (FAILED(hr))
        {
        DumpError("Parameter accessor creation failed.");
        }
    else
        {
        *ppIAccessor = pIAccessor;
        *phAccessor = hAccessor;
        }

    return (hr);
    }