[DB-SIG] .prepare()

[Greg Stein]

On Tue, 16 May 2000, M.-A. Lemburg wrote:
> Bill Tutt wrote:
> > > From: Andy Dustman [mailto:adustman@comstar.net]
> > >
> > > Some general digression/spewing:
> > >
> > > Why prepare()? Because, queries take time to parse. Complex
> > > queries take
> > > longer. In most databases, queries are parsed in the server.
> > > If you have a
> > > commonly used query, one that properly factored out the actual query
> > > parameters, having a "pre-compiled" query in the server is a
> > > big win. Even
> > > better is if these queries could have some persistence in the
> > > server so
> > > they could be reused by many clients, which may each supply a
> > > different
> > > parameter set.
> > >
> > 
> > Well, duh... However, there's no reason you need to expose this concept to
> > the user in an explicit API.

Exactly. The whole prepare/reuse thing is performed using the "is" concept
fo the command passed to the .execute() method. The API remains simple and
straightforward; all APIs are implemented and available; DBAPI modules *at
their discretion* can choose to provide additional optimizations.

Throwing prepare/command in there simply serves to toss even more concepts
at the user for them to deal with, think about, and attempt to use in
their software. It creates a higher bar for users in understanding the
API. "but they don't need that -- it is advanced functionality!" you say.
Yah, right. Users will read the whole API; they aren't going to know
what is advanced or not. Even if you mark it that way, they'll still read
through it trying to decide whether *they* are advanced enough to figure
out what it does and whether they should use it.

A good API has few methods and attributes, is simple to understand and
use, and it scales up well to the most demanding tasks.

.prepare() is simply shifting the time when a prepare is done; it provides
no advantages over doing the prepare on the first call to .execute() [and
let additional calls note the same string and reuse the parsed query]

> > example implmentation:
> > 
> > # Dictionary mapping query text -> opqaue prepared query reference (sproc
> > name, underlying db access
> > # mechanism or something entirely different)
> > preparedQuery = {}
> > 
> > def execute(self, cmd):
> >         if not preparedQuery.has_key(cmd):
> >                 preparedQuery[cmd] = conn.prepare(cmd)
> >         else:
> >                 conn.execute(preparedQuery[cmd])
> 
> Don't know about other databases, but ODBC can only prepare
> one statement on one cursor. Once you prepare another statement
> on the cursor, the previous one is lost, so making the currently
> prepared command available via an attribute looked like the
> right way for mxODBC.

Other databases have the same restriction. Bill brain-farted on this one.
:-)

Cursors maintain context -- in particular, the parsed query and the
current result information. They can't deal with multiple, parsed queries.

Cheers,
-g

-- 
Greg Stein, http://www.lyra.org/