[DB-SIG] mxODBC and Stored Procedures

[Bob Kline]

On Fri, 14 Sep 2001, Matthew T. Kromer wrote:

> First, to my mind, allowing executemany() to operate on SELECT
> statements is highly problematic, ...

Correct.  In an earlier thread this year I laid out a lengthy
explanation of why this is so.  I submitted a patch which makes it clear
that any invocation of executemany() with statements which produced
results sets constitutes undefined behavior, but it's not clear that it
was actually applied, despite the confirmation I received that it was.

> 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.

There is no need to write an asynchronous driver.  The sequence (from
the client's point of view) is:

 1. Invoke the procedure.
 2. Fetch any results sets.
 3. Retrieve any output parameters (include procedure return value).

ODBC handles this properly.  The fact that the method for invoking the
procedure returns immediately does not mean that the RDBMS is doing any
asynchronous processing.  It's sitting there waiting until the client
end of the connection retrieves any results sets which are produced.
Only then are the output parameters made available.

The secret is to avoid assuming that the invocation of the stored
procedure and the retrieval of the output parameters can be bundled into
a single call at the client.  These two steps must be made available
separately in order to handle the general case properly.

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