[Zope-DB] Getting a cursor?

[Paul Winkler]

Hi folks, first post to this list from me.
The quick question:

I have the impression that there is no standard way to get access to a
cursor from a Zope DA.  
Is that true?

The not-so-quick scenario:

I'm trying to get at an MS SQL Server database, from Zope on Linux.
So far so good - I've got an mx.ODBC demo license, installed unixODBC
and freetds, fiddled around with various configuration, and it all seems
to work now: I can add the egenix DA via the ZMI, and make queries to
the windows box. It all seems to work.

But this database includes one particular stored procedure that I need
to call - and it returns multiple result sets.  But when called via
mxODBCZopeDA, I only get one result set, as per the documentation.
Ouch.

After much docs-reading and experimentation, I've determined that
mx.ODBC supports this use case via some cursor methods, e.g. this works
as expected:

from mx.ODBC import unixODBC
conn = unixODBC.Connect(dsn, user, password)
c = conn.cursor()
c.callproc(procedure_name, params)
while True:
    rset = c.fetchone()
    pprint.pprint(rset)
    print "============================="
    if c.nextset() is None: break


But AFAICT, there's no way to do that with the mxODBCZopeDA product, or
maybe any zope DA product for that matter - because cursors are not part of
the API, and there's no other way to get at multiple record sets.

(I'm not entirely sure what *is* the API of a Zope DA and its
connections.  I see from Shared/DC/ZRDB/dbi_db.py that the connection
class, DB, has a cursor attribute.  But AFAICT the analogous class in
mxODBCZopeDA is mxODBCZopeDA.ZopeDA.DatabaseConnection, and it does not
have a cursor attribute or anything related to cursors in its API. )

mxODBCZopeDA does provide a callproc() method on the connection object,
but it doesn't just call cursor.callproc() - it only returns either the
first or last result set, depending on your configuration. All other
result sets are discarded. Source is not provided so I can't easily
figure out what's going on behind the scenes (that makes pdb kinda
useless too).

Rewriting the stored proc. is not an option, this is a third-party
database.

Any suggestions?

p.s. I'll be asking egenix too, we'll see if they reply to help requests
without a purchased license... but then, if I can't get this to work
there's no point in purchasing one :-]

-- 

Paul Winkler
http://www.slinkp.com