Delay execution of the SQL PREPARE statement

You can defer execution of the SQLPrepare statement by enabling the deferred-PREPARE feature.

This feature works primarily with dynamic SQL statements where the application does a series of SQLPrepare and SQLExecute statements. It optimizes the number of round-trip messages to the database server by not sending SQLPrepare statements to the database server until the application calls SQLExecute on that statement.

When deferred-PREPARE is enabled, the following behavior is expected of the application:
  • Execution of SQLPrepare does not put the statement in a prepared state.
  • Syntax errors in an SQLPrepare statement are not known until the statement is executed because the SQL statement is never sent to the database server until it is executed. If open-fetch-close optimization is turned on, errors are not returned to the client until the first fetch, because open-fetch-close optimizes the OPEN/FETCH so that OPEN is sent on the first fetch.
  • SQLColAttributes, SQLDescribeCol, SQLNumResultCols, and SQLNumParams always return HY010 (function sequence error) if called after SQLPrepare but before SQLExecute by the application.
  • SQLCopyDesc returns HY010 if the source descriptor handle is an IRD if called after SQLPrepare but before SQLExecute by the application.
  • SQLGetDescField and SQLGetDescRec return HY010 if the descriptor handle is an IRD if called after SQLPrepare but before SQLExecute by the application.
You can enable the deferred-PREPARE feature for an ODBC application in either of the following ways:
  • Set the SQL_INFX_ATTR_DEFERRED_PREPARE attribute with SQLSetConnectAttr.

    When you use SQLSetConnectAttr to enable this attribute, all new allocated statements for that connection inherit the attribute value. The only way to change this attribute value per statement, is to set/reset it again as a statement attribute. The default is DISABLED for the connection attribute.

  • Set the SQL_INFX_ATTR_DEFERRED_PREPARE attribute with SQLSetStmtAttr.

The SQL_INFX_ATTR_DEFERRED_PREPARE attribute can be set in any connection state between C2 and C5 (both included) when setting it using SQLSetConnectAttr, whereas it can be set by with SQLSetStmtAttr only when the statement is in S1 (allocated) state. The value of the SQL_INFX_ATTR_DEFERRED_PREPARE attribute can be retrieved with SQLGetConnectAttr or SQLSetStmtAttr.