Command Parameters

OLE DB and SQL Server

OLE DB and 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:

  • 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™ 2000 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 2000 data type of the parameter.

To ensure this does not happen, the application should:

  • 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 the provider can obtain the SQL Server data types of the parameters dynamically. Note that this causes an extra network roundtrip 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, 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.