[Zope-DB] Getting a cursor?
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
Re: [Zope-DB] Getting a cursor?
Am 27.09.2006, 23:44 Uhr, schrieb Paul Winkler [EMAIL PROTECTED]: 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 :-] Why shouldn't we answer support requests? You can access most of the mxODBC API on a ZopeDA object through an ExternalMethod. This is indeed above and beyond the ZopeDA API which predates the Python one as far as I know. Call an mxODBC Zope DA object and pass it to your ExternalMethod which can then call it as you would in Python. Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Getting a cursor?
On Wed, Sep 27, 2006 at 11:51:40PM +0200, Charlie Clark wrote: Am 27.09.2006, 23:44 Uhr, schrieb Paul Winkler [EMAIL PROTECTED]: 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 :-] Why shouldn't we answer support requests? Heh :) I was afraid you guys might say something along the lines of Pay up first, you cheapskate. Nice to see otherwise. You can access most of the mxODBC API on a ZopeDA object through an ExternalMethod. This is indeed above and beyond the ZopeDA API which predates the Python one as far as I know. Call an mxODBC Zope DA object and pass it to your ExternalMethod which can then call it as you would in Python. I'll be writing product code, and am currently just experimenting via zopectl debug, so I have full access to everything. But I still can't see how to get a cursor or anything else that will let me retrieve multiple record sets from one STP call. What should I be looking for? ./bin/zopectl debug Starting debugger (the name app is bound to the top-level Zope object) (snip) conn = app.sweetums_test() conn Products.mxODBCZopeDA.ZopeDA.DatabaseConnection DSN=MSSQLdsn thread 47227742061040/47227742061040 at 0x2b0907e8 dir(conn) ['DatabasePackage', 'MAX_SCHEMA_CACHE_SIZE', 'SQL', '__doc__', '__init__', '__module__', '__repr__', '__setstate__', '_abort', '_begin', '_finish', '_init_typecodes', '_lock', '_register', '_registered', '_shortint_converter', '_unlock', '_v_thread_lock', 'abort', 'alive', 'build_query_result', 'callproc', 'close', 'columninfos', 'columnprivileges', 'columns', 'commit', 'connect', 'connected', 'connection', 'connection_string', 'connection_timezone', 'datetime_as_mxdatetime', 'dont_fix_floats', 'errorhandler', 'execute', 'executemany', 'fetch_last_result_set', 'foreignkeys', 'get_connection_info', 'gettypeinfo', 'ignore_max_rows', 'ignore_warnings', 'messages', 'null_as_empty_string', 'options', 'primarykeys', 'procedurecolumns', 'procedures', 'query', 'record_messages_only', 'run_cursor_callback', 'schema_cache', 'set_errorhandler', 'shortint_as_int', 'shortints', 'sortKey', 'specialcolumns', 'statistics', 'tableprivileges', 'tables', 'thread_id', 'time_as_string', 'tpc_abort', 'tpc_begin', 'tpc_finish', 'tpc_vote', 'transactional', 'use_auto_commit', 'use_lazy_connect', 'used', 'zopetype'] I guess I could just use mx.ODBC instead of mxODBCZopeDA, but then I'd lose the nice Zope DA features like transaction integration and DTML support ... -- Paul Winkler http://www.slinkp.com ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
