Export (0) Print
Expand All

Allocating a Statement Handle

SQL Server 2000

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 are driver-specific.

Use caution 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 requires. Note that setting SQL_ATTR_MAX_ROWS causes the driver to issue a SET ROWCOUNT statement to the server, which affects all Microsoft® SQL Server™ statements, including triggers and updates.

Use caution 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



© 2014 Microsoft