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 <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.
Download source code: Sqlite3Param.zip
How to compare Blob datatype in c++ from sqlite?