[Twisted-Python] adbapi and pymssql

[Mike Pelletier]

I am attempting to use pymssql in an new Twisted app I am developing for a 
client (so please don't ask for a lot of detail).  pymssql claims to 
implement "most" of ADBAPI 2.0 and it's not on the list of known supported 
back ends, so I realize I'm pressing my luck a bit here.

Bare pymssql seemed to work well, so I pressed on and attempted to use it with 
adbapi.  Here is what I am using for testing:

from twisted.internet import reactor
from twisted.enterprise import adbapi
from agent.db import makeDbpool

def printResultAndDie(*result):
    print "result:", result
    reactor.stop()

def printErrorAndDie(error):
    error.printTraceback()
    reactor.stop()

import sys
dbpool = makeDbpool()
query = " ".join(sys.argv[1:])
print query
d = dbpool.runQuery(query)
d.addCallbacks(printResultAndDie, printErrorAndDie)
reactor.run()

This reports an error, though it appears on the server that the transaction 
was completely successful.  The exception is thrown by adbapi.py:310, 
"self._rollback(trans)".  This line is itself in an exception handler, and it 
is handling an exception thrown by line 306, "trans._connection.commit()".

A little further testing suggested to me that there is nothing obviously wrong 
with pymssql's commit or rollback features.  The problem seems to be line 
305: "trans.close()".  I guess pymssql doesn't support commits and rollbacks 
after that, which seems almost reasonable.  (Mind, I am in much deeper than I 
have any actual understanding.)

I have locally modified _runinteraction.  I would *really* appreciate it if 
someone took a look at my changes.  I don't understand why close() is 
supposed to be called before commit() or rollback(), so maybe this is a 
completely wrongheaded move on my part.  Here is my version:

    def _runInteraction(self, interaction, *args, **kw):
        trans = Transaction(self)
        try:
            try:
                result = interaction(trans, *args, **kw)
                trans._connection.commit()
            except:
                self._rollback(trans)
                raise
        finally:
            trans.close()
        return result

And for comparison, the original:

    def _runInteraction(self, interaction, *args, **kw):
        trans = Transaction(self)
        try:
            result = interaction(trans, *args, **kw)
            trans.close()
            trans._connection.commit()
            return result
        except:
            self._rollback(trans)
            raise

Thanks in advance!

Mike.