Re: [sqlite] Question about binding

[Igor Korot]

Keith,

On Sun, Apr 28, 2013 at 2:21 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Exactly ... Both a=? and c=?1 will use the same parameter.  With named
> parameters you would do something like:
>
> Where a = :a and b = :b and c = :a and d = :d
>
> sqlite3_bind_parameter_index(stmt, ":a") -> 1
> sqlite3_bind_parameter_index(stmt, ":b") -> 2
> sqlite3_bind_parameter_index(stmt, ":d") -> 3
>
> named parameters just create a mapping between unique names -> index so
> you do not have to track which index number corresponds to which parameter
> name.  If you have lots of parameters and/or a complicated query you can
> see where this greatly improves maintainability (at a small expense of
> speed and code size).  Many of the traditional types of SQL embeding (for
> example the EXEC SQL macro in older environments) or the tcl interpreter
> (for example) will automatically bind named parameters to local variables
> of the same .  Many SQLite wrappers can map parameters the same way,
> to a dictionary or to object attributes.
>

Thank you for confirmation.


>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Igor Korot
> > Sent: Sunday, 28 April, 2013 14:53
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Question about binding
> >
> > Hi, Keith,
> >
> > On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf <kmedc...@dessus.com>
> > wrote:
> >
> > >
> > > http://www.sqlite.org/lang_expr.html#varparam
> > >
> > > They are what are called Named Parameters.  You use the
> > > sqlite3_bind_parameter_index to look up the index associated with a
> name
> > ...
> > >
> > > ?nnn simply means to use index nnn for that parameter.  Subsequent bare
> > ?
> > > parameter indexes are incremented by 1 from the largest parameter index
> > > thus far used.
> > >
> > > Ie where a= ? and b = ? and c = ?1 and d=?
> > >
> >
> > So it means that:
> >
> > [pseudo-code]
> > sqlite3_bind...( stmt, 1,...);
> > sqlite3_bind...( stmt, 2,...);
> > sqlite3_bind...( stmt, 3, ...);
> > [/pseudo-code]
> >
> > and a and c will have 1, b - 2 and c - 3, right?
> >
> > Thank you.
> >
> >
> > > Has 3 parameters.  A and C use the same bind index and thus the same
> > > parameter.
> > >
> > > ---
> > > ()  ascii ribbon campaign against html e-mail
> > > /\  www.asciiribbon.org
> > >
> > >
> > > > -Original Message-
> > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > > boun...@sqlite.org] On Behalf Of Igor Korot
> > > > Sent: Sunday, 28 April, 2013 13:05
> > > > To: General Discussion of SQLite Database
> > > > Subject: [sqlite] Question about binding
> > > >
> > > > Hi, ALL,
> > > > On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> > > >
> > > > [quote]
> > > > . that matches one of following templates:
> > > >
> > > >
> > > >- ?
> > > >- ?NNN
> > > >- :VVV
> > > >- @VVV
> > > >- $VVV
> > > >
> > > > [/quote]
> > > >
> > > >
> > > > What is the purpose of having "NNN" and "VVV"? Are those standard?
> How
> > do
> > > > I
> > > > use those templates?
> > > >
> > > > Everywhere I see an examples which uses just "?" and no other 4
> > > templates.
> > > >
> > > >
> > > > Thank you.
> > > > ___
> > > > 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
> > >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Keith Medcalf]

Exactly ... Both a=? and c=?1 will use the same parameter.  With named 
parameters you would do something like:

Where a = :a and b = :b and c = :a and d = :d

sqlite3_bind_parameter_index(stmt, ":a") -> 1
sqlite3_bind_parameter_index(stmt, ":b") -> 2
sqlite3_bind_parameter_index(stmt, ":d") -> 3

