[DB-SIG] Last round for DB API 1.1

[Andy Dustman]

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.

> 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.

> 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...

> 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. 

-- 
Andy Dustman  (ICQ#32922760)    You should always say "spam" and "eggs"
ComStar Communications Corp.                 instead of "foo" and "bar"
(706) 549-7689 | PGP KeyID=0xC72F3F1D   in Python examples. (Mark Lutz)