[DB-SIG] API Enhancements

Greg Stein gstein@lyra.org
Wed, 03 Mar 1999 06:27:04 -0800


M.-A. Lemburg wrote:
> ...
> Hmm, strings wouldn't indicate order. Cursor level thread safety
> implies connection level and module level.

Sounds reasonable.

> 
> > A module global stating the specification level would also be handy.
> > '1.1' for modules conforming to the DBAPI 1.1 spec. If the global is not
> > present, then the module is a 1.0 module.
> 
> How about: apilevel ?!

Sure.

>...
> > But this update is passed to the execute() method of the cursor object
> > that you're talking about, isn't it? Or do you create a second cursor,
> > which updates the record currently indicated by the first cursor?
> 
> Both are possible. It depends on the database capabilities whether
> this technique is of much use: e.g. you could update all the records
> starting from the 10th row in a result set. Some DBs support scrolling
> cursors in backward direction too, making updates relying on external
> resources (e.g. file in a file system) possible.

No... The semantics of execute() are that it wipes the cursor and starts
a fresh operation. I *really* don't think you can change that.
Therefore, you must use a second cursor to derive any benefit from named
cursors.

> > Isn't that kind of technique a bit difficult without a rigorous
> > specification of the module's fetching behavior? Just because I called
> > fetchone() doesn't mean the cursor is sitting on the first record... an
> > array-fetch may have occurred, and the cursor is 90 rows later.
> 
> Good point. fetchone() ought do what the name implies: fetch exactly
> one record. Otherwise usage of cursors would be pointless anyway.

NO!!!

fetchone() is free to fetch as many as it likes. It merely needs to
*return* one at a time. The name "fetch" is from the viewpoint of the
DBAPI client. NOT the underlying module.

If these named cursors are added, then fetchone() would need to alter
its operation and semantics to rigorously specify where the cursor is.

IMO, this is getting really ugly. I don't see that we should allow these
kinds of varying semantics simply to get something of unknown benefit.

> > By naming a cursor, it would seem that any array-fetching would need to
> > be disabled so that a program can actually determine what record is
> > being referenced by the cursor. For safety, it may even be prudent to
> > disable fetchmany() and fetchall().
> 
> Leave that to the user of the database interface. It should
> be mentioned in the spec. though. fetchmany() may actually fetch
> more records than requested. fetchall() wouldn't make sense
> in this context anyway.

All the methods can fetch as many as they'd like. The only semantics are
on the return amounts. The API provides a way to give the module
array-size hints, but the module is still free to ignore them and
optimize differently.

> > Finally: is there a performance gain to the named-cursor technique?
> > (compared to an UPDATE ... WHERE <primary-key> = <value>)
> 
> Hard to say... it could safe a temporary table though.

I am beginning to believe that these named cursors don't make a lot of
sense, unless there is a way to rigorously specify fetching behavior.
However, we have specifically *avoided* doing this to allow the module
implementor much more freedom.

I would recommend that named cursors and the associated fetching
behavior specification are NOT incorporated into the API specification.

>...
> As Tod already pointed out, the nextset() method does have its
> use for stored procedures: result sets are needed to group data.
> If the interface would just silently move to the next set, there
> would be no way to identify the set boundary.

Agreed.

Cheers,
-g

--
Greg Stein, http://www.lyra.org/