[DB-SIG] mxODBC and Stored Procedures

[Matthew T. Kromer]

on 9/14/01 8:10 AM, Bob Kline at bkline@rksystems.com wrote:

> On Thu, 13 Sep 2001, M.-A. Lemburg wrote:
> 
>> The 3.0 proposal was really more about a proposal for a DBI wrapper
>> around DB API compliant modules. In that sense it did not really
>> target the DB API itself.
>> 
>> Which are the issues with DB API 2.0 ?
> 
> Well, there's one issue surrounding the specification of the
> executemany() method, which requires the implementor to cache an
> unbounded amount of result-set data (breaking the client-server
> paradigm) or to discard that data.  I submitted a patch for the spec,
> and received a reply that it had been applied, but apparently not, as
> the SIG's web site[1] still has the old language.
> [...]
> The solution that comes to mind would be the decoupling of the
> invocation of the stored procedure from the retrieval of the return
> value and output parameters, as (for example) the ODBC API does.
> 
> [1] http://python.org/topics/database/DatabaseAPI-2.0.html

Hmm.  There's a lot you can talk about here.

First, to my mind, allowing executemany() to operate on SELECT statements is
highly problematic, and almost (but not quite) useless w.r.t. just
decomposing it into a loop of SELECT statments.  Clearly, some database
systems can chain result sets together, or the driver can do it -- but I've
yet to see a really compelling reason to support this for SELECTs.  Even the
ability to use executemany() with a RETURNING INTO clause for an update or
delete is problematic.

I would, in some ways, prefer to eliminate the notion of executemany()
iteratively executing the statement, instead emphasizing that it is
providing array input to its statement to operate on multiple rows at a time
(which then begs the question of why API 1's convention of doing it all in
execute() was discarded).  Clearly, array input to SELECT is problematic
(and I have no idea what the various RDBMS solutions do with it).

Second, to stored procedures -- I'm not quite sure I follow -- because it
depends on whether the RDBMS synchronously executes your stored procedure.
Any procedure returning OUT variables should block until those values are
ready.  I would argue that pretty much any RDBMS should block during the
internal execution of a statement, rather than doing an early return and
deferring the start of a subsequent fetch() unless you specifically request
it to operate asynchronously.

Since writing an asynchronous python driver for an RDBMS would be a royal
PITA compared to writing a threaded one, I am getting a trifle confused why
there is a problem.