[DB-SIG] DBAPI-2.0 clarifications

Federico Di Gregorio fog@mixadlive.com
Sat, 17 Mar 2001 10:33:45 +0100

hi and thenk you very much for your response, some dubts remain...

Scavenging the mail folder uncovered M.-A. Lemburg's letter:
> Federico Di Gregorio wrote:
> > 
> >   if a db support transactions, how two cursors derived from the same
> >   connection are supposed to see changes done to the db? if cursor A
> >   does an insert, is cursor B supposed to see the change suddenly or
> >   only after a commit() on the connection? imho, it would be better
> This is left undefined in the DB API spec since database usually
> have their own specific idea about how to handle such a situation
> (it is called transaction isolation level in the ODBC docs). The
> transaction isolation level can be specified on a per connection
> basis if the ODBC driver supports this.

yes, i know what isolation level is. my question was about cursors
*derived from the same connection*. now, as you say, isolation level,
where vailable can be set on a "per connection" basis, so, are two
cursors created from the *same* connection **required** to see the
changes immediately or not?

> >   the api is not very clear on what a "set" is. it just says that not
> >   all db support "multiple result sets". imho, it is logical that a call
> >   to executemany() (with a SELECT operation) produces multiple sets.
> >   is this the right interpretation or multiple result sets are something
> >   different? what should happen if a SELECT is passed to executemany()
> >   and the driver does not support nextset()?
> No, .executemany() is there to allow passing multiple rows of
> arguments to the database in one go. The statement is then executed
> for each row of arguments binding those arguments to the parameter
> markers.

exactly as we implemented it in psycopg.

> Multiple result sets refer to the output format and only comes
> into play for stored procedure which may return multiple result
> sets. .nextset() then switches to the next available result set.

ok. now, how are multiple SELECTs derived from calling executemany()
on a SELECT statement supposed to work? all the results should be
concatenated into a single set? or we can extend .nextset() to cover
this situation?

> > 3/ constructor objects
> The constructors are there to wrap the date/time value in a
> way which lets the interface tell the type. Just passing in a
> string wouldn't allow this.

i never said that i want to pass a simple string as the argument.
my question was about how to implement type objects.

> I'd recommend using mxDateTime for this purpose, since it was
> written to implement these constructors.

ok. so i wrap an mxDateTime object inside my Date, Time and 
TimeStamp objects. then, what method should return the quoted
ready-to-be-inserted-into-db string? __reepr__()? __str__()?

> > 4/ closing connections
> No. It should do an implicit .rollback() if possible.

ok. that's what we do at now.

> > 5/ the description field
> > 
> >   how are the precision and scale field to be interpreted? are the fields

nobody knows what precision and scale are? i'll bet on number of significant
digits for precision and number of digits to the right of the decimal point
for scale, but that's *my* interpretation, and i really don't know how to
extract both from the results the db backend sends to me.
> >   mandatory? sometimes obtaining the information in the description is
> >   plain slow (like the display_size, you have to scan every single row
> >   in the result set) or requires accessing system tables. very few programs
> >   use the most esoteric fields (most programs only use type_code and name.)
> >   what about (i know that this will make people flame me) add a new method
> >   to obtain a detailed description and put a shorter description in the
> >   current .description field?
> These two fields can probably be safely set to None (mxODBC does
> this), since today 132-column printers and ASCII terminals
> for formatting result set outputs are not really relevant anymore ;-)

an api specification is there to provide uniformity. the answer 
"These two fields can probably be safely set to None" is, imho, not good
*unless* the api specifies that the two fields are optional. this is
why i am asking all that questions and why (in the future) i will probably
propose a little revision of the API to clarify the obscure points.

thank you again for your answer, ciao,

p.s. a beginning of the test suite is available in the suite/ subdirectory
of the psycopg package. the only well-implemented tests are, at now, the
ones on type singletons (STRING, NUMBER, etc...) but i plan to have a full
suite in a month or so. the suite will be included in psycopg 0.4.7 but
if you want to contribute you can already get it from the cvs, see
http://initd.org/Software/psycopg for more information on our cvs server.

Federico Di Gregorio
MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
                             Best friends are often failed lovers. -- Me