Re: [sqlite] host parameters / bind variables - further workouts
Earlier I said (to Dennis and the list): > I still have trouble running the tests (error 128 from msys at odd > times that may be tcl subst related) but at least you can build > testfixture and run some tests. I have also reported privately to DRH a problem running tests bigrow-2.2 & bigrow-2.3 Since upgrading from gcc 3.4.0 to gcc 3.4.1 all of these problems seem to have gone away. e
Re: [sqlite] host parameters / bind variables - further workouts
Doug Currie wrote: Friday, August 27, 2004, 10:47:30 AM, Dennis wrote: [...] Next I have to resolve the issue reported on ticket #871, since I'm building under Windows with MinGW. Right now I can't run the test suite. There is a simple workaround; at the end of src/test1.c use #if defined(OS_UNIX) && OS_UNIX Tcl_LinkVar(interp, "sqlite_temp_directory", (char*)_temp_directory, TCL_LINK_STRING); #endif and everything will compile. I still have trouble running the tests (error 128 from msys at odd times that may be tcl subst related) but at least you can build testfixture and run some tests. The correct fix, of course, is to add sqlite_temp_directory to os_win.c. I thought I had done that, but I guess it didn't make it into CVS. I'll fix it as soon as I can. In the meantime, there is always Knoppix -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] host parameters / bind variables - further workouts
From: Michael Roth <[EMAIL PROTECTED]>
D. Richard Hipp wrote:
| The ":N:" style variables were implemented briefly, but never in a
| released version. The latest in CVS supports ":AAA" (alphanumeric AAA
| with no closing colon) because that is what (I am told) is the
| SQL standard.
And what's the fate of "?NNN" (integer NNN)?
Hi all,
I have modified the latest CVS version of SQLite3 to support positional
parameters ("?"), numbered parameters ("?nnn"), and named parameters
(":aaa"). It also allows all instances of a parameter that appear in an SQL
statement to be bound with a single_bind call.
I'm currently testing my changes and trying to prepare additional tests for
the test suite. So far everything works fine. Next I have to resolve the
issue reported on ticket #871, since I'm building under Windows with MinGW.
Right now I can't run the test suite.
I'll be busy with other matters today, but hope to have everything done, and
be ready to submit a patch to Richard this weekend.
Have a good day.
_
MSN® Calendar keeps you organized and takes the effort out of scheduling
get-togethers.
http://join.msn.com/?pgmarket=en-ca=byoa/prem=1994=1034=http://hotmail.com/enca=Market_MSNIS_Taglines
Start enjoying all the benefits of MSN® Premium right now and get the
first two months FREE*.
Re: [sqlite] host parameters / bind variables - further workouts
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: | The ":N:" style variables were implemented briefly, but never in a | released version. The latest in CVS supports ":AAA" (alphanumeric AAA | with no closing colon) because that is what (I am told) is the | SQL standard. And what's the fate of "?NNN" (integer NNN)? -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBLzyGSIrOxc3jOmoRAk70AJ4uspNbYuxi8u324xuKHz//ZWStRgCfQQgN NiBgzUBAZpXaiAW65jHEW2Q= =Qk7n -END PGP SIGNATURE-
Re: [sqlite] host parameters / bind variables - further workouts
On 26 Aug 2004, at 19:15, Darren Duncan wrote:
At 2:39 PM +0100 8/26/04, Matt Sergeant wrote:
I already support sqlite3's numeric placeholders via the standard DBI
API. Switching to non-numeric placeholders will be more complex (I'll
have to use a hash instead of an array to store the placeholders) but
quite doable.
In case I was giving off the wrong idea, I don't mean to lose support
for the positional parameters, but rather to support both posit/named
concurrently.
But yes, the ability to do this would be very powerful, but hopefully
very simple to implement:
...
my $sth = $dbh->prepare(
"SELECT * FROM bar ".
"WHERE baz = :yours OR foo = :mine OR zee = :yours" );
$sth->execute( { 'yours' => 3, 'mine' => 'hello' } );
...
$sth->execute( { 'yours' => 6, 'mine' => 'goodbye' } );
...
Yes. Should be possible - I'll have to switch from an array storage to
hash storage of the parameters, but that's not a huge deal.
For Richard's benefit though, I tested the currently documented: ":N:"
style parameters and I can't compile a SQL statement with those in,
which is a bit worrying (this is with sqlite 3.0.4).
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] host parameters / bind variables - further workouts
At 2:03 PM -0600 8/26/04, Dennis Cote wrote: 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?. I agree that this would be an ideal situation. And if someone does implement it, they will have my praise too. This should be done during the beta phase, of course. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
Michael Roth wrote:
I create a prepared statement with this:
local db = sqlite3.open("somefilename")
local stmt_a = db:prepare("SELECT * FROM test WHERE id=?1")
local stmt_b = db:prepare("XYZ ?1 XYZ ?2 XYZ ?1")
local id = 123
local str = "Hello World"
stmt_a:bind(id)
stmt_a:bind(id, str)
The point is, inside stmt:bind() (which does type conversion and so on)
there is no way to access the variable names used to call stmt:bind().
You could always call bind like this:
stmt_a:bind{ id = 1, name = "John", age = 15 }
and then iterate through the table and bind all the values in the table.
-Tiago
Re: [sqlite] host parameters / bind variables - further workouts
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.
Re: [sqlite] host parameters / bind variables - further workouts
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Darren Duncan wrote:
| 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.
The point is:
If you somewhere do this:
sqlite3_prepare(db, "XXX ?1 YYY ?2 ZZZ ?3")
then, somewhere in your code you do this:
sqlite3_bind(stmt, 1, )
sqlite3_bind(stmt, 2, )
You do this possible inside a function, which justs grabs a variable
argument list and bind each element in the argument list using sqlite3_bind.
Now the point is: If you don't have the ?nnn syntax available, every
time you optimize your SQL statement, maybe you reorder some part of it
to optimize the whole statemnt, every time you do this, you must find
and change all sqlite3_bind() calls that use this statement. Now you
begin to fiddle with maybe multible sqlite3_bind call to bind one
variable twice, or you have to reorder the indeces. That's very error
prone and annoing.
But if you could simply change your old SQL statement:
sqlite3_prepare(db, "XXX ?1 YYY ?2 ZZZ ?3")
to:
sqlite3_prepare(db, "YYY ?2 ZZZ ?3 XXX ?1")
you don't have to bother with countin question marks and so on.
The point is, there are a lot of language in the world, where the
variable name just isn't available.
I'm saying this, because I wrote a very complete wrapper for sqlite3 in
lua (not released yet, but comming soon). The problem is the follwing:
I create a prepared statement with this:
local db = sqlite3.open("somefilename")
local stmt_a = db:prepare("SELECT * FROM test WHERE id=?1")
local stmt_b = db:prepare("XYZ ?1 XYZ ?2 XYZ ?1")
local id = 123
local str = "Hello World"
stmt_a:bind(id)
stmt_a:bind(id, str)
The point is, inside stmt:bind() (which does type conversion and so on)
there is no way to access the variable names used to call stmt:bind().
So, I have every time to change the stmt:bind() calls, when the SQL
changes, and second, maybe the caller has to use a ordering to
stmt:bind() which isn't nice. For example:
stmt_b:bind(str, id, id, str, id)
or something like this.
I pray to Dr. Richard Hipp to not remove the ?nnn binding. Please don't
do it. Please.
Michael Roth
-BEGIN PGP SIGNATURE-
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBLkCRSIrOxc3jOmoRAiLAAJ98lktNrzgcCmu4xuyZA9FKJK30fQCcDEJB
942JQ3A3Ge7rj2Rtxz+VlaY=
=kxrE
-END PGP SIGNATURE-
Re: [sqlite] host parameters / bind variables - further workouts
At 2:39 PM +0100 8/26/04, Matt Sergeant wrote:
I already support sqlite3's numeric placeholders via the standard
DBI API. Switching to non-numeric placeholders will be more complex
(I'll have to use a hash instead of an array to store the
placeholders) but quite doable.
In case I was giving off the wrong idea, I don't mean to lose support
for the positional parameters, but rather to support both posit/named
concurrently.
But yes, the ability to do this would be very powerful, but hopefully
very simple to implement:
...
my $sth = $dbh->prepare(
"SELECT * FROM bar ".
"WHERE baz = :yours OR foo = :mine OR zee = :yours" );
$sth->execute( { 'yours' => 3, 'mine' => 'hello' } );
...
$sth->execute( { 'yours' => 6, 'mine' => 'goodbye' } );
...
-- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
D. Richard Hipp wrote: > Parameters can be in any of three forms: > > * > * > * I repeat the other calls for continued (or added) support of the ?n style of numbered parameters. They can of course be replaced by named parameters using names like :Pn but that involves extra overhead in SQLite to handle a name for the parameter when only its number is really used. > > Each parameter is assigned a number. Numbers are sequential from left > to right and begin with 1. The parameter number is used to bind > values to the parameter. All parameters get a different number, even > those with identical names. Why do all parameters, even those with the same name, get a different number? Doesn't this imply that I need to bind each of these parameters separately? This seems to defeat the purpose of having named parameters. I want to use the same parameter value at multiple places in the SQL statement. I should be able to use the same named parameter multiple times, and then bind a value to that parameter once before executing the statement. > The sqlite3_bind_parameter_count() API returns the number of > parameters in a compiled SQL statement. > sqlite3_bind_parameter_name() returns the text of a particular bound > parameter. Just for clarity, I assume you mean that sqlite3_bind_parameter_name() returns the text of the parameter name, and not a text representation of the value bound to that parameter. Your description isn't clear to me. I think there should also be an API function int sqlite3_bind_parameter_number(const char* name) that returns the parameter number assigned to the named parameter. This number is then used with the existing bind_parameter calls to bind a value to that parameter. This one (actually two because of the two string types)API function prevents the need for an second set of bind_parameter calls that take the parameter name as an argument. > The is not standard SQL. It is an > extension. SQLite supports many other non-standard extensions in > its lexer, including things like the use of [...] to quote identifies, > the ability to use certain keywords (ex: DESC, BEGIN, VIEW) as > the names of tables or columns, and a very broad understanding of > what it means to be an identifier so that characters from non-latin > character sets can be used in identifer names without quoting. > is yet another extension. Nobody > is forced to use it if they do not want to. It only consumes 182 > bytes of compiled code space (i486 with GCC) and it makes life much > nicer for TCL programmers, so I think it is well worth including. I don't have any problems with extensions as long as the basic functions support the SQL standard (i.e. ) where ever possible. I'm sure this will be useful in the test suite if nothing else.
Re: [sqlite] host parameters / bind variables - further workouts
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
D. Richard Hipp wrote:
| Parameters can be in any of three forms:
|
| *
| *
| *
|
| Each parameter is assigned a number. Numbers are sequential from left
| to right and begin with 1. The parameter number is used to bind values
| to the parameter. All parameters get a different number, even those
| with identical names.
I vote for:
*
*
*
*
Only question mark parameters should get a number, one greater as the
last used. The identifier and tcl variable name parameters shouldn't get
a number. The rational behind this is:
There are two possible languages that use sqlite. The first group of
language known nothing about names used for variables, e.g. 'C'. The
second group known somehow the names of the variables used, e.g. TCL and
Phyton I think.
So, in a language, where you don't have access to your variable names,
you could write a function like:
bind(stmt, args)
for i = 1 to number_of_items(args) do
sqlite3_bind_xxx(stmt, args[i])
end
end
You use this wrapper from your language like this:
bind(stmt1, "Hello World")
bind(stmt2, id_variable, str_variable, "Some constant")
In language where you have access to the names of variables you could
write a function like:
bind(stmt, args)
for key, value in all_pairs(args) do
sqlite3_bind_xxx(stmt, key, value)
end
end
You use this wrapper like this from your language:
bind(stmt1, name = "Hello World")
bind(stmt2, id = id_var, str = str_var, foobar = "Hello")
or: bind(stmt2, foobar = "Hello", id = id_var, str = str_var)
But if you omit the ?nnn syntax and count all parameters in the sql
statement, is isn't nice to use sqlite from languange of the first example:
stmt = sqlite3_prepare("SELECT xxx WHERE x=:id and y=:id)
You don't know in these language that the first paramter :id is the same
as the second paramter :id. And you can't use the variable names of the
language itself, to bind the value. Instead you must do something like this:
bind_some_statement(stmt, id)
sqlite3_bind_xxx(stmt, 1, id)
sqlite3_bind_xxx(stmt, 2, id)
end
bind_some_other(stmt, id, content)
sqlite3_bind_xxx(stmt, 1, id)
sqlite3_bind_xxx(stmt, 2, content)
sqlite3_bind_xxx(stmt, 3, id)
sqlite3_bind_xxx(stmt, 4, id)
sqlite3_bind_xxx(stmt, 5, content)
end
The point is, you can't write an nice and univeral wrapper. That's not a
good thing, I think.
Michael Roth
-BEGIN PGP SIGNATURE-
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBLba1SIrOxc3jOmoRAhr9AKCg3GYPMx/ZOFvIcmmA4gjnhgYXAACfY138
DUasCesoCJufsgFHaGebQ7Y=
=8IFs
-END PGP SIGNATURE-
Re: [sqlite] host parameters / bind variables - further workouts
On Wed, Aug 25, 2004 at 09:46:38PM -0700, Darren Duncan wrote: > > While I see this issue now closed, following Richard's explanation of > how things actually are working now, I'm curious as to where in the > SQL:2003 standard it mentions positional host parameters and '?'; > please give a reference; I only saw the named host parameters > mentioned in the standard. -- Darren Duncan ISO/IEC 9075-2:2003 (E) DRAFT 4.24 Dynamic SQL concepts Many SQL-statements can be written to use parameters (which are manifested in static execution of SQL statements as host parameters in s contained in s in s or as host variables in s contained in s). In SQL-statements that are executed dynamically, the parameters are called dynamic parameters (s) and are represented in SQL language by a (?).
Re: [sqlite] host parameters / bind variables - further workouts
At 11:29 PM -0400 8/25/04, Matt Wilson wrote: Not only backwards compatibility, but standards compliance as well. While I see this issue now closed, following Richard's explanation of how things actually are working now, I'm curious as to where in the SQL:2003 standard it mentions positional host parameters and '?'; please give a reference; I only saw the named host parameters mentioned in the standard. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
On Wed, Aug 25, 2004 at 06:22:26PM -0700, Darren Duncan wrote: > > For "backwards compatability", any plain '?' could still be allowed, > and be mixed with both other usages, and each '?' occurance would > implicitly be the same as ?1, ?2, etc. Not only backwards compatibility, but standards compliance as well. Cheers, Matt
Re: [sqlite] host parameters / bind variables - further workouts
At 9:34 PM -0400 8/25/04, D. Richard Hipp wrote: Parameters can be in any of three forms: * * * Each parameter is assigned a number. Numbers are sequential from left to right and begin with 1. The parameter number is used to bind values to the parameter. All parameters get a different number, even those with identical names. The sqlite3_bind_parameter_count() API returns the number of parameters in a compiled SQL statement. sqlite3_bind_parameter_name() returns the text of a particular bound parameter. This implementation is very simple and compact. And with the exception of not supporting parameters, the implementation is sufficient, I believe to efficiently emulate all of the behaviors described by Darren and Dennis. Support for can be added in the future if a genuine need appears. That sounds great, Richard, thanks! I suggest that this may be a good time to put out an official 3.0.5 release, which is still *beta* status, so more people can thorougly test the addition of :identifier and removal of [?NNN, :NNN:]. This is a *significant* change. Matt Sergeant, when this happens, please release a corresponding DBD::SQLite 1.05, which provides proper pass-thru access for both the ? and :identifier binding methods. DBI itself declares ? in its standard interface, which each DBD either passes through to a natively supporting DB, or emulates; you probably used to emulate ? with SQLite2, but can do pass-thru with SQLite3. But if the newer DBI doesn't have a corresponding native API for :identifier, that takes a Perl hash ref as an argument, then please have passthru support for that via an extension in appropriate places, particularly having the $sth->execute() argument allowing a hash ref instead of a list. (In any event, I will petition for the standard DBI API to include :identifier support, if it doesn't already.) Also, the SQLite core documentation at http://sqlite.org/capi3.html and other places will have to be updated to include the changes. It does not yet mention the bind_parameter_count/name() functions or :identifier, and does mention the old NNN formats. Thank you very much to everyone for their hard work. Good day. -- Darren Duncan
Re: [sqlite] host parameters / bind variables - further workouts
At 6:31 PM -0600 8/25/04, Dennis Cote wrote: The application should not be setting the mapping between the parameter names and their index numbers. This should be done automatically by SQLite as it parses the SQL statement. As each named parameter is encountered SQLite should scan the parameter table to see if this name has already be assigned an index number. If it has, then that number is used for this parameter in the VDBE code that is being generated. If not, then this is a new parameter which is assigned the next highest unused index number automatically. The reason I suggested having an application function for just explicit mapping is so that we can save on a bloat of bind() functions. There are about 7-10 bind functions now, one for each of the data types of values being bound, and what host params they are being bound to is given as an integer, the second argument. If we ditch the extra function I mentioned, then we will need a set of bind() functions each of whose second argument is the string param name instead of the integer. And twice if we want UTF-8 and UTF-16 versions. A good 15 extra functions. If there is a concern with cost of scanning the table for each named parameter, this could be replaced with a hash based lookup using SQLite's existing hash functions. I doubt that this will beneficial for anything but extreme cases with very many named parameters. In either case this process only takes time when preparing the statement. There is no additional overhead when executing a precompiled statement multiple times. A lookup hash will, of course, still be used by SQLite to map the :ident in the SQL statement and the names given by host_param_name(). And the value for a :foo used more than once in a SQL statement will still be bound exactly once and used multiple times, just as ?N is. So the question is, would we rather have 1-2 extra host_param_name() functions, or 14-20 extra bind() functions? Of course, the 14-20 may actually be better, and if we actually want to do it that way, then it's all great news to me; and that approach does mean exactly one function call by the app instead of 2. (FYI, the named parameters, as given in my #1, and the SQL standard, is all I ever plan to use myself, and if it were to come to that being the only means provided, I would be fine with it. The main advantage of the positional numbered options is speed in some environments, or maybe not.) Would anyone suffer if the plain '?' were just dropped entirely, and :ident + ?N were the only options? Both of the remaining would work great for generated SQL; in fact, with my own SQL generator, named params are dead easy to deal with, but purely positional plain-? ones are an order of magnitude more difficult. Even with hand-coded SQL, plain '?' are a pain in all but the most trivial statements, since programmers have to be really careful to get their lists of bind values exactly matched up correctly with the '?', with additional trouble if we want to edit, and that disallows use of the same value multiple times. Plain '?' is generally a detriment to troubleshooting. So then, if we drop bare '?' entirely, then that will let us conceptually or actually make both the ?N and the :ident into hash keys in a sparse list. The second argument to the existing bind() would no longer be an array index per se. Moreover, the ?N and :ident can be conceptually separate lists, where elements from both can be used in the same SQL statement. The ? or : would just tell SQLite how to treat the characters following, as an integer or identifier. For "backwards compatability", any plain '?' could still be allowed, and be mixed with both other usages, and each '?' occurance would implicitly be the same as ?1, ?2, etc. -- Darren Duncan
[sqlite] host parameters / bind variables - further workouts
I have some further suggestions to workout regarding host parameters / bind variables, sort of bringing things together as it were, so they can be addressed during the current SQLite 3 beta phase. Note that I don't have any current SQLite 3 code in front of me, so the following is more a description of concepts and/or references to the documentation on the sqlite.org website; some of my suggestions may be identical to things already done, and some may not be, I don't know which is which yet. First of all, I believe that, to serve the most needs, SQLite should accept host parameter references in SQL strings in exactly 3 formats: 1. by name, as the SQL standard dictates, looking like: 2. by bind var array position name, looking like eg: - or possibly like eg: - not recommended since looks to similar to "by name" - but probably not like eg: - only works if a delimited can never be an integer, but it can be 3. by pure wildcard position, looking like: There would be a single root implementation where all host parameters are referenced by a 1..N index number, which is the second argument to the sqlite3_bind_*() functions. All actual binding operations would continue to use these as if #2 was the only option. This single root implementation would be like "named" such that each host parameter can be referenced any number of times in a SQL string, and in a different order than their binding index, but that the "identifier" is always an integer. As an internally-implemented thin wrapper on top of this, #1 would lead to the addition of one conceptual function or two actual functions like this: int sqlite3_host_param_name(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_host_param_name16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); Calling one of those functions would map a character string / "text" to one of the "named integer" host parameters so that they effectively become synonyms. For example, calling: sqlite3_host_param_name(stmt, 1, 'foo', ...); ... would result in a statement "SELECT :foo AS a, ?1 AS b" having the same value in 'a' and 'b'. A caller application would use #1 or #2 in exactly the same way, except #1 has a single extra step of calling host_param_name also. Of course, it doesn't matter whether host_param_name() or bind() is called first; they both just have to be before the execute(). The #3 above would also be an internally-implemented thin wrapper over #2. Any time the parser encounters a plain '?', it associates that position with the next consecutive index. A caller application makes exactly the same number of function calls for #3 as they do #2. As far as formatting goes in SQL strings, I think #1 and #3 are nailed down perfectly. But I still think there is room for improvement in #2. For example, unless you have a vested interest in multiple aliases such as both ?N and :N:, I suggest getting rid of one of those, or getting rid of both and picking a third option that looks markedly different from both #1 and #3. If you keep an existing one, I suggest the ?N version. Otherwise, have just a leading sigil (no trailing), for consistency, and make it something other than ? and :. And of course, anything host language specific, TCL or otherwise, shouldn't be in the core, so no $. Once again, my apologies if this post is redundant, but I thought it easier to describe a target rather than simply a delta. -- Darren Duncan
