MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 3: Commands


 

Using Parameters

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" in Chapter 6, "Getting and Setting Data."

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:

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.