INF: Change in Behavior of SQLSetPOS on Empty Result Set
ID: Q124998
|
2.10
WINDOWS
kbprg
The information in this article applies to:
-
Microsoft Open Database Connectivity, version 2.1
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.
Additional query words:
2.10 MFC ODBC CURSOR DYNASET Windows NT
Keywords :
Version : WINDOWS:2.1
Platform : WINDOWS
Issue type :