Allocating a Statement Handle

Before an application can execute a statement, it must allocate a statement handle. It does this by calling SQLAllocHandle with the HandleType parameter set to SQL_HANDLE_STMT and InputHandle pointing to a connection handle.

Statement attributes are characteristics of the statement handle. Sample statement attributes can include whether to use bookmarks and what kind of cursor to use with the statement’s result set. Statement attributes are set with SQLSetStmtAttr, and their current settings are retrieved with SQLGetStmtAttr. There is no requirement that an application set any statement attributes; all statement attributes have defaults, some of which are driver specific.

Care should be taken in the use of several ODBC statement and connection options. Calling SQLSetConnectAttr with fOption set to SQL_ATTR_LOGIN_TIMEOUT controls the amount of time an application waits for a connection attempt to timeout while waiting to establish a connection (0 specifies an infinite wait). Sites with slow response times can set this value high to ensure connections have sufficient time to complete, but the interval should always be low enough to give the user a response in a reasonable amount of time if the driver cannot connect.

Calling SQLSetStmtAttr with fOption set to SQL_ATTR_QUERY_TIMEOUT sets a query time-out interval to protect the server and the user from long-running queries.

Calling SQLSetStmtAttr with fOption set to SQL_ATTR_MAX_LENGTH limits the amount of text and image data that an individual statement can retrieve. Calling SQLSetStmtAttr with fOption set to SQL_ATTR_MAX_ROWS also limits a rowset to the first n rows if that is all the application needs. Note that setting SQL_ATTR_MAX_ROWS causes the driver to issue a SET ROWCOUNT statement to the server, which affects all SQL statements, including triggers and updates.

Care should be used when setting these options. It is best if all statement handles on a connection handle have the same settings for SQL_ATTR_MAX_LENGTH and SQL_ATTR_MAX_ROWS. If the driver switches from a statement handle to another with different values for these options, the driver must generate the appropriate SET TEXTSIZE and SET ROWCOUNT statements to change the settings. The driver cannot put these statements in the same batch as the user SQL statement because the user SQL statement can contain a statement that must be the first statement in a batch. The driver must send the SET TEXTSIZE and SET ROWCOUNT statements in a separate batch, which automatically generates an extra roundtrip to the server.

To use a statement

See Also