INF: Change in Behavior of SQLSetPOS on Empty Result Set (124998)

SUMMARY

This article applies only to those drivers that support the ODBC Cursor API and SQLSetPos.

The ODBC API Specification prior to version 2.1 did not allow calling SQLSetPos with fOption=SQL_ADD after SQLExtendedFetch returned SQL_NO_DATA_FOUND. This will cause a problem with the use of a dynaset in Microsoft Foundation Classes (MFC) version 3.0.

When using dynasets and trying to add a record to a table following an query that produced an empty recordset, you will receive the following error:

SQLSTATE 24000 - Invalid Cursor State

In other words, if you open a dynaset CRecordset object and the query returns an empty recordset, a subsequent AddNew()/Update() call will cause the error.

The ODBC Specification version 2.1 removes this restriction.

MORE INFORMATION

An application can use block and scrollable cursors on a result set by calling SQLSetConnectOption to specify SQL_CURSOR_TYPE, SQL_ROWSET_SIZE, SQL_CONCURRENCY, and (optionally) SQL_KEYSET_SIZE. Result sets are generated by executing a SELECT statement or by other operations, such as calling catalog functions.

After the appropriate cursor type is specified and a result set is generated, the application can use SQLExtendedFetch to fetch a rowset; within a rowset, it can use SQLSetPos to move the cursor to a specified row. An application can also use SQLSetPos to insert, delete, or update rows in the rowset and to refresh the rows in the rowset. If the result set is empty or if the end of the result set has been reached, SQLExtendedFetch returns SQL_NO_DATA_FOUND.

In ODBC API Specification prior to version 2.1, an application was not allowed to call SQLSetPos with fOption=SQL_ADD to insert a row after SQLExtendedFetch had returned SQL_NO_DATA_FOUND. In other words, an application could not insert at the end of a result set or into an empty result set.

Doing so would produce the invalid cursor state (SQLSTATE 24000) error returned from SQLSetPos. This situation is encountered in MFC 3.0 when using dynasets. If you open a dynaset Crecordset object and the query returns an empty recordset, a subsequent AddNew()/Update() will call SQLSetPos as described above and cause the error.

In ODBC API Specification version 2.1, this restriction is removed. As a result, SQLSetPos with fOption=SQL_ADD can be called even if SQLExtendedFetch returns SQL_NO_DATA_FOUND. However, the application still needs to call SQLExtendedFetch before it calls SQLSetPos.

The Driver Manager and Cursor Library for ODBC API version 2.1 are available on the Internet, via anonymous ftp to ftp.microsoft.com, cd to developr/odbc/public. The file name is ODBC21.EXE.