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

[Bob Kline]

On Mon, 6 Aug 2001, Dittmar, Daniel wrote:

Hi, Daniel.  Thanks for your reply.

> > 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 
> 
> - one possible solution was to allow multiple result sets, but not
> to require them. Thus databases which allow batch SELECTs could
> create a single result set, other databases would create one for
> each parameter set. Portable client code would have to check for
> multiple result sets.

This does not address the problem I raised in my previous message.
Perhaps an example will make it clearer what this problem is.
Furthermore, the problem exists regardless of the number of result sets
returned by the stored procedure.

Imagine a stored procedure p which takes two parameters and returns a
single result set.  Client code invokes the stored procedure using the
executemany() method on a cursor object:

    c = conn.cursor()
    c.executemany("EXEC p ?,?", [[40,75], [100,125]])

Now, what does the driver code do?  It uses the first set of parameter
values to invoke the stored procedure once.  So far, its behavior seems
reasonable.  The result set produced by this invocation is available for
retrieval with fetchXXX() calls.  What about the second set of parameter
values?  If the implementation of executemany() proceeds on to invoke
the stored procedure a second time, the result set for the first
invocation will have been discarded, unless the driver fetches all the
data and saves it internally.

This breaks the standard DBMS model of deferring retrieval of a
recordset's data until the client asks for it.  It means that some
operations which would be possible using that model (for example, submit
a query whose result set would exhaust the resources available to the
driver, and incrementally fetch the rows as the client code requests
them) would become impossible.  Surely we don't need a database API
specification today which discards the benefits of the client/server
paradigm.

Suppose, then, that the driver decides to stop after the first
submission of the requested action to the back end, waiting until the
client has retrieved all of the data from that invocation.  If the
client never requests all of the rows in the result set for the first
invocation, then the second invocation will never take place, nor will
any of the DML operations expected for that invocation have happened.

Note that it is not possible in the general case for the driver to
detect whether the 'operation' argument to the executemany() method
represents DQL, DML, or a combination.  In fact, it cannot reliably
predict what a subsequent invocation of a stored procedure will produce
with other parameter sets, based on what it has seen happen for the
first invocation of that same procedure.  The fact that a stored
procedure invocation can produce multiple result sets just complicates
the problem further.

Therefore it seems apparent to me that while executemany() may be safely
useful for DML the API should clearly state that use of this method for
operations which produce result sets constitutes undefined behavior, and
may result in an exception being raised by the driver.

> 
> > It seems to me that the API is either overly ambitious or 
> > insufficiently specified (or both) in this area.
> 
> It is insufficiently specified, but for a reason. 

I'd be interested in knowing what that reason was.

> 
> > 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).
> 
> Depending on the database, there may be no possibility to check
> whether a statement returns a result set short of executing it.

In the general case, this is probably true for all but the most trivial
database products.  However, at the point when the driver recognizes
that one of the invocations of the operation requested by a call to
executemany() has produced a result set it should be free to raise an
exception and the programmer should have been warned by the API's
specification that such an exception is possible (indeed, likely).  His
data may be left in a logically inconsistent state if his stored
procedure mixes DQL and DML, but that wouldn't be surprising for usage
which the API should describe as "undefined behavior."

If you disagree, please tell me exactly what the driver implementation
should do in the case I described above.

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