Before a consumer can use a command, it must determine whether commands are supported. To do this, the consumer calls QueryInterface for IDBCreateCommand on a session. If this interface is exposed, the provider supports commands. To create a command, the consumer then calls IDBCreateCommand::CreateCommand on the session. You can use a single session to create multiple commands.
When the command is first created, it does not contain command text. The consumer sets the command text with ICommandText::SetCommandText. Because the text command syntax is provider specific, the consumer passes the GUID of the syntax to use. This can be one of the following values, or a provider-specific value specifying some provider specific syntax.
| Dialect Value | Meaning |
| DBGUID_DEFAULT | The command text is to be interpreted according to the provider's default grammar. If the provider supports multiple syntax including SQL, it must be able to recognize SQL syntax when DBGUID_DEFAULT is passed. |
| DBGUID_SQL | The command text is to be interpreted according to a superset of ANSI SQL syntax rules. To determine the level of ANSI SQL syntax supported by a provider, a consumer calls IDBProperties::GetProperties for the DBPROP_SQLSUPPORT property. Providers reporting this syntax are free to use extensions to ANSI SQL, or any subset of ANSI SQL according to their reported level of support, but must comply with the rules defined in ANSI SQL for any supported syntax defined in that specification. Note DBGUID__SQL was added in version 1.5 and is not supported in earlier providers. Consumers should use DBGUID_SQL only when calling a version 1.5 or greater provider that supports DBPROP_SQLSUPPORT. In most cases, unless the provider is trying to distinguish between different dialects, the consumer can just pass DBGUID_DEFAULT. Providers that support multiple syntaxes including SQL must be able to recognize SQL syntax when DBGUID_DEFAULT is passed. |
To execute the command, the consumer calls ICommand::Execute. If the command text specifies a rowset, such as an SQL SELECT statement, Execute creates an instance of the rowset and returns an interface pointer to it.
Before executing a command that creates a rowset, the consumer can specify various properties of the rowset—such as the interfaces to expose and degree of scrollability—by calling ICommandProperties::SetProperties. For more information, see "Setting Rowset Properties" in Chapter 4. The consumer can also get information about the columns of the rowset by calling IColumnsInfo::GetColumnInfo, which all providers support, or IColumnsRowset::GetColumnsRowset, which providers may support on the command. The command text must be set to use these methods and, if the command supports preparation, it must be prepared.
If a command is to be executed multiple times with the same text command, or if the consumer needs information about the rowset it will create, the consumer can prepare the command by calling ICommandPrepare::Prepare. This is equivalent to compiling the command. Providers are not required to support command preparation.
Prepared commands are more versatile when used with parameters, which are variables in the text command. For example, if the provider supports SQL, the consumer might prepare and repeatedly execute the parameterized SQL statement UPDATE Employees SET (Salary = ?) WHERE EmployeeID = ? to update the salaries of a number of employees.
Parameter support is optional for commands, prepared or otherwise; providers that support them expose ICommandWithParameters on the command. To use parameters, the consumer first describes the parameters to the provider by calling ICommandWithParameters::SetParameterInfo. Some providers can describe parameters themselves and do not need consumers to call SetParameterInfo. The consumer then creates an accessor that specifies the structure of a buffer and places parameter values in this buffer. Finally, it passes the handle of the accessor and a pointer to the buffer to Execute. On later calls to Execute, the consumer simply places new parameter values in the buffer and calls Execute with the accessor handle and buffer pointer. For more information, see Chapter 6, "Getting and Setting Data."