Darren Duncan wrote: > In answer to both Michael Roth and Dennis Cote, I will try to explain > the logic in what D. Richard Hipp presented as the way things > currently are, as I understand the explanation. > > I interpret what DRH said in that SQLite 3 is still using plain > vanilla positional host parameters all around, which while naive is > also very simple and 'lite' for the internal code. > > Each instance of the various formats he allows in SQL like ?|:|$ etc > are just alternative format markings for a sequential > placeholder/wildcard, and each location in a SQL string is given a > unique sequential number in 1..N when it is encountered during the > SQL preparation stage. The second argument to bind() functions still > simply matches a location. The location numbers used with bind() are > all non-sparse. > > I interpret that SQLite 3 does not in fact re-use a bind value when > identical identifier names are used in multiple locations; this is > part of the naivety; we still have to re-bind multiple times > ourselves. > > It is true that we need to do more work ourselves; however, I believe > this is still easily wrapperable in a generic fashion. > > SQLite 3 does provide us the sqlite3_bind_parameter_count() and > sqlite3_bind_parameter_name() functions, which we can use to query > the prepared SQL as to how many placeholders it found, and for each > one, what identifier name we were using in the SQL statement. Those > are info-getting functions, not setting functions. > > Those of us that use the :ident format for host parameter names just > need to have a simple hash table, which can be built and used > dynamically, which we can use to loop through the positional > placeholders and lookup the 'name' that actually is there, and then > bind our value to the placeholder that matches the name. (The > wrapper for Perl would probably use an HV structure, and other > languages would use their built-in equivalent, or a separate simple > hash library.) > > Those who use the plain '?' see a native interface like they expect. > > If support for '?N' was added to this paradigm, then people using > that would still have to do the same app-side lookup table as the >> ident people do, except that their lookup implementation can be > simpler, without a hashing function, and you can use a version of the > sqlite3_bind_parameter_name() that returns an integer rather than a > string for each element. > > AFAIK, the $tcl support works the same way as the :ident, except that > Richard did the app-side work already in the bundled TCL wrapper. > > I will note that, while reusing the same bound value more than once > in the same SQL statement execution is ideal, multiple binding > probably isn't as costly as one thinks. After all, large values in C > are passed by reference anyway; the actual large data block isn't > being copied for each binding, is it? > > In conclusion, if SQLite 3 were to internalize to single-bind, > multiple-use thing, that would mean more complicated internal code. > On the other hand, that may still be the best option, ultimately. > > My apologies to DRH if I mis-interpreted his statements. > > -- Darren Duncan
I don't have time to respond properly right now, but I think it would be a real shame if SQLite adopts this kind of mechanism for named parameters when there is a better alternative. Multiple use, single bind named parameters are the preferred way to go. You even say they are "ideal". The implementation cost of this is a trivially more complex than straight positional parameters. A simple loop calling strcmp() to match the parameter name. There are no changes required to the execution engine. This runtime cost of this support is incurred only once when the statement is prepared. There is no additional cost at execution time. It does not require each application to implement (and possibly get wrong) its own code to map between names and numbers. The SQLite code would be developed once, tested, and would work correctly for all applications. Why force all users to reinvent this stuff over and over?. Heck, I will even volunteer to write the code if that's what's needed.