named parameters just create a mapping between unique names -> index so you do 
not have to track which index number corresponds to which parameter name.  If 
you have lots of parameters and/or a complicated query you can see where this 
greatly improves maintainability (at a small expense of speed and code size).  
Many of the traditional types of SQL embeding (for example the EXEC SQL macro 
in older environments) or the tcl interpreter (for example) will automatically 
bind named parameters to local variables of the same .  Many SQLite 
wrappers can map parameters the same way, to a dictionary or to object 
attributes.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Korot
> Sent: Sunday, 28 April, 2013 14:53
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Question about binding
> 
> Hi, Keith,
> 
> On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
> 
> >
> > http://www.sqlite.org/lang_expr.html#varparam
> >
> > They are what are called Named Parameters.  You use the
> > sqlite3_bind_parameter_index to look up the index associated with a name
> ...
> >
> > ?nnn simply means to use index nnn for that parameter.  Subsequent bare
> ?
> > parameter indexes are incremented by 1 from the largest parameter index
> > thus far used.
> >
> > Ie where a= ? and b = ? and c = ?1 and d=?
> >
> 
> So it means that:
> 
> [pseudo-code]
> sqlite3_bind...( stmt, 1,...);
> sqlite3_bind...( stmt, 2,...);
> sqlite3_bind...( stmt, 3, ...);
> [/pseudo-code]
> 
> and a and c will have 1, b - 2 and c - 3, right?
> 
> Thank you.
> 
> 
> > Has 3 parameters.  A and C use the same bind index and thus the same
> > parameter.
> >
> > ---
> > ()  ascii ribbon campaign against html e-mail
> > /\  www.asciiribbon.org
> >
> >
> > > -----Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of Igor Korot
> > > Sent: Sunday, 28 April, 2013 13:05
> > > To: General Discussion of SQLite Database
> > > Subject: [sqlite] Question about binding
> > >
> > > Hi, ALL,
> > > On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> > >
> > > [quote]
> > > . that matches one of following templates:
> > >
> > >
> > >- ?
> > >- ?NNN
> > >- :VVV
> > >- @VVV
> > >- $VVV
> > >
> > > [/quote]
> > >
> > >
> > > What is the purpose of having "NNN" and "VVV"? Are those standard? How
> do
> > > I
> > > use those templates?
> > >
> > > Everywhere I see an examples which uses just "?" and no other 4
> > templates.
> > >
> > >
> > > Thank you.
> > > ___
> > > 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
> >
> ___
> 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

Re: [sqlite] Question about binding

[Igor Korot]

Hi, Keith,

On Sun, Apr 28, 2013 at 12:34 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> http://www.sqlite.org/lang_expr.html#varparam
>
> They are what are called Named Parameters.  You use the
> sqlite3_bind_parameter_index to look up the index associated with a name ...
>
> ?nnn simply means to use index nnn for that parameter.  Subsequent bare ?
> parameter indexes are incremented by 1 from the largest parameter index
> thus far used.
>
> Ie where a= ? and b = ? and c = ?1 and d=?
>

So it means that:

[pseudo-code]
sqlite3_bind...( stmt, 1,...);
sqlite3_bind...( stmt, 2,...);
sqlite3_bind...( stmt, 3, ...);
[/pseudo-code]

and a and c will have 1, b - 2 and c - 3, right?

Thank you.


> Has 3 parameters.  A and C use the same bind index and thus the same
> parameter.
>
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Igor Korot
> > Sent: Sunday, 28 April, 2013 13:05
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Question about binding
> >
> > Hi, ALL,
> > On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> >
> > [quote]
> > . that matches one of following templates:
> >
> >
> >- ?
> >- ?NNN
> >- :VVV
> >- @VVV
> >- $VVV
> >
> > [/quote]
> >
> >
> > What is the purpose of having "NNN" and "VVV"? Are those standard? How do
> > I
> > use those templates?
> >
> > Everywhere I see an examples which uses just "?" and no other 4
> templates.
> >
> >
> > Thank you.
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Keith Medcalf]

