Executing statements with bound parameters

The code samples in this section describe how to construct and execute a SQL statement, using bound parameters to set values for statement parameters at runtime.

1. Create a Command object from the session:

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

2. Set the SQL statement you want to execute:

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

3. 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
        }
   };
   

4. 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();
   

5. 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;
   };
   

6. 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
   }
   };
   

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

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

8. Create an accessor on the Command object for the parameters:

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

9. 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
        }};
   

10. Execute the command:

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