[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

On 18.12.2014 21:57, Michael Bayer wrote:
> 
>> 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?   

PostgreSQL is an example where the transaction gets canceled if
you e.g. query a non-existing table in the SQL statement or have
use some other non-existing entity in your SQL.

Since the transaction runs on the connection, all cursors currently
open are affected by this, and this can be a hassle to clean up,
depending on what you're doing :-)

Another use case is having the database tell you more about the
result columns or the data types of the parameters used in the
SQL statement. The latter information is not available through
standard DB-API interfaces, though. Perhaps something to consider
for another optional extension :-)

-- 
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/