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.)

---

Creating Tables

Working with SQLite3 includes three main functions: sqlite3_open(), sqlite3_exec(), and sqlite3_close().

In the previous part, the functions sqlite3_open() and sqlite3_close() were used to open and close the connection.

To execute SQL, the function sqlite3_exec() is used.

sqlite3_exec()

Calls to sqlite3_exec() use five parameters:

• Database connection (SQLite3 pointer)
• SQL to run
• Callback function
• First argument to callback function
• Address of where to write error messages

Because SQL commands like SELECT can return multiple results, the callback function is used to act on them. The fourth parameter is optional when working with functions that may not need it.

CREATE TABLE

To create a table within the existing database, the CREATE TABLE keywords are used in SQL.

CREATE TABLE PEOPLE (
      "ID INT PRIMARY KEY     NOT NULL,
      "NAME           TEXT    NOT NULL);

To work with SQL in C++, it will need to be supplied to sqlite3_exec().

	#include <iostream>
	#include <string>
	#include <sqlite3.h>
	using namespace std;
	// Create a callback function
	int callback(void *NotUsed, int argc, char **argv, char **azColName){
	// Return successful
	return 0;
	}
	int main() {
	// Pointer to SQLite connection
	sqlite3 *db;
	// Save any error messages
	char *zErrMsg = 0;
	// Save the result of opening the file
	int rc;
	// Save any SQL
	string sql;
	// Save the result of opening the file
	rc = sqlite3_open("example.db", &db);
	if( rc ){
	// Show an error message
	cout << "DB Error: " << sqlite3_errmsg(db) << endl;
	// Close the connection
	sqlite3_close(db);
	// Return an error
	return(1);
	}
	// Save SQL to create a table
	sql = "CREATE TABLE PEOPLE (" \
	"ID INT PRIMARY KEY NOT NULL," \
	"NAME TEXT NOT NULL);";
	// Run the SQL (convert the string to a C-String with c_str() )
	rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
	// Close the SQL connection
	sqlite3_close(db);
	return 0;
	}

view raw test.cpp hosted with ❤ by GitHub

Like with the previous part, compile and run the example.

If everything works correctly, the “example.db” file will now hold a table called “People” and be prepared for data.