[DB-SIG] DB API compliance test--clarification

Andy Dustman andy@dustman.net
09 Mar 2003 13:26:06 -0500

On Mon, 2003-02-17 at 03:00, M.-A. Lemburg wrote:
> David Rushby wrote:
> > Code snippet from Stuart's DB API compliance suite:
> > 
> > -----------
> > def test_commit(self):
> >     con = self._connect()
> >     try:
> >         # Commit must work, even if it doesn't do anything
> >         con.commit()
> >     finally:
> >         con.close()
> > -----------
> > 
> > Is it reasonable to commit a transaction without ever having created
> > it?
> You create the transaction implicitly by connecting to the database.

Transactions are implicitly created when you execute a statement that
changes the state of the database (INSERT, UPDATE, DELETE), or retrieves
data (SELECT). Note that in at least some cases (MySQL), CREATE and DROP
TABLE are NOT transactional. Some examples of this:


        The SQL standards-compatible mode, called chained mode,
        implicitly begins a transaction before any data retrieval or
        modification statement. These statements include: delete,
        insert, open, fetch, select, and update. You must still
        explicitly end the transaction with commit transaction or
        rollback transaction. 


        A transaction begins implicitly with the first executable SQL
        statement and ends with either a COMMIT or a ROLLBACK statement,
        or when the program ends.


        Well basically, ADO should do all this for you. Here is what the
        ODBC docs say about transactions:
        Performing Transactions
             In auto-commit mode, every SQL statement is a complete transaction, which is automatically
             committed. In manual-commit mode, a transaction consists of one or more statements. In
             manual-commit mode, when an application submits an SQL statement and no transaction is
             open, the driver implicitly begins a transaction. The transaction remains open until the
             application commits or rolls back the transaction.

You can find some other references via google:


but I like the last one the best. ;)

Andy Dustman         PGP: 0x930B8AB6
    @       .net     http://dustman.net/andy
Freedom isn't free. It's sold to the highest bidder.