Command Parameters

Parameters are marked in command text with the ODBC-specified question mark character. For example, the following ODBC SQL statement is marked for a single input parameter:

{call SalesByCategory('Produce', ?)}
  

Example

SQLOLEDB supports input parameters in SQL statement commands. On procedure-call commands, SQLOLEDB supports input, output, and input/output parameters. Output parameter values are returned to the application either on execution or when all returned rowsets are exhausted by the application. To ensure that returned values are valid, use IMultipleResults to force rowset consumption.

// Macro used in the example.
#define COUNTRY_MAX_CHARS     15
  
// Structure supporting the parameters of the example stored procedure.
typedef struct tagSPROCPARAMS
  {
  long    lReturnValue;
  char    acCountry[COUNTRY_MAX_CHARS + 1];
  } SPROCPARAMS;
  
  // Interfaces used in the example.
  ICommandText*   pICommandText = NULL;
  ICommandWithParameters* pICommandWithParameters = NULL;
  IAccessor*    pIAccessor = NULL;
  IMultipleResults* pIMultipleResults = NULL;
  IRowset*    pIRowset = NULL;
  
  // Command parameter data.
  DBPARAMS    Params;
  const ULONG   nParams = 2;
  DBPARAMBINDINFO   rgParamBindInfo[nParams] = 
    {
    L"DBTYPE_I4",
    L"ReturnVal",
    sizeof(long),
    DBPARAMFLAGS_ISOUTPUT,
    11,
    0,
    L"DBTYPE_VARCHAR",
    L"@Country",
    COUNTRY_MAX_CHARS,
    DBPARAMFLAGS_ISINPUT,
    0,
    0 };
  ULONG     rgParamOrdinals[nParams] = {1,2};
  
  // Parameter accessor data.
  HACCESSOR     hAccessor;
  DBBINDING     acDBBinding[nParams];
  DBBINDSTATUS    acDBBindStatus[nParams];
  
  // The command and parameter data.
  WCHAR*      wszSQLString =
    L"{? = call CustomersInCountry(?)}";
  SPROCPARAMS   sprocparams = {0, "USA"};
  
  // Returned count of rows affected.
  LONG      cRowsAffected = 0;
  
  HRESULT     hr;
  
  // Create the command.
  if (FAILED(hr = pIDBCreateCommand->CreateCommand(NULL, 
    IID_ICommandText, (IUnknown**) &pICommandText)))
    {
    // Process error from IDBCreateCommand and return.
    }
  
  // Set the command text value.
  if (FAILED(hr = pICommandText->SetCommandText(DBGUID_DBSQL,
    wszSQLString)))
    {
    // Process error from ICommand and return.
    }
  
  // Get the ICommandWithParameters interface to set up parameter
  // values.
  if (FAILED(hr = pICommandText->QueryInterface(
    IID_ICommandWithParameters,
    (void**) &pICommandWithParameters)))
    {
    // Process error from ICommand and return.
    }
  
  // Set parameter information.
  if (FAILED(hr = pICommandWithParameters->SetParameterInfo(nParams, 
    rgParamOrdinals, rgParamBindInfo)))
    {
    // Process error from ICommandWithParameters and return.
    }
  
  // Create parameter accessor, but first set binding structures
  // to indicate the characteristics of each parameter.
  for (ULONG i = 0; i < nParams; i++)
    {
    acDBBinding[i].obLength = 0;
    acDBBinding[i].obStatus = 0;
    acDBBinding[i].pTypeInfo = NULL;
    acDBBinding[i].pObject = NULL;
    acDBBinding[i].pBindExt = NULL;
    acDBBinding[i].dwPart = DBPART_VALUE;
    acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
    acDBBinding[i].dwFlags = 0;
    acDBBinding[i].bScale = 0;
    }
  
  acDBBinding[0].iOrdinal = 1;
  acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);
  acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;
  acDBBinding[0].cbMaxLen = sizeof(long);
  acDBBinding[0].wType = DBTYPE_I4;
  acDBBinding[0].bPrecision = 11;
  
  acDBBinding[1].iOrdinal = 2;
  acDBBinding[1].obValue = offsetof(SPROCPARAMS, acCountry);
  acDBBinding[1].eParamIO = DBPARAMIO_INPUT;
  acDBBinding[1].cbMaxLen = COUNTRY_MAX_CHARS;
  acDBBinding[1].wType = DBTYPE_STR;
  acDBBinding[1].bPrecision = 0;
  
  // Get the IAccessor interface, then create the accessor for
  // the defined parameters.
  pICommandWithParameters->QueryInterface(IID_IAccessor,
    (void**) &pIAccessor);
  
  hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,
    nParams, acDBBinding, sizeof(SPROCPARAMS), &hAccessor,
    acDBBindStatus);
  if (FAILED(hr))
    {
    // Process error from IAccessor and return.
    }
  
  // Fill the DBPARAMS structure for the command execution.
  Params.pData = &sprocparams;
  Params.cParamSets = 1;
  Params.hAccessor = hAccessor;
  
  // Execute the command.
  if (FAILED(hr = pICommandText->Execute(NULL, IID_IMultipleResults,
    &Params, &cRowsAffected, (IUnknown**) &pIMultipleResults)))
    {
    // Process error from ICommand and return.
    }
  
  // For each rowset or count of rows affected...
  do
    {
    hr = ((IMultipleResults*) pIResults)->GetResult(NULL, 0,
    IID_IRowset, &cRowsAffected, (IUnknown**) &pIRowset);
  
    switch (hr)
    {
    case S_OK:
      {
      if (pIRowset != NULL)
        {
        // Process data from the rowset and release.
        pIRowset->Release();
        }
      else if (cRowsAffected != -1)
        {
        printf("Command succeeded. %ld rows affected.\n\n",
        cRowsAffected);
        }
      else
        {
        printf("Command succeeded.\n\n");
        }
  
      break;
      }
  
    case DB_S_NORESULT:
    case DB_S_STOPLIMITREACHED:
      break;
  
    default:
      {
      DumpError(pIResults, IID_IMultipleResults);
      break;
      }
    }
    }
  while (hr == S_OK);
  
  if (SUCCEEDED(hr))
    {
    // At this point, the value of the return is guaranteed correct.
    // If any other output parameters had been specified, then they
    // too would now contain their correct values.
    printf("Return value %d\n", sprocparams.lReturnValue);
    }
  

The names of stored procedure parameters need not be specified in a DBPARAMBINDINFO structure. Use NULL for the value of the pwszName member to indicate that SQLOLEDB should ignore the parameter name and use only the ordinal specified in the rgParamOrdinals member of ICommandWithParameters::SetParameterInfo.

If the name of a stored procedure parameter is specified, SQLOLEDB checks the name to ensure that it is valid. SQLOLEDB returns an error when it receives an erroneous parameter name from the consumer.