http://www.sqlite.org/lang_expr.html#varparam

They are what are called Named Parameters.  You use the 
sqlite3_bind_parameter_index to look up the index associated with a name ...

?nnn simply means to use index nnn for that parameter.  Subsequent bare ? 
parameter indexes are incremented by 1 from the largest parameter index thus 
far used.

Ie where a= ? and b = ? and c = ?1 and d=?

Has 3 parameters.  A and C use the same bind index and thus the same parameter. 

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Korot
> Sent: Sunday, 28 April, 2013 13:05
> To: General Discussion of SQLite Database
> Subject: [sqlite] Question about binding
> 
> Hi, ALL,
> On the page http://www.sqlite.org/c3ref/bind_blob.html it says:
> 
> [quote]
> . that matches one of following templates:
> 
> 
>- ?
>- ?NNN
>- :VVV
>- @VVV
>- $VVV
> 
> [/quote]
> 
> 
> What is the purpose of having "NNN" and "VVV"? Are those standard? How do
> I
> use those templates?
> 
> Everywhere I see an examples which uses just "?" and no other 4 templates.
> 
> 
> Thank you.
> ___
> 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

[sqlite] Question about binding

[Igor Korot]

Hi, ALL,
On the page http://www.sqlite.org/c3ref/bind_blob.html it says:

[quote]
. that matches one of following templates:


   - ?
   - ?NNN
   - :VVV
   - @VVV
   - $VVV

[/quote]


What is the purpose of having "NNN" and "VVV"? Are those standard? How do I
use those templates?

Everywhere I see an examples which uses just "?" and no other 4 templates.


Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Vance E. Neff]

Thanks Pavel!

Vance

Pavel Ivanov wrote:
>> Is there documentation that talks about about the various binding place
>> holders or is this a standard SQL construct?
> 
> Probably this will help you: http://www.sqlite.org/c3ref/bind_blob.html.
> 
> 
> Pavel
> 
> On Tue, Mar 23, 2010 at 3:48 PM, Vance E. Neff  wrote:
>> 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  wrote:

 
> 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(, "UPDATE table1 SET col1=$c1val WHERE col2=
>>> $c2val");
>>>   db_bind_int(, "$c1val", 123);
>>>   db_bind_double(, "$c2val, 456.78);
>>>   db_step();
>>>   db_finalize();
>>>
>>> The db_bind_int() and db_bind_double() and similar routines wrap the
>>> sqlite3_bind_x() 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
>>
> ___
> 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

Re: [sqlite] Question about binding

[Adam DeVita]

 sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
":repeats"), csrepeated, csrepeated.GetLength(), SQLITE_STATIC);
sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt,
":versy"), verse3, verse3.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, sqlite3_bind_parameter_index(ppStmt,
"@ringy"), rings[ring_verse]);
sqlite3_step(ppStmt);

// :)

sqlite3_finalize(ppStmt);
sqlite3_close(db);
}




On Wed, Mar 24, 2010 at 4:06 PM, Pavel Ivanov <paiva...@gmail.com> wrote:

