SQLite operations in C++
This tutorial aimed to demonstrate how to use SQLite database operations such as CREATE, INSERT, UPDATE, SELECT and DELETE in C++. Since the appearance first smartphone and other mobile devices SQLite become most widely deployed and used database engine in the world. Precise numbers are difficult to obtain but SQLite is likely used more than all other database engines combined. We can find SQLite in every Android device, iPhone and iOS device, Mac, Windows10, Firefox, Chrome, Safari, Skype, Dropbox, TurboTax, QuickBooks, PHP, Python, television sets, set-top cable boxes and many automotive multimedia systems.
It’s an open-source SQL database engine. You can download the source code or precompiled binaries depending your requirements. You might also need to add [-lsqlite3] and [-std=c++17] to successfully compile, create an executable file. Each of the following source code available on my git repository.
github.com/lnrsoft/sqlite3_cpp_basics_updated or lnrrepo.ddns.net/git/lnrsoft/sqlite3_cpp_basics_updated
Use this link
local_db.sqlite if you would like to download the local_db.sqlite file that I used in this tutorial.
Step 1 – The first thing we need to do is to create the actual database file that we will use in this article. I called sqltdemo.db in our case.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#include #include #include int main() { sqlite3* db; auto op = sqlite3_open("local_db.sqlite", &db); auto cl = sqlite3_close(db); if(op) { std::cerr << "SQLite ERROR: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cout << "SQLite local db has been opened successfully." << std::endl; } if(cl) { std::cerr << "SQLite ERROR: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cout << "SQLite local db has been closed successfully." << std::endl; } } |
Compile and run our source code to create the local_db.sqlite with the following command. Here we link our code with the sqlite3 library that will create our db file.
|
|
$ clang++ 01_openDB.cpp -Wall -Wextra -lsqlite3 -std=c++17 $ ./a.out SQLite local db has been opened successfully. SQLite local db has been closed successfully. |
Verify our result:
|
|
$ ls -la -rw-r--r-- 1 rolandihasz staff 605 Mar 1 21:05 01_openDB.cpp -rw-r--r-- 1 rolandihasz staff 21606 Mar 1 21:06 local_db.sqlite -rwxr-xr-x 1 rolandihasz staff 8660 Mar 1 21:06 a.out |
Step 2 – CREATE 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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
#include #include #include static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i = 0; i < argc; i++) { std::cerr << azColName[i] << argv[i] << argv[i] << "NULL"; } std::cout << std::endl; return 0; } int main() { sqlite3 *db; char *zErrMsg = nullptr; const char *sql; auto rc = sqlite3_open("local_db.sqlite", &db); if(rc) { std::cerr << "Failed open database: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cerr << "Database successfully opened" << std::endl; } sql = "CREATE TABLE CANDIDATE(" "ID INT PRIMARY KEY NOT NULL," "NAME TEXT NOT NULL," "AGE INT NOT NULL," "ROLE TEXT NOT NULL," "SALARY TEXT NOT NULL);"; rc = sqlite3_exec(db, sql, callback, nullptr, &zErrMsg); if(rc != SQLITE_OK) { std::cerr << "SQL error: " << zErrMsg << std::endl; sqlite3_free(&zErrMsg); } else { std::cerr << "Table successfully created" << std::endl; } sqlite3_close(db); return 0; } |
Compile and run.
|
|
$ clang++ 02_sqlite3_create_table.cpp -Wall -Wextra -lsqlite3 -std=c++17 $ ./a.out Database successfully opened Table successfully created |
Step 3 – INSERT INTO our CANDIDATE 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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
#include #include #include static int callback(void *NotUsed, int argc, char **argv, char **azColName) { int i; for(i = 0; i < argc; i++) { std::cerr << azColName[i] << argv[i] << argv[i] << "NULL"; } std::cout << std::endl; return 0; } int main() { sqlite3 *db; char *zErrMsg = nullptr; int rc; const char *sql; rc = sqlite3_open("local_db.sqlite", &db); if(rc) { std::cerr << "Failed open database: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cerr << "Database successfully opened" << std::endl; } sql = "INSERT INTO CANDIDATE (ID,NAME,AGE,ROLE,SALARY) " "VALUES (1, 'Daniel', 29, 'CCO', 130000 ); " "INSERT INTO CANDIDATE (ID,NAME,AGE,ROLE,SALARY) " "VALUES (2, 'Roland', 35, 'CTO', 175000 ); " "INSERT INTO CANDIDATE (ID,NAME,AGE,ROLE,SALARY)" "VALUES (3, 'Leslie', 33, 'CEO', 200000 );"; rc = sqlite3_exec(db, sql, callback, nullptr, &zErrMsg); if(rc != SQLITE_OK) { std::cerr << "SQL error: " << zErrMsg << std::endl; sqlite3_free(zErrMsg); } else { std::cerr << "Records created successfully" << std::endl; } sqlite3_close(db); return 0; } |
Compile and run.
|
|
$ clang++ 03_sqlite3_insert.cpp -Wall -Wextra -lsqlite3 -std=c++17 $ ./a.out Database successfully opened Records created successfully |
Step 4 – Fetch and display records from the company CANDIDATE TABLE. In this scenario we use a callback function that obtains results from SELECT statement.
|
|
//callback function declaration typedef int (*sqlite3_callback)( void*, /* Data provided in the 4th argument of sqlite3_exec() */ int, /* The number of columns in row */ char**, /* An array of strings representing fields in the row */ char** /* An array of strings representing column names */ ); |
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 |
#include #include #include #include #include static int callback(void *data, int argc, char **argv, char **azColName) { fprintf(stderr, "\nCallback function call: ", &data); for(auto i = 0; i < argc; i++) { printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } return 0; } int main() { sqlite3 *db; char *zErrMsg = nullptr; int rc; const char *sql; const char *data = "Callback function call"; rc = sqlite3_open("local_db.sqlite", &db); if(rc) { std::cerr << "Failed open database: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cerr << "Database successfully opened" << std::endl; } sql = "SELECT * from CANDIDATE"; rc = sqlite3_exec(db, sql, callback, &data, &zErrMsg); if(rc != SQLITE_OK) { std::cerr << "SQL error: " << zErrMsg << std::endl; sqlite3_free(zErrMsg); } else { std::cout << "\nExecution completed successfully" << std::endl; } sqlite3_close(db); return 0; } |
Compile and run.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
$ clang++ 04_sqlite3_select_operation.cpp -Wall -Wextra -lsqlite3 -std=c++17 $ ./a.out Database successfully opened Callback function call: ID = 1 NAME = Daniel AGE = 29 ROLE = CCO SALARY = 130000 Callback function call: ID = 2 NAME = Roland AGE = 35 ROLE = CTO SALARY = 175000 Callback function call: ID = 3 NAME = Leslie AGE = 33 ROLE = CEO SALARY = 200000 Execution completed successfully |
Step 5 – Using UPDATE statement to update any records in our CANDIDATE 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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
#include #include #include #include #include static int callback(void *data, int argc, char **argv, char **azColName) { fprintf(stderr, "\nCallback function call: ", &data); for(auto i = 0; i < argc; i++) { printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } return 0; } int main() { sqlite3 *db; char *zErrMsg = nullptr; int rc; const char *sql; const char *data = "Callback function call"; rc = sqlite3_open("local_db.sqlite", &db); if(rc) { std::cerr << "Failed open database: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cout << "Database successfully opened" << std::endl; } sql = "UPDATE CANDIDATE set SALARY = 150000 where ID=1; " "SELECT * from CANDIDATE"; rc = sqlite3_exec(db, sql, callback, &data, &zErrMsg); if(rc != SQLITE_OK) { std::cerr << "SQL error: " << zErrMsg << std::endl; sqlite3_free(zErrMsg); } else { std::cout << "Execution completed successfully" << std::endl; } sqlite3_close(db); return 0; } |
Compile and run.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$ clang++ 05_sqlite3_update_operation.cpp -Wall -Wextra -lsqlite3 -std=c++17 $ ./a.out Database successfully opened Callback function call: ID = 1 NAME = Daniel AGE = 29 ROLE = CCO SALARY = 150000 Callback function call: ID = 2 NAME = Roland AGE = 35 ROLE = CTO SALARY = 175000 Callback function call: ID = 3 NAME = Leslie AGE = 33 ROLE = CEO SALARY = 200000 Execution completed successfully |
Step 6 – DELETE statement can delete any records from the CANDIDATE 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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
#include #include #include #include #include static int callback(void *data, int argc, char **argv, char **azColName) { fprintf(stderr, "\nCallback function call: ", &data); for(auto i = 0; i < argc; i++) { printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); } return 0; } int main() { sqlite3 *db; char *zErrMsg = nullptr; int rc; const char *sql; const char *data = "Callback function call"; rc = sqlite3_open("local_db.sqlite", &db); if(rc) { std::cerr << "Failed open database: " << sqlite3_errmsg(db) << std::endl; return (0); } else { std::cerr << "Database successfully opened" << std::endl; } sql = "DELETE from CANDIDATE where ID=2; " "SELECT * from CANDIDATE"; rc = sqlite3_exec(db, sql, callback, &data, &zErrMsg); if(rc != SQLITE_OK) { std::cerr << "SQL error: " << zErrMsg << std::endl; sqlite3_free(zErrMsg); } else { std::cout << "\nExecution completed successfully" << std::endl; } sqlite3_close(db); return 0; } |
Compile and run.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$ clang++ 06_sqlite3_delete_operation.cpp -Wall -Wextra -lsqlite3 -std=c++17 $ ./a.out Database successfully opened Callback function call: ID = 1 NAME = Daniel AGE = 29 ROLE = CCO SALARY = 150000 Callback function call: ID = 3 NAME = Leslie AGE = 33 ROLE = CEO SALARY = 200000 Execution completed successfully |
I recommend you to check the official SQLite documentation at sqlite.org for more advance SQLite features and options and support. For further info about using sql with c++ you might found it useful to read me previous post of a simple Qt tool to test QSqlDatabase access connection to a MySQL database. I created this simple Qt command line tool to test connection between Qt client applications and a remote Mysql Server. The code includes Secure Sockets Layer (SSL) support to test secure connection. The QSqlDatabase class provides an interface for accessing a database through a connection where an instance of QSqlDatabase represents the connection. The connection provides access to the database via one of the supported database drivers, which are derived from QSqlDriver.
Alternatively, we can subclass your own database driver from QSqlDriver. My next post will be an article how to write your own database driver in Qt for Unix, OSX and Windows platforms.