transactions and MS SQL Server 7.0

[Geoff Talvola]

jeffcjohnson at my-deja.com wrote:

> Thanks for the info, I'll look into ADO.  I also downloaded mxODBC but
> haven't installed it yet.  Are there pros and cons for each?  ADO makes
> me nervous because it sounds proprietary and who knows when I'll have
> to switch to Sybase or whatever.  I'd prefer to stick to open standards
> unless there is a good reason not to..

ADO has the advantage that it's how database access is typically done in
Visual Basic/VBScript.  This means there is a lot of MS documentation on
how to use it.  Also, while it may be proprietary in the sense that it only
works on Windows, it won't lock you into SQL Server -- any data source that
is accessible through ODBC can be accessed through ADO.  I've used ADO
through Python to access a Sybase SQL Anywhere database, for instance.

There's also a very preliminary version of a DBAPI2.0 wrapper for ADO which
was posted to comp.lang.python last month by Michel Orengo.  I haven't
tried it, but I have a copy of it that I could send you if you can't find
it.  I'm planning on trying it out real soon now.

I haven't looked carefully at mxODBC, but it has the advantage that it it
dbapi compatible, and you can use it on Unix as well as Windows (not
important for me).  On the other hand, I believe that you have to pay for a
license for mxODBC if you plan to distribute applications based on it,
although it is free for use internal to an organization.

Finally, I've also used the odbc module that comes with win32all.  It
doesn't have a lot of fancy features, but for basic execution of SQL
queries it seems to work fine, with two quirks: boolean fields are returned
as strings rather than as integers, and it doesn't handle date/times before
1970 properly.  I believe your original question was how to turn on
transactions.  Try:

db = odbc.odbc('MyDataSource')
db.setautocommit(0)
# do stuff...
db.commit()

This works when connected to an Access database, at least.

--


- Geoff Talvola
  Parlance Corporation
  gtalvola at NameConnector.com