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");
    }