On Saturday, 04 May, 2013, at 06:59, Stephan Beal <sgb...@googlemail.com> said:
> while working on a DB driver abstraction layer[1] i have come across an > interesting problem which i first thought only affected MySQL[2], but i > now see that i have the same problem in my sqlite3 driver. Consider this > SQL snippet: > > UPDATE foo SET x=:param1, y=:param1 ... > > As i understand it (possibly incorrectly!), the bind-by-param-name API > will properly set both instances of ":param1", but as far as i can find > there is no way to programatically get both indexes: > sqlite3_bind_parameter_index() > will return only the first index: > > http://www.sqlite.org/capi3ref.html#sqlite3_bind_parameter_index Both correct and incorrect: There is *one* parameter named ":param1". The value of this one parameter is being used twice, in two different places within the SQL Statement. The statement is exactly the same as: UPDATE foo SET x=?, y=?1 There is only one parameter, and its index is 1. The purpose of using a "named parameter" is to give a parameter a name. Everywhere that name is used it accesses the single value bound to that name so that you do not have to manually count parameter indexes when composing SQL statements so that you can ensure that two usages of the same parameter reference the same data. It is much simpler to write something like: SELECT * from a,b,c,d where a.a=:a and a.b=:b and c.d=:d and d.a=:b That it is two write SELECT * from a,b,c,d where a.a=? and a.b=? and c.d=? and d.a=?2 This is especially true if you have a much longer statement with multiple references scattered about to the same parameters. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
