Re: [sqlite] Questions about binding
On Dec 24, 2004, at 9:56 AM, Dennis Cote wrote: James Berry wrote: 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. Index was the correct term when it was first used, since SQLite only supported positional parameters at that time. The parameter number was the index of the parameter in the SQL statement. Things have gotten more complicated since then. Now, the term index really refers to the internal array of parameter values that is maintained for each statement. I don't think it means much to talk about the "parameter number" of a named parameter. They should be refered to by their name. To bind a value to a named parameter you need to ask SQLite for the index number to use for that variable when you make the bind call. I agree that index makes a certain amount of sense if/once you understand the internal implementation. But unless you're looking at the code, there's nothing very "indexy" about it, particularly if you mix parameter types, or use numbered parameters indiscriminately. Not that I would suggest someone do so. And also that indexes in C start at 0, not 1. So I came up with the term "parameter number" because it refers to a more generic mapping of parameter <==> number. Let's face it: parameters are referred to by number through the bind interface, not by index. And the mapping of parameter to number is complex. The fact that it's called an index is just the implementation leaking through into the interface. Anyway, call it what you want, we just need to be clear that it's not an index as one would normally think if it. It's a number predictively assigned to the parameter...a parameter number ;) James For numbered parameters, the parameter's number is the index by design (but it could have been different). For positional parameters, you still need to count the parameters in the SQL statement to determine its index. I think index is still the best term, it is just that you need to be clear that it is an index into the array of bound values, not into the text of the SQL statement. Dennis Cote
Re: [sqlite] Questions about binding
James Berry wrote: 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, Index was the correct term when it was first used, since SQLite only supported positional parameters at that time. The parameter number was the index of the parameter in the SQL statement. Things have gotten more complicated since then. Now, the term index really refers to the internal array of parameter values that is maintained for each statement. I don't think it means much to talk about the "parameter number" of a named parameter. They should be refered to by their name. To bind a value to a named parameter you need to ask SQLite for the index number to use for that variable when you make the bind call. For numbered parameters, the parameter's number is the index by design (but it could have been different). For positional parameters, you still need to count the parameters in the SQL statement to determine its index. I think index is still the best term, it is just that you need to be clear that it is an index into the array of bound values, not into the text of the SQL statement. Dennis Cote
Re: [sqlite] Questions about binding
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:
(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
Re: [sqlite] Questions about binding
Eli Burke wrote:
I can't tell you how all those variations behave, but I think that by
far the clearest/simplest functional form is plain :N. I'm sure there
are reasons for the other forms to exist (legacy style? oracle/mysql
compatability?), but from a programmatic standpoint:
INSERT INTO mytable (a, b, c, d, e) VALUES (:1, :2, 0, :3, :2)
sqlite3_bind_int(stmt, 1, 42);
sqlite3_bind_int(stmt, 2, 99);
sqlite3_bind_int(stmt, 3, 144);
is both easy to write and easy to understand when you come back and
look at it in the future.
Eli,
You should use numbered parameters for your queries instead. Like this:
INSERT INTO mytable (a, b, c, d, e) VALUES (?1, ?2, 0, ?3, ?2)
sqlite3_bind_int(stmt, 1, 42);
sqlite3_bind_int(stmt, 2, 99);
sqlite3_bind_int(stmt, 3, 144);
Your example only works because of the order your named parameters appear in
the statement. If you had written your query as:
INSERT INTO mytable (a, b, c, d, e) VALUES (:2, :3, 0, :2, :1)
sqlite3_bind_int(stmt, 1, 42);
sqlite3_bind_int(stmt, 2, 99);
sqlite3_bind_int(stmt, 3, 144);
Then the first and third parameters, :2, would get the value 42 since :2 is
the first named parameter to appear, and it would be assigned index number
1. Similarly the second parameter, :3, would be assigned index 2 and be
bound to the value 99. And finally the last parameter, :1, would be the
third named parameter, assigned index number 3, and get bound to the value
144.
Numbered parameters are slightly more efficient than named parameters
because they don't involve string matching. However, named parameters are
far better at documenting the purpose of the parameter which speeds up
inspection and comprehension when you look at the statements later.
INSERT INTO mytable VALUES (:quantity, :cost, 0, :price, :customer)
sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(":cost"), 42);
sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(":price"), 99);
sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(":quantity"), 144);
sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(":customer"), 10);
Re: [sqlite] Questions about binding
(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. James, I can't tell you how all those variations behave, but I think that by far the clearest/simplest functional form is plain :N. I'm sure there are reasons for the other forms to exist (legacy style? oracle/mysql compatability?), but from a programmatic standpoint: INSERT INTO mytable (a, b, c, d, e) VALUES (:1, :2, 0, :3, :2) sqlite3_bind_int(stmt, 1, 42); sqlite3_bind_int(stmt, 2, 99); sqlite3_bind_int(stmt, 3, 144); is both easy to write and easy to understand when you come back and look at it in the future. -Eli
Re: [sqlite] Questions about binding
James Berry wrote: (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? Correct. The answers to your other questions should be easy to discover by examining the code. You are welcomed to submit documentation patches. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
