(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?
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.
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.
(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?
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.
- 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 third parameter would use number 2 (the first unused id number at this point).
- 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 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.
- 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.
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.
(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
