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);
