Using Parameters (OLE DB)

  • Article

Parameters are placeholders that can be inserted into a command object. These placeholders are bound to consumer variables at execution time. By using input parameters, consumers can specify a command once and execute it multiple times, each with a different set of values. Parameters are also useful for inserting nonstring values into a command. By using parameters, consumers can execute a command, such as a stored procedure, that contains output arguments.

The provider determines how parameters are specified within a command. SQL providers that report at least a DBPROP_SQLSUPPORT value of DBPROPVAL_SQL_ODBC_MINIMUM or DBPROPVAL_SQL_ANSI92_INTERMEDIATE must support parameters in conjunction with the DBGUID_SQL dialect. SQL providers represent parameter markers within a command as question marks (?). In most cases, parameter markers can legally appear only in certain positions within an SQL statement. They cannot take the place of an identifier, such as a column name in the SELECT list or a table name in the FROM clause, and are not allowed in data definition language (DDL) statements.

Not all SQL providers support the ability to describe the parameters embedded within a command. For providers that do support describing parameters, consumers can obtain parameter information by calling ICommandWithParameters::GetParameterInfo. Consumers that know the number and types of the parameters can specify this information by calling ICommandWithParameters::SetParameterInfo. The provider uses this information to determine how to convert parameter data from the type supplied by the consumer to the native type used by the data store. This is particularly useful for providers that cannot describe the parameters embedded within a command. It can also lead to more efficient code because the provider does not have to go through the potentially expensive operation of describing the command.

The consumer variables from which input parameter values are taken and to which output parameter values are written are passed at run time to ICommand::Execute in the DBPARAMS structure. Multiple sets of parameter values can be passed in a single call to Execute, which has the effect of executing the statement multiple times, once with each set of parameter values. The consumer describes the layout of parameter information within the buffer passed in the DBPARAMS structure by creating a parameter accessor and passing a handle to the parameter accessor in its call to ICommand::Execute. For more information about parameter accessors, see Using Accessors.

Parameters may be named or unnamed but are always identified within the parameter accessor according to ordinal, using the iOrdinal member of the DBBINDING structure. In most cases, the ordinal of a parameter within a textual command is its one-based position within the command.

To bind parameters, the consumer does one of the following:

  • If the consumer knows the ordinals and server types of the parameters to be bound, it calls ICommandWithParameters::SetParameterInfo, supplying the ordinals of the parameters and specifying the name, if known, along with the data type of each parameter in the DBPARAMBINDINFO structure. The iOrdinal members of the DBBINDING structure correspond to the ordinal values specified in SetParameterInfo.

  • If the consumer knows the names but not the ordinals of the parameters, it can obtain a list of ordinals corresponding to a set of named parameters by calling ICommandWithParameters::MapParameterNames. The consumer then calls ICommandWithParameters::SetParameterInfo, using these ordinal values with the names. The iOrdinal members of the DBBINDING structure correspond to the ordinal values specified in SetParameterInfo. The ordinals returned by MapParameterNames do not necessarily indicate the position of the parameters in the command but represent how the provider associates the parameter name to the ordinal specified in the accessor.

  • If the consumer does not know the ordinals of the parameters to be bound and the provider supports describing parameters, the consumer can call ICommandWithParameters::GetParameterInfo to determine the parameter name, if applicable, along with the type of each parameter ordinal. The iOrdinal members of the DBBINDING structure correspond to the ordinal values returned in GetParameterInfo.

  • If the consumer does not know the ordinals of the parameters, and the provider does not support describing the parameters, the consumer must try to determine the number and order of parameters. It must either obtain parameter information from the user or "guess" at the data types to bind each parameter. If the consumer does not need to convert between the bound type and the type of data on the server and does not need to use named parameters, it can simply create an accessor using the ordinals of the parameters and execute the statement without calling any method from ICommandWithParameters.

Output parameters are filled in when the provider has completely finished processing the execution. For some providers, this does not occur until the rowset is released, and the memory specified for the output parameters must remain valid until the rowset is released. To determine when output parameters are populated and memory can be released, the consumer should check the DBPROP_OUTPUTPARAMETERAVAILABILITY data source information property.