Re: [sqlite] expression syntax

[Paul Dixon]

It worked out with pre-compiled statements (using sqlite3_bind_int).
But I still wonder if there is a way to set integer values (dynamic values) 
into DB with INSERT and UPDATE?
 

If you don't want to use prepared statements, I believe you can get the 
effect you are after with calls to sqlite3_mprintf and sqlite3_exec, for 
example:

//here's our variables
char * strFoo="isn't this nice?";
int nBar=123;
//use sqlite's built in string formatter - note that %q helpfully does 
escaping for us
char * strSQL=sqlite3_mprintf("insert into mytable(col1,col2) values 
(%d, '%q')", bBar, strFoo);

//execute our sql
sqlite3_exec(db, strSQL, NULL, NULL,NULL);
//and remember to free up the return value from sqlite3_mprintf
sqlite3_free(strSQL);
If you use this technique, you'll may want to roll your own function or 
class method with a signature like exec(const char* fmt, ) - easy to 
do, just use sqlite3_vmprintf instead to pick up the argument list.

Paul

Re: [sqlite] expression syntax

[ljucerk]

Thanks Ulrik,

It worked out with pre-compiled statements (using sqlite3_bind_int).
But I still wonder if there is a way to set integer values (dynamic values) 
into DB with INSERT and UPDATE?

NK


> Hi NK,
> 
> [EMAIL PROTECTED] wrote:
> 
> >Hi,
> >what is the correct syntax to use for WHERE expression:
> >
> >if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 
> integer, item3 integer, item4 integer, item5 integer);", NULL, 0, NULL))
> >  sqlite_error(test_db);
> >
> >// create index
> > if (sqlite3_exec(test_db, "CREATE INDEX item1idx ON ana_db (item1);", NULL, 
> > 0, 
> NULL))
> >  sqlite_error(test_db);
> >
> >  // insert values for 1000 records
> >  for (ii=0; ii< 1000; ii++)
> > {
> >   if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES 
> ('ii', 1, 0, 1, 100);", NULL, 0, NULL))
> > {
> >   sqlite_error(test_db);
> >   return (-1);
> > }
> > }
> >
> > // update values for 1000 records
> >  for (ii=0; ii< 1000; ii++)
> > {
> >   if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET 
> item2=item2+1 WHERE item1='ii';", NULL, 0, NULL))
> > {
> >   sqlite_error(test_db);
> >   return (-1);
> > }
> >}
> >
> >It looks like I'm not using the right syntax in UPDATE statement, I tried 
> >with: 
> WHERE item1 = $ii, w/out success. 
> >What am I doing wrong?
> >
> >Thanks,
> >
> >NK
> >  
> >
> 
> you want the sqlite3_bind_int API.  Look it up on the www.sqlite.org 
> website.
> 
> HTH
> 
> Ulrik
> 
> 
> 
> -- 
> Ulrik Petersen, MA, B.Sc.
> University of Aalborg, Denmark
> Homepage: http://ulrikp.org
> 
> 

Re: [sqlite] expression syntax

[Ulrik Petersen]

Hi NK,
[EMAIL PROTECTED] wrote:
Hi,
what is the correct syntax to use for WHERE expression:
   if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 integer, 
item3 integer, item4 integer, item5 integer);", NULL, 0, NULL))
 sqlite_error(test_db);
// create index
if (sqlite3_exec(test_db, "CREATE INDEX item1idx ON ana_db (item1);", NULL, 0, 
NULL))
 sqlite_error(test_db);
 // insert values for 1000 records
 for (ii=0; ii< 1000; ii++)
{
  if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES ('ii', 
1, 0, 1, 100);", NULL, 0, NULL))
{
  sqlite_error(test_db);
  return (-1);
}
}
// update values for 1000 records
 for (ii=0; ii< 1000; ii++)
{
  if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET item2=item2+1 
WHERE item1='ii';", NULL, 0, NULL))
{
  sqlite_error(test_db);
  return (-1);
}
   }
It looks like I'm not using the right syntax in UPDATE statement, I tried with: WHERE item1 = $ii, w/out success. 
What am I doing wrong?

Thanks,
NK
 

you want the sqlite3_bind_int API.  Look it up on the www.sqlite.org 
website.

HTH
Ulrik

--
Ulrik Petersen, MA, B.Sc.
University of Aalborg, Denmark
Homepage: http://ulrikp.org

[sqlite] expression syntax

[ljucerk]

Hi,
what is the correct syntax to use for WHERE expression:

if (sqlite3_exec(test_db, "CREATE TABLE ana_db (item1 integer, item2 
integer, item3 integer, item4 integer, item5 integer);", NULL, 0, NULL))
  sqlite_error(test_db);

// create index
 if (sqlite3_exec(test_db, "CREATE INDEX item1idx ON ana_db (item1);", NULL, 0, 
NULL))
  sqlite_error(test_db);

  // insert values for 1000 records
  for (ii=0; ii< 1000; ii++)
{
  if (error_code = sqlite3_exec(test_db, "INSERT INTO ana_db VALUES 
('ii', 1, 0, 1, 100);", NULL, 0, NULL))
{
  sqlite_error(test_db);
  return (-1);
}
}

 // update values for 1000 records
  for (ii=0; ii< 1000; ii++)
{
  if (error_code = sqlite3_exec(test_db, "UPDATE ana_db SET 
item2=item2+1 WHERE item1='ii';", NULL, 0, NULL))
{
  sqlite_error(test_db);
  return (-1);
}
}

It looks like I'm not using the right syntax in UPDATE statement, I tried with: 
WHERE item1 = $ii, w/out success. 
What am I doing wrong?

Thanks,

NK