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
