Executing prepared statements

The ASE OLE DB Provider provides a full set of functions for using prepared statements, which provide performance advantages for statements that are used repeatedly. The following code samples show how to use the prepared statements.

1. Get 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. Get the ICommandPrepare interface from the Command object. Then, prepare the command by calling Prepare, as shown:

   ICommandPrepare* pICommandPrepare;
   hr = pICommandText->QueryInterface(
        __uuidof(ICommandPrepare),
        (void**)&pICommandPrepare);
   hr = pICommandPrepare->Prepare(cExpectedRuns);
   pICommandPrepare->Release();
   

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

5. Get the ICommandWithParameters interface from the Command object and set the parameter information:

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

6. Create a struct to hold the parameter data. This struct contains all of the parameters for this command, as shown:

   struct Parameters {
        int dept_id;
   };
   

   The following describes the struct to the command:

   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
   }
   };
   
   IAccessor* pIAccessor;
   hr = pICommandText->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
   
   DBBINDSTATUS status[1];
   HACCESSOR hAccessor;
   HRESULT hr = pIAccessor->CreateAccessor(
        DBACCESSOR_PARAMETERDATA, 1,
        ExactBindingsParameters, sizeof(ExactBindingsParameters),
        &hAccessor, status);
   pIAccessor->Release();
   
   Parameters param = { 1 };
   DBPARAMS params[1] = {
        {
             &param,
             1,
             hAccessor
        }
   };
   
   DBROWCOUNT cRowsAffected;
   IRowset* pIRowset;
   hr = pICommandText->Execute(
        NULL, IID_IRowset, params,
        &cRowsAffected, (IUnknown**)&pIRowset);
   

7. Create an accessor for the parameter struct, using the IAccessor interface:

   IAccessor* pIAccessor;
   hr = pICommandText->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
   
   DBBINDSTATUS status[1];
   HACCESSOR hAccessor;
   HRESULT hr = pIAccessor->CreateAccessor(
        DBACCESSOR_PARAMETERDATA, 1,
        ExactBindingsParameters, sizeof(ExactBindingsParameters),
        &hAccessor, status);
   pIAccessor->Release();
   

   The following is an array of the parameter sets:

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

8. Execute the command:

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