Using Parameters

Parameters allow the insertion of placeholders, which are bound to consumer variables at execution time, into a command. Input parameters allow consumers to specify a command once and execute it multiple times, each with a different set of values, and are also useful for inserting nonstring values into a command. Output parameters allow consumers to execute a command, such as a stored procedure, that contains output arguments.

The provider determines the manner in which 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 are legal only in certain places within an SQL statement; they are not allowed to 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 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 source. This is particularly useful for providers that cannot describe the parameters embedded within a command, but 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 means of a parameter accessor.

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. If the provider supports named parameters and the consumer calls MapParameterNames prior to calling SetParameterInfo or GetParameterInfo, the provider may assign an alternate set of ordinals to identify the parameters. This alternate set of ordinals must remain consistent through MapParameterNames and GetParameterInfo until SetParameterInfo is called or the command statement is changed.

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 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 calls 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 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 from the user. It must either obtain parameter information from the user or “guess” at the data types to bind each parameter. If the consumer is not trying to do any conversions between the bound type and the type of data on the server and is not trying 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.