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

---

Inserting

In the previous part, the function sqlite3_exec() was used create a new table using the CREATE TABLE keywords in SQL. To add data to the table, sqlite3_exec() can be used again.

To insert more data, multiple SQL statements can be run.

When working with SQL, the sqlite3_exec() will often be used. However, as with using CREATE TABLE, the use of INSERT INTO does not return data to the callback function. For that, a SELECT keyword in SQL would need to be used.

Selecting

The SELECT keyword in SQL can be used to get data from a table. Because it returns data, the callback function used with sqlite3_exec() function.

The callback function takes four parameters:

• void *NotUsed: Not used. This can be passed from the sqlite3_exec() function directly.
• int argc: Number of results
• char **argv: Array of values
• char **azColName: Array of column names

	#include <iostream>
	#include <string>
	#include <sqlite3.h>
	using namespace std;
	// Create a callback function
	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;
	}
	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);
	// Save SQL insert data
	sql = "INSERT INTO PEOPLE ('ID', 'NAME') VALUES ('3','Jeff');";
	// Run the SQL (convert the string to a C-String with c_str() )
	rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
	// Save SQL insert data
	sql = "INSERT INTO PEOPLE ('ID', 'NAME') VALUES ('2','Dan');";
	// Run the SQL (convert the string to a C-String with c_str() )
	rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
	// Save SQL insert data
	sql = "INSERT INTO PEOPLE ('ID', 'NAME') VALUES ('1','Cara');";
	// Run the SQL (convert the string to a C-String with c_str() )
	rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
	// Save SQL insert data
	sql = "SELECT * FROM 'PEOPLE';";
	// 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