[DB-SIG] Cursor.executemany() and Cursor.nextset()

[Bob Kline]

I'm a little bit puzzled about how the specs intend for the executemany
and nextset methods of the Cursor class to interact with each other.

I can easily picture the standard use cases for the nextset() method.
The user invokes a stored procedure.  The procedure returns more than
one result set.  The nextset() method allows the user to move through
the result sets.  Happens all the time.

I can even imagine the justification for providing executemany() in the
case of data manipulation queries (INSERT, UPDATE, DELETE), though it's
not entirely clear to me that such batching isn't more appropriately
handled by client code.

The problem is that the specification for executemany() does not
restrict its use to actions which do not return result sets.

So, if I were implementing the API, what should my code do if
executemany() is invoked with multiple parameter sets on a stored
procedure which returns multiple result sets?  The choices I can imagine
include:

 1. Ignore the result sets; throw an exception for fecthXXX() and 
    nextset() calls.
 2. Ignore the result sets silently.
 3. Detect the condition and throw an exception from executemany().
 4. Perform invocations of the command for each of the parameter
    sets, without waiting for the fetchXXX() or nextset() calls,
    gathering and caching the data for all result sets.
 5. Delay invocation of subsequent iterations of the command as
    soon as it is detected that a result set has been generated,
    holding off until the results are fetched and nextset() is
    invoked.

None of these options seem very satisfactory, to put it politely.  

In the absence of more specific language in the spec barring the use of
executemany() for commands which produce result sets, it seems at best
dishonest to just ignore the result sets, silently or otherwise, which
would eliminate the first two approaches.

The third option, though the least of the evils listed, is excessively
crude, both in light of the absence of a warning in the spec that such
an exception might be thrown, and because of the indeterminate nature of
the point in time at which the condition would be detected, leaving the
possibility that some, but not all of the invocations might be
performed.

The fourth option could be prohibitively expensive, and neither the user
nor the API implementation has any reliable way of specifying (in the
user's case) or detecting (in the driver's case) when such pre-fetching
of result sets would be required, so it would have to be employed in all
cases, killing the performance for the common case of sequential walk
through the first few rows of a very large result set.

The last option is clearly unworkable, as there is no way for the driver
to tell when fetchXXX() or nextset() calls are to be expected.

Have I missed any obvious solutions?

It seems to me that the API is either overly ambitious or insufficiently
specified (or both) in this area.

I strongly recommend that the use of executemany for commands producing
result sets be deprecated, and eventually forbidden (assuming the method
is retained at all).

I'll be grateful for any insight into solving this problem which I have
missed.

[Sorry if this topic has been covered before.  I didn't see a search
interface for the archives, and though I did a brute force review of the
past few months, didn't see any discussion which solved the problem.
Is there a FAQ document for this SIG?]

-- 
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com