[DB-SIG] Scrollable cursors

Andy Dustman adustman@comstar.net
Wed, 15 Mar 2000 10:12:59 -0500 (EST)

On Tue, 14 Mar 2000, M.-A. Lemburg wrote:

> Andy Dustman wrote:
> > 
> > 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.

Uh, ok.
> > (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...)

It doesn't, yet. I have heard they are coming soon, though, in 3.23.

Problems I have encountered implementing this:

mysql_data_seek(n) seeks on the result to the absolute row n.
mysql_row_seek(row) sets the cursor position to row, where row is a
pointer to the row in memory. mysql_row_tell(row) returns the current row
for later use by mysql_row_seek(). So no method here for relative
addressing. This prompted me to examine the MySQL code. The rows are
stored in memory as a singly-linked list, which is great if you never have
to seek. mysql_data_seek() starts at the beginning of the chain and simply
advances down the chain (in memory, of course) until it is at the correct
row. So forget about this happening in any efficient way.

It would be better (and I may very likely implement this) to simply fetch
all the rows and store them in the cursor and operate on those, because
the result object could then be freed at that point. None of the seeking
stuff has any chance of working if mysql_use_result() was used instead of
mysql_store_result(), anyway. This would probably mean two different
cursor types to be efficient, but this would not be obvious from the
interface (done by cursor()).

andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"