I agree with Duncan. The original SQL code and a list of bound values. This is a problem we know well and have already solved exactly this way in a different context.
Yes, it would be a good new feature request. Regards David M Bennett FACS Andl - A New Database Language - andl.org -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan Sent: Saturday, 9 January 2016 9:22 AM To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages Okay, I think this clears some things up. On 2016-01-08 11:36 AM, Warren Young wrote: > On Jan 8, 2016, at 12:39 AM, Darren Duncan <darren at darrenduncan.net> wrote: >> >> I interpreted your request as if current systems' error outputs at execute >> time were printing out the problematic SQL statement with placeholder names >> as originally prepared, and you wanted the error outputs to have the >> placeholders substituted with literals for the values passed to them at >> execute time interpolated into them. > > Yes. > >> one can just list the bound values separately / afterwards rather than >> having to rewrite the SQL to interpolate those values. > > Of course, but the question is not whether a caller *can* do this, it?s > whether the caller *should have to* do this. > > From the caller?s perspective, it has already passed ownership of the values > off to SQLite via sqlite3_bind_*(). If an error occurs, the bind calls may > be a few levels separate from the sqlite3_step() call that actually causes > the error. SQLite still owns the values, though, and could provide them in a > hypothetical sqlite3_preview() call, which assembles the effective SQL it > tried to execute and failed. > > You?re asking the caller to maintain separate ownership of data that SQLite > needs just for the error case. SQLite has a much better reason to have a > copy of that data, so it should be providing the values to the error handler, > not the caller. Actually, I agree with you that SQLite should be providing everything. What I disagree with is making the interpolation necessary. The hypothetical sqlite3_preview() should output 2 things, the SQL as originally passed to prepare with placeholders intact, plus a list of placeholder names and their bound values that failed. >> If your problem is that the output simply says an error occurred and doesn't >> print out the SQL, then fair enough, I misunderstood you. > > It?s a bit more than that. The problem is that a given prepared statement is > necessarily generic. Just from looking at the statement in a log file, you > can?t tell what values were used with it, which would help you understand the > context in which it was used. To further explain, I see SQL as a programming language same as C or Perl or whatever. Calling prepare is effectively invoking a compiler on SQL source code where that SQL source defines a routine that may have parameters. Calling execute is then asking to execute that compiled routine where the bind parameters are the runtime-provided arguments to the routine. Do you think it makes sense in any other common programming language that, if a routine fails with a particular set of arguments, that the debugging message includes say C source code rewritten to substitute literals where references to its parameters were? Or does it make more sense for the debugging message to print the actual routine source plus a list of the passed argument values? I am arguing for the latter, all done by SQLite. I consider what is reasonable for SQL to be the same as for other languages. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
