[DB-SIG] Suggestion for alterning the spec for Cursor.nextset()

[M.-A. Lemburg]

On 2008-05-07 22:11, Bob Kline wrote:
> [I've looked back through several months' worth of the list archives to 
> see if this is a topic which has already been visited, but I wasn't able 
> to find a search interface, and I wasn't really up to doing the 
> month-at-a-time thing through several years' worth of messages.  Is 
> there such an interface?]

http://www.python.org/search/

It would also be possible to build a customized search for just searching
the db-sig archives and put it on the SIG page, but I don't have access
to that page.

> The DB API 2.0 spec has this to say about nextset():
> 
>    This method will make the cursor skip to the next available set,
>    discarding any remaining rows from the current set.
> 
>    If there are no more sets, the method returns None. Otherwise, it
>    returns a true value and subsequent calls to the fetch methods will
>    return rows from the next result set.
> 
> 
> However, it seems that reality is more subtle than this description.  In 
> the case of a multi-query stored procedure, for example, some of the 
> queries may return rows and some might not.  If the spec forces the 
> implementers to try and detect which is which, and skip over the queries 
> which do not return rows, then even if it's possible to make the 
> distinction reliably for all possible back ends, the programmer will be 
> deprived of access to the rowcount information which would otherwise be 
> available for the queries which modify data but do not return any rows.  
> How about substitute language something like the following:
> 
>    This method will make the cursor skip to the next available set,
>    discarding any remaining rows from the current set.
> 
>    If there are no more sets, the method returns None.  Otherwise, it
>    returns a true value.  It is the responsibility of the programmer to
>    know whether the current result set represents a (possibly empty)
>    set of rows available for retrieval with subsequent calls to the
>    fetch methods.  In some cases the query generating the result (for
>    example, an INSERT statement in a stored procedure) will not provide
>    such a set of rows, but may instead set the rowcount attribute to
>    reflect the number of rows affected by the query.  In such a case an
>    exception will be raised if an attempt is made to fetch rows from
>    the non-existent set of rows.
> 
> 
> The wording could probably be improved, and it's unfortunate that use of 
> the words "set" and "sets" here covers such vague territory, but that's 
> how things work in at least some of the underlying layers (ODBC, ADODB, 
> SQL Server, etc.): they're apparently using "results set" to cover both 
> a set of results which consists of a sequence of rows, as well as the 
> results of a query for which you get information about the effects of 
> the query instead of a retrievable set of rows.
> 
> Has this been discussed before?

I'm not sure, but agree that things are a bit more subtle than the
short snippet in the API spec.

There's also the problem of differentiating between an empty result
set and one that doesn't have any output columns, e.g.
.fetchall() will (usually) return [] for the first and raise an
exception for the second.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 07 2008)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
            Registered at Amtsgericht Duesseldorf: HRB 46611