[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

On 21.12.2014 19:40, Michael Bayer wrote:
> 
> 
> M.-A. Lemburg <mal at egenix.com> wrote:
> 
>>
>> My experience is from working with ODBC and ODBC drivers.
>> IBM DB2 and MS SQL Server use ODBC as their native database
>> interface API. In ODBC, cursors are called "statements" and
>> you have two modes of operation:
>>
>> a) direct execution, which sends the SQL straight to the
>>   database
>>
>> b) prepare + execute, which separates the prepare from the
>>   execute step
>>
>> You can configure both connections and cursors in ODBC,
>> e.g. the cursor type setting is configured on a per
>> cursor basis and this has direct influence on what locking
>> mechanisms need to be enabled in the database to run the
>> SQL statement:
>>
>> http://msdn.microsoft.com/en-us/library/ms712631%28v=vs.85%29.aspx
>>
>> Note that the DB-API is modeled in many aspects after the ODBC
>> API and its concepts, since ODBC is an industry standard and
>> provides a good common denominator for how database APIs work
>> and which concepts they can support.
> 
> /rant-on
> 
> In general, while ODBC is a good enough standard to serve as the basis for
> general concepts, I hope it doesn’t remain the final arbiter for most/all
> design decisions in the DBAPI, which I hope to see progress at some point to
> even a DBAPI 3. ODBC, while an industry standard, IMHO is slowly becoming a
> relic of the commercial closed-source software industry, of which Python
> itself decidedly stands in contrast towards as a key member of open source
> ecosystem. ODBC is of course widely used for Microsoft SQL Server, the
> commercial database for which it was ultimately designed for, but for the
> ever-more-popular open source databases such as Postgresql and MySQL, while
> ODBC drivers certainly exist, their use is almost unheard of in the
> non-Windows community. I’ve worked heavily with Oracle with Java and Perl
> starting in the 1990s, and even then, ODBC use on non-windows platforms was
> unheard of (specifically with Java we had started with the JDBC-ODBC bridge
> which was terribly unstable, and long ago was trumped by the type-4 drivers
> for JDBC/Oracle). Even the SQL Server and DB2 shops that I work with, again
> because they are hosting their applications from a linux platform, don’t use
> ODBC either, on Python they instead favor the pymssql driver and IBM’s own
> non-ODBC-based IBM-DB driver (https://code.google.com/p/ibm-db/wiki/README).
> ODBC is definitely a standard, but in my experience it’s a stogy old
> standard created by and dominated by closed source solutions, and is
> generally the last choice of the open source developer as specific open
> source drivers are invariably always very difficult and buggy to deal with,
> even hiding behind the DBAPI. IMO it does tend to expose a little too much
> of underlying mechanics, as it is an API that is largely a product of the
> 80’s and 90’s before higher-level VM-based and/or scripting languages had
> achieved wide dominance. To the degree that the DBAPI has already drawn upon
> it is fine, but I would be concerned if the DBAPI is to continue to attempt
> to mold itself as an ongoing ODBC-compatibility layer going forward. 
> 
> /rant-off

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.

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

> That is, the cursor establishes that it will use prepared statements in the
> background, and as long as you pass the same string each time, it uses the
> same prepared statement. The moment you send a different string, now a new
> prepared statement is produced which is used specific to that string. This
> eliminates the need for an awkward “prepare()” step and the introduction of
> new and potentially more awkward APIs. Does that appeal to anyone else here?

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.

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/