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.

To enable compilation and preparation of the statement on ASE, set the DynamicPrepare property to 1.

Get 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 = ?");

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

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 and set the parameter information:

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

A struct holds 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);

Use the IAccessor interface to create an accessor for the parameter struct:

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 parameter sets:

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