| title | Allocating a Statement Handle | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| description | Allocating a Statement Handle | |||||||||
| author | markingmyname | |||||||||
| ms.author | maghan | |||||||||
| ms.date | 03/14/2017 | |||||||||
| ms.service | sql | |||||||||
| ms.subservice | native-client | |||||||||
| ms.topic | reference | |||||||||
| helpviewer_keywords |
|
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]
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 using bookmarks and the kind of cursor to use with the statement's result set. Statement attributes are set with SQLSetStmtAttr, and their current settings are retrieved by using SQLGetStmtAttr. There is no requirement that an application set any statement attributes; all statement attributes have defaults, and 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 time that an application waits for a connection attempt to time-out while waiting to establish a connection (0 specifies an infinite wait). Sites that have slow response times can set this value high to make sure that connections have sufficient time to be completed. However, the interval should always be low enough to give the user a response in a reasonable time if the driver cannot connect.
Calling SQLSetStmtAttr with fOption set to SQL_ATTR_QUERY_TIMEOUT sets a query time-out interval to help 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. This affects all [!INCLUDEmsCoName] [!INCLUDEssNoVersion] statements, including triggers and updates.
Use caution when you are 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 additional roundtrip to the server.