Execution Models

ExecDirect

In this model, the SQL statement is specified, sent to the server, and executed all in one step. This model is best suited for SQL statements for a particular purpose or SQL statements that are executed only once. Parameters can be used, but they act merely as placeholders that the driver replaces with the parameter values before it sends the SQL statement to the server.

The DBMS discards the optimization information that is used to execute the SQL statement after execution is complete. If the same statement is specified again with SQLExecDirect, the entire process of parsing and optimizing happens again.

Prepare/Execute

In this model, the SQL statement is prepared (sent to the server, parsed, and optimized) first and executed later. When the statement is executed, what flows to the server is not the SQL statement itself, but a way of referencing the statement so that the access plan can be executed immediately. Parameters are often used in these SQL statements, so the only items that flow to the server are the reference to the access plan and the parameter values, not the entire SQL statement.

The Prepare/Execute model should be used when repeated execution of the same SQL statement is needed and when the SQL statement must be constructed dynamically during runtime. To use this model, the application calls SQLPrepare and then (presumably in a loop) calls SQLExecute.

Stored Procedures

The stored procedure model is like the Prepare/Execute model except that with stored procedures, the preparation step can be done independently from the application and the stored procedure persists beyond the runtime of the application. To use stored procedures in ODBC, the application calls SQLExecDirect but uses the SQL statement to specify the stored procedure name, as illustrated in the following example:

SQLExecDirect(hstmt, "{call query.proc1(?,?,?)}", SQL_NTS);