[DB-SIG] Clarification of API 2.0 wrt result sets

M.-A. Lemburg mal at egenix.com
Tue Oct 17 21:45:10 CEST 2006

Art Protin wrote:
> Dear folks,
>     Since I could not find a FAQ for the API (other than in the API) let 
> alone find my specific question, I guess I need to present it here.  In 
> its simplest form:
> When should a Cursor object discard a result set?
> After reading and rereading and ... the specification (dated 
> 2006-03-28), I infer from the existence of the method .nextset() that a 
> Cursor may (though not necessarily) queue up multiple result sets.  I 
> found only three (3) methods that produce result sets: .callproc(), 
> .execute(), and .executemany().  Of these, the first two seem to be 
> allowed to produce zero or one result sets and the behavior of the third 
> is "undefined" if it produces even one result set.  None of the 
> descriptions for these three methods tell what to do with any result set 
> from previous invocations.
>     I am attempting to implement an interface that complies with the 
> specification and can support multiple result sets (in a limited fashion 
> by queuing up operations) if that is what a user would expect. 
> Alternatively, I can make each new execution discard the previous result 
> set. 

This is what's intended: each new execute should free up the result
from a previous execute (regardless of whether the method was
.execute(), .executemany(), .callproc() or some other method creating
result sets).

If one of these creates multiple result sets, the first of these
result sets should be made available to the user right after the
call finished.

The .nextset() method is intended to free the current result and
move to the next result set (if any).

> It is even possible to make the .executemany() do both the discard 
> of previous results and queue up to generate result set after result set 
> until either another invocation of one of the three or until .nextset() 
> is invoked enough to go through all of them (presumably with intervening 
> .fetchXXX() invocations.

The behavior of .executemany() with respect to multiple result sets
is undefined, because backends may implement result sets in ways
that don't allow queuing.

In practice, .executemany() should not be used to generate
multiple result sets - it's main purpose is that of doing bulk
inserts. You normally only create multiple result sets as output
of a stored procedure.

>     I think the spec should be revised to either:
> variant I:
>         i) require a call to .nextset() to finish with each result set, and
>        ii) until .nextset() returns False, make each of the three either:
>                 1) queue up to execute after .nextset() finishes with 
> the previous result sets, or
>                 2) raise an exception for Cursor not ready.
> variant II:
>         i) require each of the three to flush all previous result sets, and
>        ii) not return from any of the three until all queries are completed
> variant III:
>         i) have an attribute .queueable that reflects if any of the 
> three will return before the query has been performed, where the attribute
>                   A) may be changed, or
>                   B) is readonly.
>                 (I think it best if the code allows write attempts on 
> the attribute, but but may fail to change it so that the user should be 
> required to check the value after attempting to set it.  This would 
> allow the specification to accommodate those that implement it 
> switchable or with either fixed value.)
> (This goes into more detail than I intended but I do not see what to 
> leave out.)

The above sounds too complicated.

The general idea with multiple result sets is the same as for
cursors - only that you're iterating over result sets instead
of rows.

Hope that clarifies things a bit,
Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Oct 17 2006)
>>> 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,FreeBSD for free ! ::::

More information about the DB-SIG mailing list