KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q124998: INF: Change in Behavior of SQLSetPOS on Empty Result Set

Article: Q124998
Product(s): Open Database Connectivity (ODBC)
Version(s): WINDOWS:2.1
Operating System(s): 
Keyword(s): 
Last Modified: 27-AUG-1999

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          :  
Technology        : kbAudDeveloper kbODBCSearch kbODBC210
Version           : WINDOWS:2.1

=============================================================================

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.