[Twisted-Python] Using adbapi w/Sybase stored procs

[Allan Streib]

Sybase stored procedures can return tabular results like a SQL query can.
This is unlike stored procedures in Oracle (not sure about other DBMSs)

I found (in 1.0.4) that I could not invoke such a procedure with the
runQuery method.  I developed the following subclasses that add a
"callProc" method which calls the procedure and fetches the results.

I could easily have added these methods to enterprise/adbapi.py but I
don't like to run locally modified distributions so I went the subclass
route for my needs.  Thought I'd share this so that someone could possbly
add it to the distribution.

I used this with the Sybase module for Python from Object Craft[1]

from twisted.enterprise import adbapi
from twisted.internet import defer, threads

class SybaseConnectionPool(adbapi.ConnectionPool):
    def __init__(self, dbapiName, *connargs, **connkw):
        apply(adbapi.ConnectionPool.__init__,
              (self, dbapiName) + connargs, connkw)

    def _callProc(self, args, kw):
        conn = self.connect()
        curs = conn.cursor()
        try:
            apply(curs.callproc, args, kw)
            result = curs.fetchall()
            curs.close()
            conn.commit()
        except:
            conn.rollback()
        return result

    def procedure(self, callback, errback, *args, **kw):
        threads.deferToThread(self._callProc, args, kw).addCallbacks(
            callback, errback)

class SybaseAugmentation(adbapi.Augmentation):
    def __init__(self, dbpool):
        adbapi.Augmentation.__init__(self, dbpool)

    def callProc(self, *args, **kw):
        d = defer.Deferred()
        apply(self.dbpool.procedure, (d.callback, d.errback)+args, kw)
        return d

The code to call a Sybase stored proc then looks like (patterned after the
example in the Twisted Book):

dbpool = SybaseConnectionPool("Sybase", "syb_server", "user", "pass")

class SybaseDatabase(SybaseAugmentation):
    def myProc(self, myArg):
	# A one-argument stored procedure...
        return self.callProc("dbname..my_proc", [myArg])

def gotResults(resultlist):
    """Callback for handling the result of the procedure"""
    print `resultlist`

db = SybaseDatabase(dbpool)
# These will *not* block. Hooray!

db.myProc("foo").addCallbacks(gotResults, db.operationError)

# Of course, nothing will happen until the reactor is started
from twisted.internet import reactor
reactor.run()


References:
 [1] http://www.object-craft.com.au/projects/sybase/

Allan
--
"If you understand what you're doing, you're not learning anything."
                                                -- Anonymous