> Yes, they are the same.
>
> Pavel
>
> On Wed, Mar 24, 2010 at 4:04 PM, a1rex <a1rex2...@yahoo.com> wrote:
> >* :VVV
> >* @VVV
> >* $VVV
> > Are above bindings the same? (Just different prefix to VVV)?
> > Thank you,
> > Samuel
> > 
> > From: Pavel Ivanov <paiva...@gmail.com>
> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > Sent: Wed, March 24, 2010 2:16:34 PM
> > Subject: Re: [sqlite] Question about binding
> >
> >> Is there documentation that talks about about the various binding place
> >> holders or is this a standard SQL construct?
> >
> > Probably this will help you: http://www.sqlite.org/c3ref/bind_blob.html.
> >
> >
> > Pavel
> >
> > On Tue, Mar 23, 2010 at 3:48 PM, Vance E. Neff <ven...@intouchmi.com>
> wrote:
> >> 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:
> >>>>
> >>>> 
> >>>>> 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(, "UPDATE table1 SET col1=$c1val WHERE col2=
> >>> $c2val");
> >>>   db_bind_int(, "$c1val", 123);
> >>>   db_bind_double(, "$c2val, 456.78);
> >>>   db_step();
> >>>   db_finalize();
> >>>
> >>> The db_bind_int() and db_bind

Re: [sqlite] Question about binding

[Pavel Ivanov]

Yes, they are the same.

Pavel

On Wed, Mar 24, 2010 at 4:04 PM, a1rex <a1rex2...@yahoo.com> wrote:
>    * :VVV
>    * @VVV
>    * $VVV
> Are above bindings the same? (Just different prefix to VVV)?
> Thank you,
> Samuel
> 
> From: Pavel Ivanov <paiva...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Wed, March 24, 2010 2:16:34 PM
> Subject: Re: [sqlite] Question about binding
>
>> Is there documentation that talks about about the various binding place
>> holders or is this a standard SQL construct?
>
> Probably this will help you: http://www.sqlite.org/c3ref/bind_blob.html.
>
>
> Pavel
>
> On Tue, Mar 23, 2010 at 3:48 PM, Vance E. Neff <ven...@intouchmi.com> wrote:
>> 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:
>>>>
>>>> 
>>>>> 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(, "UPDATE table1 SET col1=$c1val WHERE col2=
>>> $c2val");
>>>       db_bind_int(, "$c1val", 123);
>>>       db_bind_double(, "$c2val, 456.78);
>>>       db_step();
>>>       db_finalize();
>>>
>>> The db_bind_int() and db_bind_double() and similar routines wrap the
>>> sqlite3_bind_x() 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
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>      __
> Connect with friends from any web browser - no download required. Try the new 
> Yahoo! Canada Messenger for the Web BETA at 
> http://ca.messenger.yahoo.com/webmessengerpromo.php
> ___
> 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

Re: [sqlite] Question about binding

[a1rex]

* :VVV 
* @VVV 
* $VVV 
Are above bindings the same? (Just different prefix to VVV)?  
Thank you,
Samuel

From: Pavel Ivanov <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wed, March 24, 2010 2:16:34 PM
Subject: Re: [sqlite] Question about binding

> Is there documentation that talks about about the various binding place
> holders or is this a standard SQL construct?

Probably this will help you: http://www.sqlite.org/c3ref/bind_blob.html.


Pavel

On Tue, Mar 23, 2010 at 3:48 PM, Vance E. Neff <ven...@intouchmi.com> wrote:
> 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:
>>>
>>> 
>>>> 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(, "UPDATE table1 SET col1=$c1val WHERE col2=
>> $c2val");
>>   db_bind_int(, "$c1val", 123);
>>   db_bind_double(, "$c2val, 456.78);
>>   db_step();
>>   db_finalize();
>>
>> The db_bind_int() and db_bind_double() and similar routines wrap the
>> sqlite3_bind_x() 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  __
Connect with friends from any web browser - no download required. Try the new 
Yahoo! Canada Messenger for the Web BETA at 
http://ca.messenger.yahoo.com/webmessengerpromo.php
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Pavel Ivanov]

> Is there documentation that talks about about the various binding place
> holders or is this a standard SQL construct?

Probably this will help you: http://www.sqlite.org/c3ref/bind_blob.html.


Pavel

On Tue, Mar 23, 2010 at 3:48 PM, Vance E. Neff  wrote:
> 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  wrote:
>>>
>>> 
 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(, "UPDATE table1 SET col1=$c1val WHERE col2=
>> $c2val");
>>       db_bind_int(, "$c1val", 123);
>>       db_bind_double(, "$c2val, 456.78);
>>       db_step();
>>       db_finalize();
>>
>> The db_bind_int() and db_bind_double() and similar routines wrap the
>> sqlite3_bind_x() 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Vance E. Neff]

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  wrote:
>>
>> 
>>> 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(, "UPDATE table1 SET col1=$c1val WHERE col2= 
> $c2val");
>   db_bind_int(, "$c1val", 123);
>   db_bind_double(, "$c2val, 456.78);
>   db_step();
>   db_finalize();
> 
> The db_bind_int() and db_bind_double() and similar routines wrap the  
> sqlite3_bind_x() 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

Re: [sqlite] Question about binding

[D. Richard Hipp]

On Mar 19, 2010, at 3:29 PM, David Bicking wrote:

>
>
> --- On Fri, 3/19/10, Vance E. Neff  wrote:
>
> 
>> 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(, "UPDATE table1 SET col1=$c1val WHERE col2= 
$c2val");
  db_bind_int(, "$c1val", 123);
  db_bind_double(, "$c2val, 456.78);
  db_step();
  db_finalize();

The db_bind_int() and db_bind_double() and similar routines wrap the  
sqlite3_bind_x() 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

Re: [sqlite] Question about binding

[David Bicking]

--- On Fri, 3/19/10, Vance E. Neff  wrote:

 
> 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.

> UPDATE table1 set (?3, ?4, ?5) WHERE col1=?1 and col2=?2;

Which comes in handy if you need to use a value in more than one spot.

As far as I know "set (?, ?, ?)" is incorrect SQL.

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Jay A. Kreibich]

On Fri, Mar 19, 2010 at 03:13:19PM -0400, Vance E. Neff scratched on the wall:
> First of all, I apologize for hijacking the thread!  I did not intend 
> to, I just screwed up!
> 
> Second:
> Question about binding:
> 
> I know how to prepare an INSERT state, for example:
> INSERT into table1 values (?, ?, ?);
> and in the wrapper I am using I can specify the index # (1, 2 or 3) of
> the column and the value in order to set the values.
> 
> The question is:
> How to do this with an update statement?  Is there a way to specify the
> index of the columns used in the where clause?  Such as:
> UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?;

  You can't do that.  You can only bind values where you would
  otherwise use a literal.  In specific, you cannot bind identifiers
  (e.g. column names) or SQL phrases.

  You need to do something like:

  UPDATE table1 SET col1=?, col2=?, col3=? WHERE col1=? AND col2=?


-j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[Pavel Ivanov]

You can do it like this:

UPDATE table1 set col1 = ?, col2 = ?, col3 = ?
WHERE col1=? and col2=?;

And don't confuse indexes of columns and indexes of parameters. To
mention indexes of parameters explicitly I'd suggest to do it like
this:

UPDATE table1 set col1 = ?1, col2 = ?2, col3 = ?3
WHERE col1=?4 and col2=?5;


Pavel

On Fri, Mar 19, 2010 at 3:13 PM, Vance E. Neff  wrote:
> First of all, I apologize for hijacking the thread!  I did not intend
> to, I just screwed up!
>
> Second:
> Question about binding:
>
> I know how to prepare an INSERT state, for example:
> INSERT into table1 values (?, ?, ?);
> and in the wrapper I am using I can specify the index # (1, 2 or 3) of
> the column and the value in order to set the values.
>
> The question is:
> How to do this with an update statement?  Is there a way to specify the
> index of the columns used in the where clause?  Such as:
> 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
>
>
> ___
> 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

[sqlite] Question about binding

[Vance E. Neff]

First of all, I apologize for hijacking the thread!  I did not intend 
to, I just screwed up!

Second:
Question about binding:

I know how to prepare an INSERT state, for example:
INSERT into table1 values (?, ?, ?);
and in the wrapper I am using I can specify the index # (1, 2 or 3) of
the column and the value in order to set the values.

The question is:
How to do this with an update statement?  Is there a way to specify the
index of the columns used in the where clause?  Such as:
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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users