[DB-SIG] Improved support for prepared SQL statements

[Michael Bayer]

M.-A. Lemburg <mal at egenix.com> wrote:

> 
> Just to clarify: both MS SQL Server and IBM DB2 use ODBC as their
> native database interface C API.
> 
> They have both extended it to include some special features which
> are not part of the ODBC standard, but in way that's compatible
> with ODBC. Just look at the source code.
> 
> So while you may not see that you're using ODBC, you in fact
> are under the hood :-)
> 
> The only difference is that these database modules are directly
> linked against the driver libraries. We did that as well in
> subpackages of older versions of mxODBC, but found that getting
> things to work in such a direct setup is much more difficult (for our
> users) than using an ODBC manager in between and registering the
> drivers with this subsystem.
> 
> The ODBC manager also makes it possible to plug in different
> drivers easily without having to change the application, and
> it can provide transparent connection pooling, so you stay
> flexible.

I understand the rationale for the ODBC layer of abstraction, but in practice, this is too many layers of abstraction; a driver like pymssql can essentially “curate” all the quirks and surprises that one has to deal with explicitly when using multiple generic ODBC connection layers.   Switching between FreeTDS on Unix and the Microsoft native drivers on windows for the same SQL Server database, pyodbc acts *completely* differently and far worse on linux and even more poorly on OSX.   My experience of ODBC includes much plugging of drivers for sure, but in no case has it ever been “easy” if one isn’t on Microsoft Windows.    That ODBC serves as the native C API for SQL Server and DB2 only supports the assertion that ODBC is very detailed and close to the implementation details.

> 
>> However, getting back to the issue of prepared statements, I will note that
>> specific to MySQL, MySQL’s own DBAPI driver, MySQL-connector-Python, does in
>> fact tie the prepared statement to the cursor:
>> http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html,
>> most expediently as a flag to the connection.cursor() call, “cursor =
>> connection.cursor(prepared=True)”, which is a shortcut for using their
>> CursorPrepared cursor class.     
>> 
>> I actually find MySQL-connector’s API quite appealing here: "The first time
>> you pass a statement to the cursor's execute() method, it prepares the
>> statement. For subsequent invocations of execute(), the preparation phase is
>> skipped if the statement is the same.”
> 
> That's a standard DB-API optimization feature, not special to MySQL.

I see no mention of the “prepare” keyword at https://www.python.org/dev/peps/pep-0249/#cursor …

> 
> Michael, I think you've misunderstood the proposal. The idea is
> to expose the prepare step which normally happens implicitly,
> as an explicit optional intermediate step. The main DB-API
> functionality is not affected by the proposal.
> 
> We're only trying to find a standard for database modules to adapt
> to, which are already exposing the prepare step. Just like we've
> done several times in the past with other extensions which
> several modules implemented in sometimes slightly different
> ways.

Right.  I’m referring to the explicit API of MySQL-connector which introduces a “prepare” keyword argument to connection.cursor() as one possible option, one that already works well without the need for extra method calls and bigger changes in use.

> 
> The aim here is to prevent different semantics from causing
> surprises with the database module users.


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