MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server

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', ?)}

To improve performance by reducing network traffic, SQLOLEDB does not automatically derive parameter information unless ICommandWithParameters::GetParameterInfo or ICommandPrepare::Prepare is called before executing a command. This means that SQLOLEDB does not automatically perform the following actions:

• Verify the correctness of the data type specified with ICommandWithParameters::SetParameterInfo.
  
  
• Map from the DBTYPE specified in the accessor binding information to the correct Microsoft® SQL Server™ data type for the parameter.

Applications will receive possible errors or loss of precision with either of these methods if they specify data types that are not compatible with the SQL Server data type of the parameter.

To ensure this does not happen, the application should do the following:

• If hard-coding ICommandWithParameters::SetParameterInfo, ensure that pwszDataSourceType matches the SQL Server data type for the parameter.
  
  
• If hard-coding an accessor, ensure that the DBTYPE value being bound to the parameter is of the same type as the SQL Server data type for the parameter.
  
  
• Code the application to call ICommandWithParameters::GetParameterInfo so that the provider can obtain the SQL Server data types of the parameters dynamically. Note that this causes an extra network round-trip to the server.

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, 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 command text contains both named and unnamed parameters, all the unnamed parameters must be specified before any named parameters.

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.