Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Zope: DB

Getting a cursor?

 

 

Zope db RSS feed   Index | Next | Previous | View Threaded


pw_lists at slinkp

Sep 27, 2006, 2:44 PM

Post #1 of 9 (2614 views)
Permalink
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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Sep 27, 2006, 2:51 PM

Post #2 of 9 (2547 views)
Permalink
Re: Getting a cursor? [In reply to]

Am 27.09.2006, 23:44 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:

> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


pw_lists at slinkp

Sep 27, 2006, 3:26 PM

Post #3 of 9 (2532 views)
Permalink
Re: Getting a cursor? [In reply to]

On Wed, Sep 27, 2006 at 11:51:40PM +0200, Charlie Clark wrote:
> Am 27.09.2006, 23:44 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:
>
> >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 0x2aaaab0907e8>
>>> 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 [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Sep 28, 2006, 2:20 AM

Post #4 of 9 (2552 views)
Permalink
Re: Getting a cursor? [In reply to]

Am 28.09.2006, 00:26 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:

> 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 0x2aaaab0907e8>
>>>> dir(conn)

You don't need an explicit cursor as you already have one for your
connection. Simply use callproc()

conn.callproc(procedure_name, params)
while True:
rset = conn.fetchone()
pprint.pprint(rset)
print "============================="
if conn.nextset() is None: break

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


pw_lists at slinkp

Sep 28, 2006, 8:30 AM

Post #5 of 9 (2550 views)
Permalink
Re: Getting a cursor? [In reply to]

On Thu, Sep 28, 2006 at 11:20:13AM +0200, Charlie Clark wrote:
> Am 28.09.2006, 00:26 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:
>
> >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 0x2aaaab0907e8>
> >>>>dir(conn)
>
> You don't need an explicit cursor as you already have one for your
> connection. Simply use callproc()
>
> conn.callproc(procedure_name, params)
> while True:
> rset = conn.fetchone()

No dice, it barfs on that line:

Traceback (most recent call last):
File "<stdin>", line 2, in ?
AttributeError: DatabaseConnection instance has no attribute 'fetchone'

And, as I pointed out in my original post, DatabaseConnection.callproc()
has a different API from cursor.callproc(). Per my experimentation,
cursor.callproc() gets (but does not return) all result sets, which can
then be accessed via cursor.fetchone(), cursor.nextset() etc.

But DatabaseConnection.callproc() simply returns either the first or
last result set, depending on your configuration. All other result sets
are discarded.

Thanks for the help though. Any other suggestions?

--

Paul Winkler
http://www.slinkp.com
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


pw_lists at slinkp

Sep 28, 2006, 9:16 AM

Post #6 of 9 (2551 views)
Permalink
Re: Getting a cursor? [In reply to]

On Thu, Sep 28, 2006 at 06:11:01PM +0200, Charlie Clark wrote:
> Am 28.09.2006, 17:30 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:
>
> >>conn.callproc(procedure_name, params)
> >>while True:
> >> rset = conn.fetchone()
> >No dice, it barfs on that line:
> >Traceback (most recent call last):
> > File "<stdin>", line 2, in ?
> >AttributeError: DatabaseConnection instance has no attribute 'fetchone'
> >And, as I pointed out in my original post, DatabaseConnection.callproc()
> >has a different API from cursor.callproc(). Per my experimentation,
> >cursor.callproc() gets (but does not return) all result sets, which can
> >then be accessed via cursor.fetchone(), cursor.nextset() etc.
> >But DatabaseConnection.callproc() simply returns either the first or
> >last result set, depending on your configuration. All other result sets
> >are discarded.
> >Thanks for the help though. Any other suggestions?
>
> Ah, okay.
>
> You can get a cursor on the connection of the connection object:
> c = conn.connection.cursor()
> c.execute("whatever")
> c.fetchone()
>
> Does that work for you?

Yes! Thank you very much.

Somehow I failed to notice that the connection had a connection
attribute.

--

Paul Winkler
http://www.slinkp.com
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


pw_lists at slinkp

Sep 28, 2006, 9:33 AM

Post #7 of 9 (2553 views)
Permalink
Re: Getting a cursor? [In reply to]

On Thu, Sep 28, 2006 at 12:16:54PM -0400, Paul Winkler wrote:
> On Thu, Sep 28, 2006 at 06:11:01PM +0200, Charlie Clark wrote:
> > Am 28.09.2006, 17:30 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:
> >
> > >>conn.callproc(procedure_name, params)
> > >>while True:
> > >> rset = conn.fetchone()
> > >No dice, it barfs on that line:
> > >Traceback (most recent call last):
> > > File "<stdin>", line 2, in ?
> > >AttributeError: DatabaseConnection instance has no attribute 'fetchone'
> > >And, as I pointed out in my original post, DatabaseConnection.callproc()
> > >has a different API from cursor.callproc(). Per my experimentation,
> > >cursor.callproc() gets (but does not return) all result sets, which can
> > >then be accessed via cursor.fetchone(), cursor.nextset() etc.
> > >But DatabaseConnection.callproc() simply returns either the first or
> > >last result set, depending on your configuration. All other result sets
> > >are discarded.
> > >Thanks for the help though. Any other suggestions?
> >
> > Ah, okay.
> >
> > You can get a cursor on the connection of the connection object:
> > c = conn.connection.cursor()
> > c.execute("whatever")
> > c.fetchone()
> >
> > Does that work for you?
>
> Yes! Thank you very much.
>
> Somehow I failed to notice that the connection had a connection
> attribute.

BTW, I think long-term it would be nice if mxODBCZopeDA grew a configuration
option like "Fetch all result sets?"
but I'm not sure how that would fit with zope's Results class.

--

Paul Winkler
http://www.slinkp.com
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Sep 28, 2006, 10:04 AM

Post #8 of 9 (2546 views)
Permalink
Re: Getting a cursor? [In reply to]

Am 28.09.2006, 18:33 Uhr, schrieb Paul Winkler <pw_lists [at] slinkp>:

>> Yes! Thank you very much.
>>
>> Somehow I failed to notice that the connection had a connection
>> attribute.

Not surprising really. Most people are quite happy with ZopeDA's the way
they are and so we only very rarely get questions on the additional
features of the mxODBC Zope DA.

> BTW, I think long-term it would be nice if mxODBCZopeDA grew a
> configuration
> option like "Fetch all result sets?"
> but I'm not sure how that would fit with zope's Results class.

Maybe, but there isn't a great deal of impetus to do much with Zope DA's
and associated. My understanding is that things will change with Zope 3 as
much more of the underlying driver will be exposed making some of the
cruft of the current stuff redundant.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


dieter at handshake

Sep 28, 2006, 12:56 PM

Post #9 of 9 (2548 views)
Permalink
Re: Getting a cursor? [In reply to]

Paul Winkler wrote at 2006-9-27 17:44 -0400:
>I have the impression that there is no standard way to get access to a
>cursor from a Zope DA.
>Is that true?

True.

But you can get at a cursor for each DA using DA specific implementation
details, as the DA needs a cursor itself.

And as most Zope DA's are open source....



--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

Zope db RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.