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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db