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:
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:
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 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.