On Mar 19, 2010, at 3:29 PM, David Bicking wrote: > > > --- On Fri, 3/19/10, Vance E. Neff <ven...@intouchmi.com> wrote: > > <snip> >> UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?; >> >> I've never used binding before but have known it is a good >> idea in order >> to avoid injection of bad stuff. >> >> Vance >> > > You count the question marks from left to right. > >> UPDATE table1 set (<1>, <2>, <3>) WHERE col1=<4> and col2=<5>; > > You can also put the index number you want to use after the ? so > they can be in any order you want.
Better still is to use a symbolic name for the parameters. The symbolic names can be any identifier that begins with $, :, or @. Examples: UPDATE table1 SET col1=$c1val, co...@c2val, col3=:c3val WHERE co...@c2val AND col3=:c3val; You still have to translate the symbolic name into a "parameter index" before you bind it. The sqlite3_bind_parameter_index() routine will do that for you. In the programs I write, I always try to use symbolic names for parameters and I rig the infrastructure to handle the mapping from symbolic name to parameter index. For example, if you are using the TCL interface to SQLite, you just specify TCL variables embedded in the SQL: db eval {UPDATE table1 SET col1=$c1val WHERE col2=$c2val} In the statement above, the TCL interface automatically looks up the values of TCL variables $c1val and $c2val and binds them appropriately before running the statement. It doesn't get any cleaner than this. Unfortunately, other programming languages require more complex syntax. In the implementation of "Fossil" I do this: db_prepare(&stmt, "UPDATE table1 SET col1=$c1val WHERE col2= $c2val"); db_bind_int(&stmt, "$c1val", 123); db_bind_double(&stmt, "$c2val, 456.78); db_step(&stmt); db_finalize(&stmt); The db_bind_int() and db_bind_double() and similar routines wrap the sqlite3_bind_xxxxx() and sqlite3_bind_parameter_index() calls. If we've learned one thing over the history of computing it is that programmers are notoriously bad at counting parameters and that symbolic names tend to be much better at avoiding bugs. D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users