You might know the benefits of using parameterized query (e.g boost query performance in term of speed, avoid SQL injection attack, etc) but how it can be done in programming way might sound like a different story to you. 🙂 In this tutorial, I’m going to show you how we can achieve that in using of C++ and SQLite. I am using VS C++ 2010 in this tutorial. Let’s start the tutorial now.

1. Create a Win32 Console Application.

2. Include SQLite header in the project.

#include "stdafx.h"
#include <sqlite3.h>

3. Create a function to insert record into the table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Insert record
void RunInsertParamSQL(sqlite3 *db, char *fn, char *ln, int age)
{
  if (!db)
    return;

  char *zErrMsg = 0;
  sqlite3_stmt *stmt;
  const char *pzTest;
  char *szSQL;

  // Insert data item into myTable
  szSQL = "insert into myTable (FirstName, LastName, Age)
            values (?,?,?)";

  int rc = sqlite3_prepare(db, szSQL, strlen(szSQL), &stmt, &pzTest);

  if( rc == SQLITE_OK ) {
    // bind the value
    sqlite3_bind_text(stmt, 1, fn, strlen(fn), 0);
    sqlite3_bind_text(stmt, 2, ln, strlen(ln), 0);
    sqlite3_bind_int(stmt, 3, age);

    // commit
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
  }
}

4. Create a function to update Age column in the table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// Update record
void RunUpdateParamSQL(sqlite3 *db, int age, char *fn)
{
  if (!db)
    return;

  char *zErrMsg = 0;
  sqlite3_stmt *stmt;
  const char *pzTest;
  char *szSQL;

  // Insert data item into myTable
  szSQL = "update myTable set Age = ? where FirstName = ?";

  int rc = sqlite3_prepare(db, szSQL, strlen(szSQL), &stmt, &pzTest);

  if( rc == SQLITE_OK ) {
    // bind the value
    sqlite3_bind_int(stmt, 1, age);
    sqlite3_bind_text(stmt, 2, fn, strlen(fn), 0);

    // commit
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
  }
}

5. The Main function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
int _tmain(int argc, _TCHAR* argv[])
{
  sqlite3 *db; // sqlite3 db struct
  char *zErrMsg = 0;
  char *szSQL;
  int rc;

  // Open the test.db file
  rc = sqlite3_open(argv[1], &db);

  if( rc )
  {
    // failed
    fprintf(stderr, "Can't open database: %s\n",
              sqlite3_errmsg(db));
  }
  else
  {
    // success
    fprintf(stderr, "Open database successfully\n");
  }

  // create myTable
  szSQL = "create table myTable (FirstName varchar(30),
        LastName varchar(30), Age smallint not null)";

  rc = sqlite3_exec(db, szSQL, callback, 0, &zErrMsg);

  if( rc == SQLITE_OK )
  {
    // insert 1 record into myTable
    RunInsertParamSQL(db, "askyb", "com", 10);

    // update age to 28 if FirstName equal to "askyb"
    RunUpdateParamSQL(db, 28, "askyb");

    // fetch records
    szSQL = "select * from myTable";
    rc = sqlite3_exec(db, szSQL, callback, 0, &zErrMsg);
  }

  // Close test.db file
  sqlite3_close(db);

  getchar();

  return 0;
}

6. Compile and run you code in command windows now and you should see the following screenshot.
C++ SQLite Example with Parameterized Query

Download source code: Sqlite3Param.zip