-----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, <value_1>)
sqlite3_bind(stmt, 2, <value_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-----
