[DB-SIG] query - prepared statment

[M.-A. Lemburg]

Carsten Haese wrote:
> 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 ;)

Since two database modules already have implemented the .prepare()
method more or less, I'd say we go for that in the DB API spec.

Note that if we say that .prepare() doesn't have a pre-defined
return value, module authors are free to have it return one
of the objects mentioned in proposal 2. Furthermore, accepting
a special OperationClass object on input to .executexxx() would
also be possible - as long as standard strings or Unicode are
accepted as well.

One thing should be clear though: the .prepare()
operation will change the cursor state, so the binding between
.prepare() and the cursor is rather tight.

Regarding the name of the attribute holding the prepared or
last executed statement/command/query/operation, I don't have much
of a preference (and yes, you've found a documentation bug
there ;-). This attribute is merely meant to serve as input for the
.executexxx() methods, but you could just as well store the
command somewhere else.

Here's the typical use case for .prepare():

cursor.prepare('select * from sometable')
...
cursor.execute(cursor.command)

It's main purpose is to prepare cursors for the repeated
execution of a statement, detect errors in the
command prior to actually executing it or accessing
meta-data associated with a particular command.

Whether or not this actually works (e.g. errors get raised
during .prepare()) depends on the database:

Some databases don't allow separating the execution from the
preparation of a command. In such a situation, the .prepare()
method would simply set cursor.command to the given command
string.

Other databases delay the actual preparation until you
fetch meta-data.

Yet others, won't give you the meta-data until you actually
execute and fetch data on the cursor.

For most databases, preparation of a command for execution
is a rather time consuming operation, often longer than
the execution itself, so using .prepare() would also make
sense to prepare complicated queries upfront, e.g. on
application startup rather than during request processing.

Note that caching prepared cursor may or may not work:
rolling back or committing a transaction on a connection
may render the cursors created on that connection
unusable.

In summary, I think we should come up with an interface
definition that allows module authors to be creative while
still providing a common basis for writing cross-database
module applications - this is essentially what the DB API
spec has been trying to do all along, while keeping the
API simple enough to implement and use.

I'm not sure about the other attributes that you were
proposing. There's no distinction being made regarding
input and output parameters, so I don't understand why
you'd want a separate .input_description attribute. Dito
for the other attributes.

Information such as whether a certain parameter in a stored
procedure is an input or output parameter or query plans are
not within the scope of the DB API. Module authors should
provide database native means for accessing this kind of
information.

Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Feb 12 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::