[DB-SIG] query - prepared statment

[David Rushby]

--- "M.-A. Lemburg" <mal at egenix.com> wrote:
> Perhaps we should something like this to the list of standard
> DB-API extensions ?!
> 
> This is what we have in mxODBC 2.1:
> 
> cursor.prepare(operation)
> 
>    Prepare a database operation (query or command) statement for
>    later execution and set cursor.command. To execute a prepared
>    statement, pass cursor.statement to one of the .executeXXX()
>    methods. Return values are not defined.
> 
> cursor.command
>    Provides access to the last prepared or executed SQL command
>    available through the cursor. If no such command is available,
>    None is returned.
> 
> Looks very similar to what cx_Oracle implements.

kinterbasdb implements similar functionality this way:
---
  cursor.prep(sql_string)
    Prepare a SQL statement for later execution.  Return a
PreparedStatement object that can later be passed as the first
parameter to cursor.executeXXX() instead of a SQL string.
---

I'd definitely suggest the method name "prepare" as the standard
instead of "prep"--I chose "prep" specifically so it wouldn't clash
with the anticipated addition of "prepare" to the standard.

However, I would find the cx_Oracle-style interface that Carsten
described too constraining.  Why not let the user create and manipulate
as many PreparedStatement objects as desired, and let
PreparedStatements expose whatever properties and methods are
appropriate for the database engine, instead of confining the whole
prepared statement concept to "I'm about to execute this statement a
bunch of times, so don't re-prepare it every time".

kinterbasdb.PreparedStatement objects expose properties that contain:
  - the SQL string on which the PreparedStatement is based
  - a code indicating the statement type (insert, update, delete,
select, execute procedure, etc.)
  - the plan that the server will use to execute the statement
  - the number of input parameters
  - the number of output parameters

This information could be crucial to client programs such as database
administrative GUIs, where the Python programmer using the database
module knows nothing about the SQL statements the user will submit, but
needs to examine them programmatically.

How would the cx_Oracle-style interface even expose this functionality?
 cx_Oracle currently exposes a cursor.statement property which contains
the SQL string that was most recently "prepared with prepare() or
executed with execute()".  But the SQL string on which a prepared
statement is based is only one of several potentially useful
properties, so that interface is not rich enough to be blessed as
standard.

Also, isn't it advantageous to allow the client programmer to create
and manipulate any number of prepared statements, rather than just "the
most recent one", as the cx_Oracle-style interface does?  Any
sophisticated database module will cache and reuse multiple prepared
statements internally, so why constrain the public interface to expose
just a single prepared statement at a time?

I'm in favor of standardizing statement preparation, but not with an
interface that precludes all advantages of using prepared statements
except those related to efficiency.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com