INF: Change in Behavior of SQLSetPOS on Empty Result Set

Last reviewed: July 2, 1997
Article 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 reference words: 2.10 MFC ODBC CURSOR DYNASET Windows NT
KBCategory: kbprg
KBSubcategory:
Keywords : kbprg
Technology : kbMfc
Version : 2.10
Platform : WINDOWS


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: July 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.