[DB-SIG] Scrollable cursors

[M.-A. Lemburg]

Andy Dustman wrote:
> 
> On Tue, 22 Feb 2000, Christopher Petrilli wrote:
> 
> > Cary Collett [cary@ratatosk.org] wrote:
> > >
> > > For me LIMIT is the big one:
> > >
> > > SELECT ... LIMIT 4 (return the first 4 results)
> > > SELECT ... LIMIT 3,5 (return the 4th through 9th rows)
> >
> > Correct behaviour of scrollable cursors will get you this in a MUCH
> > more advanced form :-)
> 
> Scrollable cursors are not (yet) part of the DB API spec. However, it
> occurred to me in doing some MySQLdb updates that MySQL effectively has
> scrollable cursors using mysql_data_seek(result, offset), where offset is
> a relative offset. (Although, this is limited to clients that use
> mysql_store_result(), which MySQLdb does, as opposed to
> mysql_use_result(). Either way, the LIMIT SQL extension for MySQL will
> reduce the number of rows which are send back to the client.)
> 
> So, are there any DB API interface which employ scrollable cursors to this
> date? If not, I propose the following semantics:
> 
> cursor.seek(n) -- scrolls the cursor to row n, with the first row being 0.
> If the database does not support seekable cursors, this method should not
> be defined. If database cannot support seeking under current conditions,
> it should raise NotSupportedError. (Similar to the cursor.rollback()
> language)

Change this to "raises an AttributeError or a NotSupportedError"
-- this is how the other APIs work too: if the database doesn't
provide this interface, don't define the method; if this information
is dynamic, raise a NotSupportedError when it is called.

BTW, ODBC defines these fetch operations:

SQL_FETCH_NEXT 
SQL_FETCH_PRIOR 
SQL_FETCH_FIRST 
SQL_FETCH_LAST 
SQL_FETCH_ABSOLUTE 
SQL_FETCH_RELATIVE 

Most of these should be doable with your addition of whence,
but there is no way to say "jump to last row". ODBC 3.0 has
a nice API for this too: SQLFetchScroll() which I could use
(the older one SQLExtendedFetch() is rather complicated to
use right...).

> cursor.seek(n, whence=0) -- if whence=0, scrolls the cursor to absolute
> row n, else perform a relative scroll. There are some difficulties in
> doing the relative scroll, but I hope I can get around them.

> Does this sound okay? The next release is not immininent, but I'd just as
> soon not have to change it if I don't have to.

Would be cool indeed :-)

> (Incidentally, the next version of MySQLdb will recognize when MySQL
> supports transactions, and define commit() and rollback() accordingly.)

Since when does MySQL support transactions ? (I would be very
interested in this...)

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/