[DB-SIG] query - prepared statment

[Carsten Haese]

On Sat, 11 Feb 2006 15:30:42 -0800 (PST), Jeff Lewis wrote
> --- Carsten Haese <carsten at uniqsys.com> wrote:
> > On Sat, 11 Feb 2006 09:41:41 -0800 (PST), David
> > Rushby wrote
> > > --- Carsten Haese <carsten at uniqsys.com> wrote:
> > > > I'm -1 on introducing a whole new class to
> > encapsulate these deeper
> > > > diagnostics.
> > > > [...]
> > > > If the programmer needs more than one prepared
> > statement, nothing is
> > > > stopping them from creating more than one
> > cursor.
> > > 
> > > But this is akin to limiting Connections to having
> > one Cursor open 
> > > at a time, and saying, "if the programmer needs
> > more than one cursor,
> > >  nothing is preventing them from creating more
> > than one connection."
> > 
> > That's a weak analogy. A connection implies a
> > transaction, ...
> 
> A connection only implies a transaction when
> autocommit is either set to true or when a
> non-autocommit connection is opened, used,
> committed/rolled back, and finally closed.  But what
> about the case where a connection is cached, as in a
> connection pool?  Pooled connections are used for
> multiple transactions over time.  In the java world,
> PreparedStatements are often pooled as well.  As such,
> David's analogy seems valid to me.

Analogies and comparisons to java aside, let's take a rational look at the
proposals at hand:

1) Add an optional .prepare() method to the cursor class, and add optional
attributes such as .statement_type etc to the cursor class.

2) Introduce a whole new class that would be the result of a .prepare() call,
that can be passed to .execute(), and that exposes statement type etc.

Both proposals serve the purpose of allowing the programmer to separate
statement preparation and execution, and to inspect the properties of a
statement before executing it.

Proposal 1 is already mostly implemented in at least two DB-API modules
(cx_Oracle and mxODBC), and it can easily be implemented in at least one more
(InformixDB). Proposal 2 exists in one DB-API module, kinterbasdb.

Proposal 1 fits naturally into the existing API. Proposal 2 is a major
addition to the existing API and would require clearing up a lot of open
questions about its semantics: Can PreparedStatements be executed by a cursor
other than the one that prepared them? Can PreparedStatements migrate between
threads? Even if these are easy to answer, there may very well be other open
questions, and none of them are an issue with Proposal 1.

And for all the added complications that come with Proposal 2, it does not
seem to offer any benefits that Proposal 1 wouldn't offer as well.

I remain +1 on Proposal 1 and -1 on Proposal 2.

Marc-Andre, do you have any input on this? You're the one that threw the
snowball that set this avalanche in motion ;)

Best regards,

Carsten.