ADO - ADO Command Preparation and Prepared Statements

Introduction

In ActiveX Data Objects (ADO), command preparation is a technique used to prepare an SQL statement before executing it. Instead of sending and compiling the same SQL statement repeatedly, an application can ask the database provider to prepare the command in advance. Once prepared, the command can often be executed multiple times with different parameter values.

This is particularly useful when an application repeatedly performs the same type of database operation. For example, suppose an application needs to search for customers based on their customer ID. The SQL structure remains the same, while only the customer ID changes. Preparing the command can reduce some of the work required for repeated executions.

What Is a Prepared Statement?

A prepared statement is an SQL command that is prepared by the database provider before execution. The general process is:

  1. The application creates an ADO Command object.

  2. The SQL statement is assigned to the CommandText property.

  3. Parameters are created and added to the command.

  4. The command is prepared.

  5. Parameter values are supplied.

  6. The command is executed.

  7. The same prepared command can be executed again with different parameter values.

For example, consider the following SQL statement:

SELECT * FROM Employees WHERE DepartmentID = ?

The SQL structure does not change. Only the value of DepartmentID changes. A prepared command can therefore be reused for multiple department IDs.

The ADO Command Object

The ADO Command object represents a command that can be executed against a data source. It is commonly used for SQL statements, stored procedures, and parameterized commands.

A simplified example using VBScript/VB-style ADO code is:

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = conn

cmd.CommandText = _
    "SELECT * FROM Employees WHERE DepartmentID = ?"

cmd.CommandType = adCmdText

Here, cmd represents the command that will be sent to the database.

Using Parameters

Prepared statements are commonly used together with parameters. Instead of directly placing values inside an SQL string, a parameter represents the value that will be supplied later.

For example:

Dim param

Set param = cmd.CreateParameter( _
    "DepartmentID", adInteger, adParamInput)

cmd.Parameters.Append param

The parameter value can then be assigned before execution:

cmd.Parameters("DepartmentID").Value = 10

The command can then be executed:

Set rs = cmd.Execute

The parameter value can subsequently be changed:

cmd.Parameters("DepartmentID").Value = 20
Set rs = cmd.Execute

The same command structure is reused while the parameter value changes.

Preparing the Command

ADO provides the Prepared property of the Command object for requesting command preparation.

For example:

cmd.Prepared = True

This tells the ADO provider that the command should be prepared before execution, if the provider supports preparation.

A complete conceptual example is:

Dim cmd
Dim param
Dim rs

Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = conn

cmd.CommandText = _
    "SELECT * FROM Employees WHERE DepartmentID = ?"

cmd.CommandType = adCmdText

Set param = cmd.CreateParameter( _
    "DepartmentID", adInteger, adParamInput)

cmd.Parameters.Append param

cmd.Prepared = True

cmd.Parameters("DepartmentID").Value = 10

Set rs = cmd.Execute

The exact behavior of preparation depends on the OLE DB provider being used. ADO provides the interface, but the provider ultimately determines how preparation is implemented.

Why Command Preparation Is Useful

The main advantage of command preparation is reusability.

Consider an application that needs to execute the following query hundreds or thousands of times:

SELECT Name, Salary
FROM Employees
WHERE DepartmentID = ?

Without preparation, the provider may need to process the command repeatedly. With a prepared command, the provider can potentially prepare the SQL statement once and reuse the prepared representation.

This can be beneficial when:

  • The same SQL command is executed repeatedly.

  • Only parameter values change.

  • The application performs many database operations.

  • Database communication is relatively expensive.

  • The database provider supports efficient prepared-command execution.

The performance improvement is not guaranteed in every situation. Some providers may already optimize repeated commands, while others may not provide significant benefits from the Prepared property.

Prepared Statements and SQL Injection

Prepared statements are also closely associated with safer parameter handling.

Consider an unsafe approach:

sql = "SELECT * FROM Employees WHERE Name = '" & userName & "'"

If userName comes directly from an external user, constructing SQL this way can create SQL injection vulnerabilities.

A parameterized command instead separates the SQL structure from the value:

SELECT * FROM Employees WHERE Name = ?

The value is supplied through an ADO parameter.

