Re: [DB-SIG] query - prepared statment
--- Carsten Haese <[EMAIL PROTECTED]> 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 ___ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] query - prepared statment
On Sat, 11 Feb 2006 09:41:41 -0800 (PST), David Rushby wrote > --- Carsten Haese <[EMAIL PROTECTED]> 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." That's a weak analogy. A connection implies a transaction, and requiring the module user to open a second connection to get a second cursor would render the module utterly useless for most cases except extremely trivial ones. On the other hand, asking the module user to obtain a second cursor to prepare two different statements places no undue burden on them. > 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? Of course it's arbitrary, any standard is arbitrary, but it is not a limitation of usability or an impediment to code readability. The proposal of adding an optional .prepare() method and the additional diagnostic attributes on the cursor fits well with the existing API. Adding a whole new class for functionality that can be provided by optional methods and attributes on the cursor is an arbitrary complication with dubious benefit. > > 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. That's the idea. If a module implements .prepare(), .description and the suggested additional attributes (.input_description, .statement_type, etc., if they're implemented) should be made available after .prepare(). I was remiss in not stating this explicitly. > > 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. I agree, it should be possible to apply the same semantics of .description to .input_description. Best regards, Carsten. ___ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] query - prepared statment
--- Carsten Haese <[EMAIL PROTECTED]> wrote: > On Sat, 11 Feb 2006 09:41:41 -0800 (PST), David > Rushby wrote > > --- Carsten Haese <[EMAIL PROTECTED]> 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." > > That's a weak analogy. A connection implies a > transaction, ... A connection only implies a transaction when autocommit is either set to true or when a non-autocommit connection is opened, used, committed/rolled back, and finally closed. But what about the case where a connection is cached, as in a connection pool? Pooled connections are used for multiple transactions over time. In the java world, PreparedStatements are often pooled as well. As such, David's analogy seems valid to me. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ___ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
Re: [DB-SIG] query - prepared statment
On Sat, 11 Feb 2006 15:30:42 -0800 (PST), Jeff Lewis wrote > --- Carsten Haese <[EMAIL PROTECTED]> wrote: > > On Sat, 11 Feb 2006 09:41:41 -0800 (PST), David > > Rushby wrote > > > --- Carsten Haese <[EMAIL PROTECTED]> 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." > > > > That's a weak analogy. A connection implies a > > transaction, ... > > A connection only implies a transaction when > autocommit is either set to true or when a > non-autocommit connection is opened, used, > committed/rolled back, and finally closed. But what > about the case where a connection is cached, as in a > connection pool? Pooled connections are used for > multiple transactions over time. In the java world, > PreparedStatements are often pooled as well. As such, > David's analogy seems valid to me. 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 ;) Best regards, Carsten. ___ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
