[DB-SIG] Preparing statement API

[M.-A. Lemburg]

Greg Stein wrote:
> 
> On Thu, 27 Jan 2000, M.-A. Lemburg wrote:
> >...
> > All true. Still, Hrvoje has got a point there: currently
> > it is not possible to prepare a statement for execution
> > (without actually executing it). This is needed in case
> > you plan to have one cursor per (often used) statement and
> > store these in a cache of your database abstraction
> > layer.
> 
> class PreparedCursor:
>   "This class allows a user to prepare a statement without executing it."
>   def __init__(self, conn, statement):
>     self.cursor = conn.cursor()
>     self.statement = statement
>   def __getattr__(self, name):
>     return getattr(self.cursor, name)
>   def run(self, params):
>     return self.cursor.execute(statement, params)
> 
> Write the code in Python. Don't force DBAPI implementors to write yet more
> code.

The problem here is that syntax errors etc. will not be found
until the command is executed.

Anyway, perhaps you are right in stating that this need not
be part of the DB API spec. I'll keep it for mxODBC though.

Perhaps we should add a section to the DB API spec stating
and specifying common extensions ?!

> If you continue on this track, then the DBAPI is going to have a hundred
> functions. Some of them optional, some required. Implementors will be
> forced to consider each one, deciding whether it can be done with their
> database, whether it should be done, and whether they want to write the
> necessary code to get it done.
 
> Users will have a hundred functions to review and figure out which they
> need, try to determine through feature testing whether the database
> supports it, and write alternate code paths for those that don't. Why
> present the user with a lot of functions to consider, when some may not
> even apply?
> 
> The current design provides for silent, behind-the-scenes, worry-free
> optimization for the cases where a statement is used over and over.
> 
> >...
> > Interface modules not having access to the prepare step of the
> > DB can then implement cursor.perpare(cmd) by simply setting
> > cursor.command to cmd and leaving the actual parsing and
> > prepare step to the execute method as is currently done.
> 
> Or the user can implement a teeny little class like the one that I
> provided above. The DBAPI implementor doesn't have to worry about more
> methods, cursor state, or execution paths through his code.
> 
> The concept of a prepared statements exists solely as a performance
> benefit. We don't have to expand the interface just to achieve each and
> every possible benefit. The current interface does that just fine. And
> users determined to *achieve* those benefits *can*.

Well, not all the way. ODBC separates the two terms "prepare"
and "execute" for obvious reasons: catching syntax errors
is usually not easily done in loops which were written to
execute and fetch data. These already have to deal with
type errors and data truncations. A separate prepare step
allows the application to verify the statement and apply
special error handling related only to the executed command.

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/