[DB-SIG] Last round for DB API 1.1

[M.-A. Lemburg]

Andy Dustman wrote:
> 
> On Wed, 17 Mar 1999, M.-A. Lemburg wrote:
> 
> > Hmm, but what if I connect to say MySQL via ODBC... the database
> > does not support transactions but still allows .commit() and .rollback()
> > without raising an exception. If the spec would say that the interface
> > *has* to raise an error, I'd have to check prior to every .rollback()
> > to see if the connection actually does do something with it.
> 
> That's a bit of a tricky situation. I'd have to say the when you use MySQL
> through ODBC, then it's a matter of the ODBC interface supporting
> rollback. I'm looking through the myODBC source code and in info.c
> (implementing SQLGetInfo) I see:
> 
>   case SQL_CURSOR_COMMIT_BEHAVIOR:              /* Lie */
>   case SQL_CURSOR_ROLLBACK_BEHAVIOR:
>     *((SWORD*) rgbInfoValue)=SQL_CB_PRESERVE;
>     *pcbInfoValue=sizeof(SWORD);
>     break;
> 
> I guess "Lie" says it all. The ODBC interface says it the database
> supports rollback (a lie), so it does. I think this is a mistake, but one
> we don't have to perpetuate.

I've just implemented a check in mxODBC to check for transactions
prior to actually performing the ROLLBACK. It'll raise an
OperationalError instead (MySQL doesn't cheat on that one ;).

> > Of course, we could add a suggestion to the spec along the lines
> > of "In case the database does not support transactions the interface
> > may throw an exception in case .rollback() is used. This can either
> > be an AttributeError because the interface does not provide the
> > .rollback() method or an OperationalError to indicate the non-ability
> > to perform the roll back."
> 
> I can probably live with this. ODBC is somewhat of a special case, as it
> is a sort of intermediate interface to a lot of different databases. Note
> that according to the Solid docs for SQLTransact, a number of errors can
> occur during either commit or rollback, including "The driver or data
> source does not support the ROLLBACK operation". So a proper ODBC
> interface ought to produce an error resulting in a Python exception (I
> guess an OperationalError) when used with a database that does not support
> it. In fact, I'm willing to drop the AttributeError part and go with
> rollback always generating a OperationError on databases where rollback is
> not supported.

I've added a note that gives the module implementor the two
exception possibilities mentioned above: AttributeError (.rollback()
not implemented) and OperationalError (.rollback() implemented,
but the test is done at execution time).

.commit() should always be implemented -- even for databases
that do not provide transactions.

> > BTW: I've noticed that MySQL support BLOBs but does not handle
> > binary data in those BinaryLongOBjects... is that a bug in their ODBC
> > driver or by design ? [would render the Binary() constructor meaningless
> > I guess...]
> 
> I really don't know very much about their ODBC driver. The main "quirk"
> with MySQL seems to be that all queries are literal, i.e. when they are
> submitted with mysql_real_query(), the whole query with all values is one
> (possibly very large if you are doing multi-row inserts) byte-counted
> string. It is also necessary to escape all strings, whether binary or not,
> with mysql_escape_string, since it must escape ', \r, \n, and \0, and '
> may certainly appear in a non-binary string. It is also fortunate that
> numeric values may be submitted in quotes, since as far as the Python API
> is concerned, DateTime objects are numbers...

Ah, something else I've noticed with the latest version of MySQL:
something in the way of handling time and timestamps changed causing
those values to be corrupted in case they include a seconds fraction
part, e.g. '12:30:45.5' won't make it into the database, '12:30:45.00'
will show up as '00:00:00'. Since the DateTime types produce
strings *with* fraction by default, you'll probably have to use
the .strftime() methods to pass strings without the fraction part
to MySQL.

> > There's a preliminary test script in mxODBC you may want to adapt
> > for your package. I'm using it to test for memory leaks and to
> > check database capabilities (esp. supported data types and SQL
> > syntax for them).
> 
> I'd like to give it a try.

The latest version is here:
	http://starship.skyport.net/~lemburg/mxODBC-pre1.0.2.zip

The updated DB API spec at:
	http://starship.skyport.net/~lemburg/DatabaseAPI-1.1.html

-- 
Marc-Andre Lemburg                               Y2000: 289 days left
---------------------------------------------------------------------
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------