[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

On 19.12.2014 00:12, Michael Bayer wrote:
> M.-A. Lemburg <mal at egenix.com> wrote:
>> I think there's a misunderstanding here. A named cursor refers to
>> a database cursor pointing into a result set. Once that result
>> set has been created and the cursor points to it, you cannot
>> run another .execute() against it, unless you first close the
>> named cursor - simply because it is already in use.
> 
> This is in fact why SQLAlchemy works the way it does in the first place;
> cursors have always to me been something that you allocate for a single
> statement’s result set. In the old ASP days, microsoft’s tools always handed
> us a scrollable, updatable cursor, in fact. PG’s named behavior seems
> natural to me and it is surprising to me that you’re suggesting that
> cursor-per-statement is a poor practice.

I'm not saying that having a cursor per statement execution is
poor practice, but it's worth considering using cursors for
multiple statement execution to reduce overhead.

Note that you have to differentiate between client and
server side cursors. Named cursors are normally server side,
whereas the anonymous cursors you typically create using the
DB-API are often client side.

A server side cursor keeps most of the resources on the server,
e.g. it only transfers data to the client when requested.

A client side cursor puts the resources mostly on the client side,
e.g. the database will try to send the complete result set to the
client in one go to allow for fast scrolling through the result set.

Both have advantages and disadvantages, e.g.

http://msdn.microsoft.com/en-us/library/aa266531%28v=vs.60%29.aspx

Whether or not a cursor is client or server side depends on many
things and is generally database backend specific. MS SQL Server
for example will default to server side cursors and revert to
client side for some special cases:

http://msdn.microsoft.com/en-us/library/ms131331.aspx

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 21 2014)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> mxODBC Plone/Zope Database Adapter ...       http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2014-12-11: Released mxODBC Plone/Zope DA 2.2.0   http://egenix.com/go67

::::: Try our mxODBC.Connect Python Database Interface for free ! ::::::

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/