Thanks to all those who responded! It was quite educational.
I'm using the zentus java jdbc wrapper. It seems to only support an
index # for the binding index so I'm stuck with being careful as to how
I count ?s.
Is there documentation that talks about about the various binding place
holders or is this a standard SQL construct?
Vance
D. Richard Hipp wrote:
> 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
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