This makes parameterized commands a much safer approach than constructing SQL statements by concatenating untrusted input. However, simply setting Prepared = True should not be confused with being the security mechanism. Parameterization is the important part for separating data from SQL syntax; preparation is primarily about command execution and potential reuse.

Prepared Property and Provider Support

One important characteristic of ADO is that the Prepared property depends on the underlying provider.

For example:

cmd.Prepared = True

does not mean that every database will necessarily prepare the command in exactly the same way.

The provider may:

  • Support prepared statements fully.

  • Partially support preparation.

  • Ignore preparation requests.

  • Implement preparation internally.

  • Return an error if the requested operation is unsupported.

Therefore, developers should not assume that setting Prepared to True always produces a measurable performance improvement.

Prepared Statements vs. Ordinary SQL Execution

The difference can be understood through a simple example.

Suppose an application needs to retrieve employees from five departments.

Without reusing a parameterized command, the application might construct different SQL statements:

SELECT * FROM Employees WHERE DepartmentID = 10
SELECT * FROM Employees WHERE DepartmentID = 20
SELECT * FROM Employees WHERE DepartmentID = 30
SELECT * FROM Employees WHERE DepartmentID = 40
SELECT * FROM Employees WHERE DepartmentID = 50

With a parameterized command, the SQL structure remains the same:

SELECT * FROM Employees WHERE DepartmentID = ?

Only the parameter changes:

DepartmentID = 10
DepartmentID = 20
DepartmentID = 30
DepartmentID = 40
DepartmentID = 50

This makes the application code cleaner and allows the same command object to be reused.

Prepared Statements and Stored Procedures

Prepared statements and stored procedures are related but are not the same thing.

A prepared statement is an SQL command prepared for execution, often allowing the same command structure to be executed with different parameters.

A stored procedure is a database-side program that is stored in the database and can contain SQL statements, control flow, parameters, and other database-specific functionality.

For example, a stored procedure might be:

CREATE PROCEDURE GetEmployees
    @DepartmentID INT
AS
BEGIN
    SELECT *
    FROM Employees
    WHERE DepartmentID = @DepartmentID
END

ADO can execute this stored procedure through a Command object.

A prepared SQL command, on the other hand, might simply contain:

SELECT * FROM Employees WHERE DepartmentID = ?

The choice between the two depends on application architecture, database capabilities, performance requirements, and maintainability.

When Preparation May Not Help

Command preparation is not automatically faster.

For a command that executes only once, preparing it may introduce additional overhead without providing enough benefit.

For example:

Create command
       |
Prepare command
       |
Execute once
       |
Finish

The preparation cost may not be worthwhile when the command is executed only once.

Preparation becomes more attractive when the same command is executed repeatedly:

Create command
       |
Prepare command
       |
Execute with value 1
       |
Execute with value 2
       |
Execute with value 3
       |
Execute with value 4
       |
Execute with value 5

In this situation, the preparation overhead can potentially be distributed across multiple executions.

Important Considerations

When using ADO prepared commands, developers should consider several factors.

First, provider support is important. ADO communicates with the database through providers, and providers can behave differently.

Second, command reuse matters. Preparing a command is most useful when the same command structure is executed multiple times.

Third, parameters should be used correctly. Parameter data types should match the corresponding database columns as closely as practical.

Fourth, performance should be measured. A prepared command should not automatically be assumed to be faster. The actual improvement depends on the database, provider, network conditions, query complexity, and execution frequency.

Fifth, parameterization and preparation are different concepts. Parameterization primarily separates values from SQL syntax and helps prevent SQL injection, whereas preparation concerns how the command is prepared for execution.

Conclusion

ADO command preparation allows an application to request that an SQL command be prepared before execution. The Command object's Prepared property is used for this purpose. Prepared commands are particularly useful when the same SQL statement needs to be executed repeatedly with different parameter values.

The technique is commonly combined with ADO parameters, allowing the SQL structure to remain unchanged while input values are supplied separately. This can improve code organization, provide safer handling of external values, and potentially improve performance for repeated executions.

However, preparation is provider-dependent, and its performance benefits are not universal. Developers should therefore use prepared commands when repeated execution makes them appropriate and should evaluate the actual behavior of the database provider rather than assuming that preparation will always improve performance.