[DB-SIG] query - prepared statment

[David Rushby]

--- 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." 
It's an arbitrary limitation with dubious benefit.  The DB API doesn't
impose such a limitation in the case of cursors; why do so with
prepared statements?

> David Rushby wrote:
> > 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
>
> cursor.description already gives the programmer information about the
> output parameters.

But currently it's only available after execution.  Would you make
.description available as a result of a .prepare call, even if
.executeXXX hadn't been called yet?

If the standard is going to cover statement preparation, it seems
sensible to me to separate preparation from execution to the extent
that any information available after preparation but before execution
is exposed as soon as possible.

> Input parameters and the query plan may be hard to standardize in
> a meaningful way...

I agree that the query plan would be impossible to standardize.

But standardizing a metadata representation of input parameters
shouldn't be hard.  It's already done for output parameters with
.description, and input parameters shouldn't theoretically be any more
difficult, right?  JDBC supports metadata for both input and output
parameters.

In a prepared SQL statement, both input and output parameters have type
information, even if the client programmer chooses to ignore it and
rely on the database engine's type coercion so as to be able to provide
(e.g.) a string representation of a date instead of an
engine-API-specific date object.  This type information might not
available with an engine such as SQLite, and some engines do provide
less information about input parameters than about output parameters,
but of course, it's already the case that not every feature of the DB
API is supported by every module/engine.  This would just be another
declaration of intent with defined fallback values for engines/modules
that couldn't support every aspect of the feature.

> The semantics of query_plan and statement_type will probably have 
> to be implementation dependent.

Yes.

> I would suggest that a module that implements statement_type should
> also define constants for basic statement types that a statement_type
> can be compared to, similar to how type constants work already.

Sounds fine.

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