[DB-SIG] Last round for DB API 1.1
Wed, 17 Mar 1999 23:17:14 +0100
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;
> 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
> > 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:
The updated DB API spec at:
Marc-Andre Lemburg Y2000: 289 days left
: Python Pages >>> http://starship.skyport.net/~lemburg/ :