[DB-SIG] Improved support for prepared SQL statements

Michael Bayer mike_mp at zzzcomputing.com
Thu Dec 18 21:57:01 CET 2014

> On Dec 18, 2014, at 3:39 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>> That would make the entire feature a non-starter for me.    SQLAlchemy doesn’t hold cursors open beyond a single statement.    My users would very much want a prepared-statement-per-transaction object.
> Perhaps you ought to reconsider this approach. Creating and closing
> cursors all the time does involve somewhat of an overhead.

I will attempt to try this, though I am anticipating that DBAPIs are going to be problematic with this approach.    One concrete example is the case where on psycopg2, we offer the option to use a “named” cursor, which on psycopg2 has the effect of maintaining the state of this cursor on the server side.  However psycopg2 throws an error if such a cursor is used for anything other than a SELECT statement.  So right there, we need more than one cursor based on the contents of the SQL.   This is kind of a very specific situation though, I’ll see if the approach in general produces issues.

> Note that the use of cached prepared cursors for performance
> reasons is only one use of having the prepare step available
> on cursors. In practice, the more important one is to be able
> check SQL statements for errors without executing them and
> possibly causing a rollback on the transaction.

Which kinds of errors are you referring to, if the statement has not been invoked, I would imagine this refers only to syntactical errors?  What kind of application contains SQL that may have syntactical errors that only become apparent at runtime and can’t be eliminated during testing?   

> -- 
> Marc-Andre Lemburg
> eGenix.com
> Professional Python Services directly from the Source  (#1, Dec 18 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/

More information about the DB-SIG mailing list