C++ with SQLite3

• Part 1: Downloading and Compiling
• Part 2: Creating Tables
• Part 3: Inserting and Selecting Data
• Part 4: Removing Data
• Part 5: Encapsulating Database Objects 

SQLite3 is the most-used SQL implementation in the world. It is a self-contained C library included in everything from portable devices to web browsers.

Note: This tutorial assumes some familiarity with using the command-line to create and compile files. It was written using a MacOS X machine and also assumes easy access to g++. (For Windows systems, consider using MinGW.)

---

Encapsulating Database Objects

In all the previous parts, SQLite3 code was simply listed as part of the other code. It was included as multiple uses of the sqlite3_exec() function. This is not very efficient.

In larger projects, all of the database functionality can be encapsulated, put into a single section or file, to help with managing it.

Most of the SQLite3 usage, in fact, can be broken up into its own functions.

Building Strings

Dealing with values passed to functions means building strings for it. To avoid issues with converting back and forth from char to string, the functions simply use char pointers. To help with building a string, the function asprintf() is used to allocate and build a new string based on what is given to it.

Calling Functions

Once the SQLite3 functionality has been broken up, it can be accessed through different function calls.

	#include <iostream>
	#include <string>
	#include <sqlite3.h>
	using namespace std;
	class DBLite {
	private:
	// Pointer to SQLite connection
	sqlite3 *db;
	// Save any error messages
	char *zErrMsg;
	// Save the result of opening the file
	int rc;
	// Saved SQL
	char* sql;
	// Compiled SQLite Statement
	sqlite3_stmt *stmt;
	// Create a callback function
	static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
	// int argc: holds the number of results
	// (array) azColName: holds each column returned
	// (array) argv: holds each value
	for(int i = 0; i < argc; i++) {
	// Show column name, value, and newline
	cout << azColName[i] << ": " << argv[i] << endl;
	}
	// Insert a newline
	cout << endl;
	// Return successful
	return 0;
	}
	void checkDBErrors() {
	if( rc ){
	// Show an error message
	cout << "DB Error: " << sqlite3_errmsg(db) << endl;
	closeDB();
	}
	}
	public:
	DBLite() {
	// Save the result of opening the file
	rc = sqlite3_open("example.db", &db);
	checkDBErrors();
	}
	void createTable() {
	// Save SQL to create a table
	sql = "CREATE TABLE PEOPLE (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL);";
	// Run the SQL
	rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
	}
	void insertData(char* ID, char* name) {
	char *query = NULL;
	// Build a string using asprintf()
	asprintf(&query, "INSERT INTO PEOPLE ('ID', 'NAME') VALUES ('%s', '%s');", ID, name);
	// Prepare the query
	sqlite3_prepare(db, query, strlen(query), &stmt, NULL);
	// Test it
	rc = sqlite3_step(stmt);
	// Finialize the usage
	sqlite3_finalize(stmt);
	// Free up the query space
	free(query);
	}
	void showTable() {
	// Save SQL insert data
	sql = "SELECT * FROM 'PEOPLE';";
	// Run the SQL
	rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
	}
	void deleteRow(char* ID) {
	char *query = NULL;
	// Build a string using asprintf()
	asprintf(&query, "DELETE FROM 'PEOPLE' WHERE ID = '%s';", ID);
	// Prepare the query
	sqlite3_prepare(db, query, strlen(query), &stmt, NULL);
	// Test it
	rc = sqlite3_step(stmt);
	// Finialize the usage
	sqlite3_finalize(stmt);
	// Free up the query space
	free(query);
	}
	void closeDB() {
	// Close the SQL connection
	sqlite3_close(db);
	}
	};

view raw DBLite.h hosted with ❤ by GitHub

	#include "DBLite.h"
	int main() {
	DBLite sqldb;
	sqldb.createTable();
	sqldb.insertData("1", "Dan");
	sqldb.insertData("2", "Jeff");
	sqldb.insertData("3", "Cara");
	sqldb.showTable();
	sqldb.deleteRow("3");
	sqldb.showTable();
	sqldb.closeDB();
	return 0;
	}

view raw test.cpp hosted with ❤ by GitHub