Executing statements with bound parameters

This section describes how to construct and execute a SQL statement, using bound parameters to set values for statement parameters at runtime.

Create a Command object from the session:

ICommandText* pICommandText;
hr = pIDBCreateCommand->CreateCommand(
     NULL, IID_ICommandText,
     (IUnknown**)&pICommandText);

Set the SQL statement you want to execute:

hr = pICommandText->SetCommandText(
     DBGUID_DBSQL,
     L"DELETE FROM department WHERE dept_id = ?");

Create an array to describe the parameters:

DB_UPARAMS paramOrdinal[1] = { 1 };
DBPARAMBINDINFO paramBindInfo[1] = {
     {
           L"DBTYPE_I4",
           NULL,
           sizeof(int),
           DBPARAMFLAGS_ISINPUT,
            0,
          0
     }
};

Get the ICommandWithParameters interface from the Command object. Set the parameter information for this command:

ICommandWithParameters* pi;
hr = pICommandText->QueryInterface(
     IID_ICommandWithParameters, (void**)&pi);
hr = pi->SetParameterInfo(1, rgParamOrdinals, rgParamBindInfo);
pi->Release();

The following is a structure that holds the data for all of the parameters. In this case, there is a single int parameter, as shown:

struct Parameters {
     int dept_id;
};

The following array describes the fields in the parameters structure:

static DBBINDING ExactBindingsParameters [1] = {
{
     1,           // iOrdinal
     offsetof (Parameters,dept_id), // obValue
     0,           // No length binding
     0,           // No Status binding
     NULL,  // No TypeInfo
     NULL,  // No Object
     NULL,    // No Extensions
     DBPART_VALUE,
     DBMEMOWNER_CLIENTOWNED,   // Ignored
     DBPARAMIO_INPUT,
     sizeof (int),
     0,
     DBTYPE_I4,
     0,              // No Precision
      0                  // No Scale
}
};

The following interface is the IAccessor interface from the Command object:

IAccessor* pIAccessor;
hr = pICommandText->QueryInterface(
     IID_IAccessor, (void**)&pIAccessor);

Create an accessor on the Command object for the pararameters:

DBBINDSTATUS status[1];
HACCESSOR hAccessor;
HRESULT hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,
     1, ExactBindingsParameters, sizeof(ExactBindingsParameters),
     &hAccessor, status);
pIAccessor->Release();

Create an array of parameters. Each element in the array is a complete set of parameters. The Execute method executes the SQL statement once for each parameter set in the array, as shown:

Parameters param = { 1 };
DBPARAMS params[1] = {
     {
          &param,
          1,
           hAccessor
     }};

Execute the command:

DBROWCOUNT cRowsAffected;
IRowset* pIRowset;
hr = pICommandText->Execute(
     NULL, IID_IRowset, params,
     &cRowsAffected, (IUnknown**)&pIRowset);