Working with Databases in C++: An Introduction

Using the SOCI library and MySQL database server

Image for post
Image for post

Introduction

What is SOCI?

Prerequisites

Installing Library Dependencies

Installing Boost

Image for post
Image for post
Listing the Boost header files in /usr/local/include/boost.
Image for post
Image for post
Listing the Boost libraries in /usr/local/lib.

Installing SOCI

cd ~
git clone git://github.com/SOCI/soci.git
cd soci        # Enter the soci repository
mkdir build # Create a new build directory
cd build # Enter the build directory
# Generate native build files
cmake -G "Unix Makefiles" ..
# Generate SOCI libraries and headers
make
# Copy libraries and headers to filesystem
make install
Image for post
Image for post
Listing the SOCI header files in /usr/local/include/soci.
Image for post
Image for post
Listing the SOCI libraries in /usr/local/lib.

Preparing A MySQL Database

Our entire SQL script to setup a MySQL database.
source /path/to/script.sql

Creating a New Database and Table

mysql -u root -p
Enter password: <your root password>
CREATE DATABASE IF NOT EXISTS soci_db;
USE soci_db;
CREATE TABLE users (
id INT AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
active BOOL DEFAULT TRUE,
PRIMARY KEY(id)
);
DESC users;
Image for post
Image for post
Describing the users table with the DESC statement.

Creating a Role

CREATE ROLE role_soci_dev;
GRANT ALL ON soci_db.* TO role_soci_dev;
SHOW GRANTS FOR role_soci_dev;

Creating a User

CREATE USER soci_dev1@localhost IDENTIFIED BY ‘Secure123’;
GRANT role_soci_dev TO soci_dev1@localhost;
SHOW GRANTS FOR soci_dev1@localhost USING role_soci_dev;
Image for post
Image for post
Showing privileges granted to the soci_dev1@localhost user.
SET DEFAULT ROLE ALL TO soci_dev1@localhost;

Verifying the Database Environment

/* exit mysql as the root user */
exit
# back in the terminal, log in to MySQL as the soci_dev1 user
mysql -u soci_dev1 -p
Enter password: <Secure123>
SELECT user();
SHOW GRANTS;
SHOW DATABASES;
USE soci_db;
SHOW TABLES;

Developing a C++ Application Using SOCI

Our entire C++ application code.

Header Files and Namespaces

#include <soci/soci.h>
#include <soci/mysql/soci-mysql.h>
#include <exception>
#include <iostream>
#include <string>
using namespace soci;using std::cin;
using std::cout;
using std::endl;
using std::string;

Exception Handling

int main() {    try {
// ...
}
catch(const std::exception& e) {
std::cerr << "Error: " << e.what() << endl;
}
}

Connecting to the MySQL Database

// Connect to MySQL database
session sql(mysql, "db=soci_db user=soci_dev1 password=Secure123");

Getting Data

// Get data for new user 
string first_name, last_name, email;
get_data("> Enter first name: ", first_name);
get_data("> Enter last name: ", last_name);
get_data("> Enter email address: ", email);
template<typename T>
void get_data(const string prompt, T& value) {
cout << prompt;
cin >> value;
}

Inserting Data

// Insert a new row into users table
insert_user(sql, first_name, last_name, email);
void insert_user(session& sql,
const string& first_name,
const string& last_name,
const string& email) {
// Insert data into users table
sql << "INSERT INTO users(first_name, last_name, email)"
"VALUES(:fn, :ln, :e)",
use(first_name, "fn"),
use(last_name, "ln"),
use(email, "e");
cout << "> Successfully inserted user." << endl << endl;
}
// Insert data into users table
sql << "INSERT INTO users(first_name, last_name, email)"
"VALUES(:fn, :ln, :e)",
use(first_name, "fn"),
use(last_name, "ln"),
use(email, "e");
cout << "> Successfully inserted user." << endl << endl;

Retrieving Data

// Retrieve all rows from users table and output data
display_users(sql);
void display_users(session& sql) {     // Retrieve all rows from users table 
rowset<row> rs = (sql.prepare << "SELECT * FROM users");
// Iterate through the result set
for (rowset<row>::const_iterator it = rs.begin();
it != rs.end(); ++it) {
const row& r = *it;
std::cout << "ID: " << r.get<int>(0) << endl
<< "First Name: " << r.get<string>(1) << endl
<< "Last Name: " << r.get<string>(2) << endl
<< "Email: " << r.get<string>(3) << endl
<< "Active: " << r.get<int>(4) << endl << endl;
}
}
rowset<row> rs = (sql.prepare << "SELECT * FROM users");
// Iterate through the result set 
for (rowset<row>::const_iterator it = rs.begin();
it != rs.end(); ++it) {
// ...
}
const row& r = *it;
std::cout << "ID: " << r.get<int>(0) << endl
<< "First Name: " << r.get<string>(1) << endl
<< "Last Name: " << r.get<string>(2) << endl
<< "Email: " << r.get<string>(3) << endl
<< "Active: " << r.get<int>(4) << endl << endl;
row.get<column_data_type>(column_index)
row.get<int>(0)    // Returns user's id as an int
row.get<string>(1) // Returns user’s first name as a string

Compiling and Running Our Application

UNIX Environments (Mac OS X, Linux, UNIX)

which g++
export CPATH="/usr/local/include:/usr/local/mysql/include"
export LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib"
export LD_LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib"
g++ -std=c++11 hello.cpp -o hello -lsoci_core -lsoci_mysql -ldl 
-lmysqlclient
./hello
Image for post
Image for post
Running our final executable.

In Summary

MSc. Programmer and fan of open source software.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store