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