Calling stored procedures

This section describes how to call stored procedures and process the results from an OLE DB application.

For a full description of stored procedures and triggers, see the Adaptive Server Enterprise Reference Manual.

1. Create a command:

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

2. Set the command’s text:

   hr = pICommandText->SetCommandText(
        DBGUID_DBSQL,
        L"{ call sp_foo(?) }");
   

3. Define the parameters:

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

4. Set the parameter information on the command:

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

5. Define the parameter's data structure:

   struct Parameters {
   int dept_id;
   };
   
   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
        }
   };
   

6. Create an accessor for the parameters:

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

7. Define the parameter data:

   Parameters param = { 1 };
   DBPARAMS params[1] = {
        {
             &param,
             1,
             hAccessor
        }
   };
   
   DBROWCOUNT cRowsAffected;
   IRowset* pIRowset;
   hr = pICommandText->Execute(
        NULL, IID_IRowset, params,
        &cRowsAffected, (IUnknown**)&pIRowset);