[DB-SIG] Fetching result sets

[M.-A. Lemburg]

"Matthew T. Kromer" wrote:
> > ...
> Well, from the point of view of the Oracle API, "results" aren't fetched
> at all to stored procedures, rather, they're bound before the procedure
> executes, so before execution is complete, the results have to be
> transfered to the client and stored in the bound variables.
> Generally, I was not aware of semantics whereby the output of a
> procedure may be picked up after execution -- although this is probably
> highly RDBMS dependant.

Indeed, it is... and it's certainly not something which a Python
should be made to think about :-)
 
> At least for the OCI driver I've been involved with, in order to execute
> the procedure properly, you must know its  IN and OUT binds before
> calling it (ie a schema describe operation takes place) since there is
> no operation that I'm aware of that would allow you to otherwise 'punt'
> albeit with positional parameters you could probably get close.  Of
> course, I also go through some gymnastics to allow stored procedures to
> be used in a different format, e.g.
> 
>     (out1, out2, ..., outn) =
> cursor.procedures.schema.package.procedure(in1, in2, ..., inn)
> 
> and also to populate a __doc__ string so you can get a human-readable
> idea of what a stored procedure wants, e.g. print c.procedures.find.__doc__

Note: the DB API method would be cursor.callproc(procname, parameters).
 
> On a side note, I've always been a bit dissapointed by how long it takes
> to actually wrap data from the RDBMS to python.  For example, my C layer
> API can retrieve 39100 results from a Zip code table in .7 seconds
> (actually fetching the data into an opaque result set); yet the
> operation of turning these into python values adds an additional 2.4
> seconds -- by the time it goes through all of the python layered above,
> it blows out to about 9 seconds for a fetchall(). Granted, part of that
> is because that code is not highly optimized, but it still irks me.

Well, this is most certainly due to the fact that Python stores
the values in objects rather than a C array. (You could, however,
try to use the array module for faster access to these values.)
 
> It also makes me inclined to add some sort of skip() operator or
> cursors, to do a fast discard of a certain number of results,
> particulary for those type of queries which are generated by web pages,
> e.g. "give me the first 20 results" then "give me the first 40 results,
> and I'll throw away the first 20" etc.  Oracle 9i has introduced a
> scrollable cursor, which I haven't had the time to play with.  I think
> other DBMS systems have had this ability for a while.
 
Again, this is highly DB dependent. I am thinking of adding forward
scrolling to the cursor objects in mxODBC. The ODBC 2.x APIs for this
are horrible, but ODBC 3.x adds some nice new ones which make
the task easier.

I'd suggest using .scroll(offset) as name for such a method.
offset could then be an integer (negative integers scroll backwards
if the DB provides this feature). DBs interfaces not capable of 
scrolling should not implement the method or raise a NotImplemented
exception when called.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
______________________________________________________________________
Consulting & Company:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/