Good day,
For the sake of fun, I have to share this - especially with tall this talk
of binding all the parameters.
void poem(CString pth)
{
sqlite3_stmt *ppStmt; //statement pointer
sqlite3 *db; //database
const char *pzTail;
char *pzerr;
if( sqlite3_open(pth, &db) ){
printf("Can't open database!" );
sqlite3_close(db);
return ;
}
CString csql;
csql.Format("Create table if not exists poem (verseno integer primary
key, rings int, location text)"); //sets the string.
int status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16
encoded */ csql.GetLength(), /* Maximum length of zSql in bytes. */
&ppStmt, /* OUT: Statement handle */
&pzTail /* OUT: Pointer to unused portion of zSql which
I'm not going to use because I want to bind things different ways.*/ );
if (status != SQLITE_OK){
printf("something is wrong, shame, shame, shame. ");
sqlite3_close(db);
return ;
}
sqlite3_step(ppStmt);
sqlite3_finalize(ppStmt);
int rings[5] ={3 , 7 ,9,1 ,1};
CString verse1 =_T(" for elvin kings, under the sky");
CString verse2 =_T(" for dwarf lords, in their halls of stone");
CString verse3 =_T(" for mortal men, doomed to die");
CString verse4 =_T(" for the dark lord, on his dark throne ");
csql.Format(" insert into poem (rings, location) values (?, ?) ");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded
*/ csql.GetLength(), /* Maximum length of zSql in bytes.
*/ &ppStmt, /* OUT: Statement handle */
&pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf("something is wrong, like %d",status);
sqlite3_close(db);
return ;
}
int ring_verse =0;
sqlite3_bind_int (ppStmt, 1, rings[ring_verse]);
sqlite3_bind_text(ppStmt, 2, verse1, verse1.GetLength(), SQLITE_STATIC);
sqlite3_step(ppStmt);
ring_verse++;
csql.Format(" insert into poem (rings, location) values (?002, ?001)
");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded
*/ csql.GetLength(), /* Maximum length of zSql in bytes. */
&ppStmt, /* OUT: Statement handle */
&pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf("something is wrong %d",status);
sqlite3_close(db);
return ;
}
sqlite3_bind_text(ppStmt, 1, verse2, verse2.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, 2, rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;
csql.Format(" insert into poem (rings, location) values ($ringy, :versy)
");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded */
csql.GetLength(), /* Maximum length of zSql in bytes. */
&ppStmt, /* OUT: Statement handle */
&pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf("something is wrong %d",status);
sqlite3_close(db);
return ;
}
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);
ring_verse++;
CString csrepeated =_T("\nin the land of Mordor, where the shadows
lie.");
csql.Format(" insert into poem (rings, location) values (@ringy, :versy
|| :repeats) ");
status = sqlite3_prepare_v2(db,csql, /* SQL statement, UTF-16 encoded */
csql.GetLength(), /* Maximum length of zSql in bytes. */
&ppStmt, /* OUT: Statement handle */
&pzTail /* OUT: Pointer to unused portion of zSql */ );
if (status != SQLITE_OK){
printf("something is wrong %d",status);
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"), verse4, verse4.GetLength(), SQLITE_STATIC);
sqlite3_bind_int (ppStmt, sqlite3_bind_parameter_index(ppStmt,
"@ringy"), rings[ring_verse]);
sqlite3_step(ppStmt);
ring_verse++;
//and finally
verse3.Format(" to bring them all and in the darkness BIND them ");
csql.Format(" insert into poem (rings, location) values (@ringy, ' ring
to rule them all '|| @ringy ||' ring to find them, '|| @ringy ||:versy ||
:repeats) ");
status = sqlite3_prepare_v2(db,csql, csql.GetLength(), &ppStmt,
&pzTail );
if (status != SQLITE_OK){
printf("something is wrong %d",status);
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:
> >>>>
> >>>> <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
> >>
> > _______________________________________________
> > 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
>
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
