Dennis,
Thanks. You nicely clarified what the documentation didn't. I'd actually been looking through the code when I got your mail, so I can verify what you say. I'll summarize:
Accepted parameter binding syntax:
? - Positional parameter. This reference is assigned the next unused parameter number, starting from 1
?n - Numbered parameter. n must be a integer <= 999. This reference is assigned parameter number n.
:a - Named parameter. a must be made up of one or more (idChar).
If the the name "a" has never been seen before, this reference is assigned the next
unused parameter number; otherwise it uses the parameter number previously assigned to "a".
(the form :a: is, as you say, not supported, though described in the documentation).
$a - TCL-style variable reference. With regard to parameter number, treated like the
named parameters above.
As in TCL, the variable name allows several forms: $a - a is one or more of alphanumeric and _, or two colons. thus $foo and $foo::bar are valid.
- may end with a TCL array index of form: (iii), where iii is a legal identifier.
thus $foo(1) and $foo(xyz) and $foo::bar(xyz123) and even $foo::bar::car(xyz) are all allowed.
${a} - a is any set of characters except NUL. Nested braces are allowed.
thus ${foo bar 123} is legal.
I think I was most confused by was the use of the word "index" in the documentation where associated with the bind calls. Index is a misnomer. It should really be something like "parameter number" as I've used it above, since index to me implies the index of the parameter reference within the SQL.
James
On Dec 24, 2004, at 8:25 AM, Dennis Cote wrote:
James Berry wrote:
SQLite now supports four types of parameters in its queries, positional parameters, numbered parameters, named parameters, and tcl parameters. While all these forms are allowed, it is expected that different users will use different styles at different times. The compiler is happy to mix tham all together, but users generally shouldn't.(1) Form of wildcards:
? ?N :N: $N At various places in the documentation, all of these seem to be mentioned, though not all consistently. At times N is only a numeric integer, while at others it is a fully alphanumeric identifier. The last ($) form, is hinted at only in the header file, near as I can tell.
Are all of these forms allowed?
Positional parameters use the "?" character by itself. Each one is internally assigned a number as it is encountered while compiling the SQL statement. This internal number is used by the VDBE code to refer to the parameters bound value. Since each positional parameter has its own number, each one's value must be bound seperately.
Numbered parameters used the form "?N" where N is a string of digit characters. The number N is used directly by the compiled VDBE code to refer to the parameters value. This allows the same parameter to be used at multiple locations in the SQL code, and yet have its value set by one bing call.
Named parameters use the form ":alpha" where alpha is a string of characters that form a valid identifier. Each identifier is extracted as the SQL is compiled, and then next unused id is assigned to each unique identifier. If in identifer has already been assigned a number, the same value is used for all subsequent appearances in the query. This allows named parameters to be used multiple times in a single query and have all bound with a single API call.
The tcl parameter format is "$tcl_var". I don't use tcl so I'm not sure what all the allowed tcl_var formats are. They are used to allow tcl variables to be used directly in SQL code (at least I think thats what they do).
For both named and tcl parameters the identifier text after the : or $ must matched
exactly for the same parameter to be used.
There are still referneces in the documentation to a :N: format for numbered parameters that is no longer supported.
Yes. The first positional parameter is assigned number 1. The second is a numbered parameter with the same number 1. They refer to the same bound value.(2) Wildcard/index mapping:
I'm also confused by the mapping in the API between the wildcard and the bind index.
INSERT INTO t (a,b,c,d,e) VALUES (?,?1,?,?666,$foo);
The documentation suggests that the second value would be set the same as the first.
- If I do a sqlite3_bind_int(stmt, 1, 99), will the 1st and 2nd values both be set to 99?
The third parameter would use number 2 (the first unused id number at this point).- To set the 3rd value do I then use index 2, or 3? (In other words, does the aliasing of 2nd value mean that it doesn't count in the indexing scheme?)
The fourth get index number 666. There is a maximum value of 999 allowed by the compiler. Larger values will generate an error when the SQL statement is compiled.- Does the 4th value get set with index 666, or is just an illegal index, or does it get indexed at 4? Or 3?
The fifth parameter is assigned the next highest unused parameter number because it is a new unique tcl parameter. It will be assigned number 667. To use this parameter the user would first call sqlite3_bind_parameter_index() with the parameters name "$foo", and SQLite will return the index number to be used for this variable when calling any of the sqlite3_bind_* API functions.- I assume the 5th value gets indexed at 5, if it's even legal, though I've thrown out a bunch of other possibilities above.
(3) Binding of blob data:
If I bind some arbitary bind data, I assume I shouldn't (and wouldn't want to) do any escaping or quoting of null values, etc. Is that correct?
Yes.
Dennis Cote
