INF: Meaning of an Active ODBC Statement Handle

Last reviewed: September 9, 1996
Article ID: Q101523

SUMMARY

An ODBC application can call SQLGetInfo() with an fInfoType of SQL_ACTIVE_STATEMENTS to determine the number of active statement handles that an ODBC driver can support on a connection. This article explains how the concept of 'active statement' is far removed from the states of statement handles in an ODBC State Transition table.

MORE INFORMATION

An ODBC application may be written such that one statement handle (hstmt) on a connection (hdbc) is used to fetch data from the back end, and a second hstmt within the same hdbc is used simultaneously for other tasks, such as generating updates. But an application must not assume that the driver will support such behavior from the application. The application must first call SQLGetInfo with the fInfoType of SQL_ACTIVE_STATEMENTS to see if multiple active hstmts are supported. In ODBC, an hstmt is defined as active if it has results pending. The term 'results' means rows from a select or the number of rows affected by an insert, update or delete operation (such as row count).

If a driver supports only one active hstmt per hdbc, applications written as above will not work. They must allocate a second hdbc, make a new connection, and then allocate another hstmt within this new hdbc to be able to concurrently use both hstmts, that is, concurrently having one hstmt with results pending as the rows are being fetched, and using the other hstmt for other application-specific tasks.

It must be noted, however, that even for a driver that supports only one active hstmt per hdbc, it may be possible to allocate more than one hstmt within the hdbc, that is, such a driver may support multiple hstmts per hdbc but not multiple active hstmts per hdbc. In such cases, applications must be careful to not use other hstmts within the hdbc when results are pending on a hstmt within the hdbc.

The following examples assume a driver that supports only one active hstmt per hdbc, without limiting multiple hstmts per hdbc.

SQLAllocStmt(hdbc, &hstmt1) SQLAllocStmt(hdbc, &hstmt2)

Example 1

SQLExecDirect (hstmt1, "select * from <a zero-row table>", SQL_NTS)

//Since the above select generated no results, it's OK to use hstmt2
SQLExecDirect(hstmt2, "select * from <table>", SQL_NTS)

Example 2

SQLExecDirect(hstmt1, "select * from <table that has rows>", SQL_NTS)

// The above statement generates a results set
SQLExecDirect(hstmt2, "some update statement", SQL_NTS)
// Error with SQLState 24000 "Invalid Cursor State"

Example 3

SQLExecDirect(hstmt1, "delete from <table that has rows>", SQL_NTS)

// The above delete generates a row count (the number of rows
affected) SQLExecDirect(hstmt2, "some update statement", SQL_NTS)
// Error with SQLState 24000 "Invalid Cursor State"


Because there are results pending on hstmt1, the last SQLExecDirect on hstmt2 in Examples 2 and 3 results in SQLState 24000 "Invalid Cursor state." Note that in example 3, if SQLRowCount had been called after the first SQLExecDirect to determine the number of rows affected, the second SQLExecDirect would have been valid.


KBCategory: kbusage
KBSubcategory:

Additional reference words: 1.00



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 9, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.