Prepared Execution

Prepared execution is an efficient way to execute a statement more than once. The statement is first compiled, or prepared, into an access plan. The access plan is then executed one or more times at a later time. For more information about access plans, see “Processing an SQL Statement” in Chapter 2, “An Introduction to SQL and ODBC.”

Prepared execution is commonly used by vertical and custom applications to repeatedly execute the same, parameterized SQL statement. For example, the following code prepares a statement to update the prices of different parts. It then executes the statement multiple times with different parameter values each time.

SQLREAL Price;

SQLUINTEGER PartID;

SQLINTEGER PartIDInd = 0, PriceInd = 0;

// Prepare a statement to update salaries in the Employees table.

SQLPrepare(hstmt, "UPDATE Parts SET Price = ? WHERE PartID = ?", SQL_NTS);

// Bind Price to the parameter for the Price column and PartID to

// the parameter for the PartID column.

SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,

&Price, 0, &PriceInd);

SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 10, 0,

&PartID, 0, &PartIDInd);

// Repeatedly execute the statement.

while (GetPrice(&PartID, &Price)) {

SQLExecute(hstmt);

}

Prepared execution is faster than direct execution for statements executed more than once, primarily because the statement is compiled only once; statements executed directly are compiled each time they are executed. Prepared execution can also provide a reduction in network traffic because the driver can send an access plan identifier to the data source each time the statement is executed, rather than an entire SQL statement, if the data source supports access plan identifiers.

The application can retrieve the metadata for the result set after the statement is prepared and before it is executed. However, returning metadata for prepared, unexecuted statements is expensive for some drivers and should be avoided by interoperable applications if possible. For more information, see “Result Set Metadata” in Chapter 10, “Retrieving Results (Basic).”

Prepared execution should not be used for statements executed a single time. For such statements, it is slightly slower than direct execution because it requires an additional ODBC function call.

Important Committing or rolling back a transaction, either by explicitly calling SQLEndTran or by working in autocommit mode, causes some data sources to delete the access plans for all statements on a connection. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR options in the SQLGetInfo function description.

To prepare and execute a statement, the application:

1 Calls SQLPrepare and passes it a string containing the SQL statement.

2 Sets the values of any parameters. Parameters can actually be set before or after preparing the statement. For more information, see “Statement Parameters,” later in this chapter.

3 Calls SQLExecute and does any additional processing that is necessary, such as fetching data.

4 Repeats steps 2 and 3 as necessary.

When SQLPrepare is called, the driver:

1 Modifies the SQL statement to use the data source’s SQL grammar without parsing the statement. This includes replacing the escape sequences discussed in “Escape Sequences in ODBC” in Chapter 8, “SQL Statements.” The application can retrieve the modified form of an SQL statement by calling SQLNativeSql. Note that escape sequences are not replaced if the SQL_ATTR_NOSCAN statement attribute is set.

2 Sends the statement to the data source for preparation.

3 Stores the returned access plan identifier for later execution (if the preparation succeeded) or returns any errors (if the preparation failed). Errors include syntactic errors such as SQLSTATE 42000 (Syntax error or access violation) and semantic errors such as SQLSTATE 42S02 (Base table or view not found).

Note Some drivers do not return errors at this point, but instead return them when the statement is executed or when catalog functions are called. Thus, SQLPrepare might appear to have succeeded when in fact it has failed.

When SQLExecute is called, the driver:

1 Retrieves the current parameter values and converts them as necessary. For more information, see “Statement Parameters,” later in this chapter.

2 Sends the access plan identifier and converted parameter values to the data source.

3 Returns any errors. These are generally run-time errors such as SQLSTATE 24000 (Invalid cursor state). However, some drivers return syntactic and semantic errors at this point.

If the data source does not support statement preparation, the driver must emulate it to the extent possible. For example, the driver might do nothing when SQLPrepare is called, then perform direct execution of the statement when SQLExecute is called.

If the data source supports syntax checking without execution, the driver might submit the statement for checking when SQLPrepare is called and submit the statement for execution when SQLExecute is called.

If the driver cannot emulate statement preparation, it stores the statement when SQLPrepare is called and submits it for execution when SQLExecute is called.

Because emulated statement preparation is not perfect, SQLExecute can return any errors normally returned by SQLPrepare.