[DB-SIG] Improved support for prepared SQL statements

Michael Bayer mike_mp at zzzcomputing.com
Thu Dec 18 22:42:47 CET 2014

> On Dec 18, 2014, at 4:36 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>> 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.

right, but in practice, those errors are caught in testing, as they relate to the fixed structure of the SQL statement and are therefore part of the application’s code; there is no need for such errors to be recoverable.   Only if your application is a open-ended query builder that exposes the database directly, or it’s some kind of live upgrade of database schemas, would this be an issue; in the latter case you’d be rolling back.

> 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 :-)

Well that doesn’t need to expose a prepared statement to be possible.     The DBAPI spec as is has a lot of problems with this, such as setinputsizes/setoutputsizes, because the typing model in pep-249 is so extremely minimal.  DBAPIs generally don’t try to expand upon that model because there are no guidelines for doing such (e.g., FLOAT / INTEGER subclasses of NUMBER, for example).

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