INF: Meaning of an Active ODBC Statement Handle

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

Example 2

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

// The above statement generates a results set

// 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

// Error with SQLState 24000 "Invalid Cursor State"