Working with Databases in C++: An Introduction

Using the SOCI library and MySQL database server

Introduction

This article will serve as an introduction to working with databases in C++. After reading this article, you will have accumulated enough knowledge to start writing C++ applications that interact with your favourite database backends.

The full pipeline of developing an application will be explored — from installing necessary libraries to compiling our final application. More specifically, this article will detail how to:

• Download and Install the SOCI library.
  This section will describe how to download SOCI and install its libraries and header files on your system.
• Set up a database environment on a MySQL server.
  In order to safely access the MySQL server from our C++ application, we will create a user and assign that user a role. This role will describe what operations the user can perform and which databases are accessible on the server.
• Write a simple C++ SOCI application.
  This section will detail how to use the SOCI API to insert and retrieve data from the database.
• Compile and run our C++ application.
  This section will demonstrate how to compile and run our C++ application from the command line using the GCC compiler.

What is SOCI?

SOCI stands for Simple Open (Database) Call Interface and is a database access library written in C++. Several database backends have been developed for SOCI, meaning that you can use one simple API provided by SOCI to work with a range of databases.

For example, it’s entirely possible to write a single C++ application with SOCI that interacts with several database backends, such as MySQL, PostgreSQL and and SQLite 3. Without SOCI, you would need to learn and implement the individual C++ APIs provided by each particular backend your application requires, which would be time-consuming and tedious.

SOCI supports popular open source database backends including MySQL, PostgreSQL, SQLite 3 and Firebird, as well as database servers used commercially such as Oracle.

Prerequisites

All software adopted in this article is open-source and freely available to download and install on your system. To complete the steps demonstrated in this article, make sure you have the following software already installed on your system:

• C++ Compiler
  Such as GCC, LLVM/Clang or Microsoft Visual C++.
• CMake 2.8+
  Used for building the SOCI API and libraries. Download CMake or install the cmake package using your OS’s native package manager.
• MySQL Server and MySQL Shared Libraries
  MySQL can be installed via your operating system’s package manager (Linux) or via the MySQL Community Server download page. Shared libraries are also included in a standard MySQL installation. MySQL Version 8.0.18 is used throughout this article.

Installing Library Dependencies

This section will detail the installation of SOCI on the Mac OS X and Linux operating systems (Unix style systems). Instructions on how to install the Boost libraries will also be provided as SOCI uses the Boost.Date_Time library.

For readers who are using Windows, please refer to the boost download page where you can download and install Boost via prebuilt Windows binaries. Also refer to the SOCI Installation page for specific instructions on running CMake on Windows and installing SOCI to your system.

Installing Boost

Luckily, the Boost libraries can be installed via a single command using your operating system’s native package manager. The following commands will install the Boost libraries and corresponding header files on your system. In addition to Mac OS X, installation commands are provided for major Linux distributions — so please choose the appropriate installation command for your operating system:

• Mac OS X: sudo brew install boost
• Arch: sudo pacman -S boost
• Debian and Ubuntu: sudo apt-get install libboost-all-dev
• CentOS and Fedora: sudo dnf install boost-devel

The default install location for the Boost libraries is the /usr/local/lib directory. The header files are installed in the /usr/local/include/boost directory. You can confirm the install locations by using the ls command in the terminal:

Listing the Boost header files in /usr/local/include/boost.

Listing the Boost libraries in /usr/local/lib.

Installing SOCI

The process to install SOCI is also quite straightforward, but involves a few more steps. We are going to download the SOCI Git repository, generate a native build environment using CMake, produce the SOCI libraries and header files, and finally install (copy) them to appropriate locations on our system.

That might sound like a lot of steps to break down and understand, so let’s start by executing the following two commands in a terminal. The cd command firstly navigates you to your home directory. The git clone command then downloads the SOCI Git repository to this directory:

cd ~
git clone git://github.com/SOCI/soci.git

This repository contains everything we need to install the SOCI libraries and header files — it contains CMake build files, documentation, tests, and the actual C++ source code. As of this writing, the latest stable version of SOCI will be downloaded (SOCI version 4.0).

Moving forward, the next task is to generate a native build environment using CMake. A native build environment contains scripts that a particular operating system uses in order to compile source code, create libraries and build executables. On a Unix-style operating system (such as Mac OS X and Linux), a Makefile will be produced by CMake. We then invoke the Unix make program to read the Makefile, which in turn will compile and generate SOCI’s headers and shared libraries.

CMake can be described as a simple interpreter that reads a script and subsequently generates build files for a particular platform in a compiler-independent way.

Next, we create a directory named build in the soci repository that will contain the native build environment produced by CMake:

cd soci        # Enter the soci repository
mkdir build    # Create a new build directory
cd build       # Enter the build directory

It is perfectly fine to name the build directory anything you like, but we will stick to convention in this case. Moving forward, we execute three more commands that will actually install SOCI to our system:

# Generate native build files
cmake -G "Unix Makefiles" ..
# Generate SOCI libraries and headers
make
# Copy libraries and headers to filesystem
make install

Let’s take a look at these different commands and examine how they work:

• cmake -G "Unix Makefiles" ..
The cmake command is invoked to generate our native build environment. Two important arguments are passed here. The -G option specifies a build system generator. In this case, the "Unix Makefiles" generator is selected in order for CMake to produce a Makefile. Lastly, .. points to the root directory of the soci repository which also includes the CMakeLists.txt file — cmake requires this file to generate the build files.
• make
  A program that will read the Makefile in the build directory and generate the shared libraries, source files, and binaries that make up the SOCI library.
• make install
This command will copy the generated shared libraries and include files to appropriate locations on your filesystem.

SOCI Shared libraries will be copied to /usr/local/lib, while header files will be copied to /usr/local/include/soci. Near the end of this article, we will configure the C++ compiler to search through these directories in order to resolve our application’s header and library dependencies.

Let’s confirm the install locations by using the ls command in the terminal:

Listing the SOCI header files in /usr/local/include/soci.

Listing the SOCI libraries in /usr/local/lib.

At this point, SOCI should be installed on your system and ready to use! The next section will detail how we go about setting up a database environment that our application will interact with during runtime.

Preparing A MySQL Database

In this section, we will walk through setting up our database environment. More specifically, we will be working with a MySQL database server that will serve as the database backend to our C++ application.

Before starting on the C++ application, we need to complete a few tasks on the database server. For this demonstration, we will:

• Create a new database called soci_db.
• Create a table called users that will enable us to insert and retrieve some data from our C++ application.
• Create a role called role_soci_dev and assign it privileges appropriate for developers working on our application.
• Create a user called soci_dev1@localhost that is able to connect to MySQL from the C++ application and work with the soci_db database.
• Grant the role_soci_dev role to the soci_dev1 user, and set it as the default role.

The following gist is a MySQL script that sets up our database environment:

Our entire SQL script to setup a MySQL database.

If you are already familiar with the SQL statements used in the script and do not feel like further explanation is necessary, feel free to download the file and execute the script from your MySQL server.

Once logged into MySQL as the root user, run MySQL’s source command to execute the script:

source /path/to/script.sql

• Replace/path/to/ to the location that the downloaded script resides on your filesystem.

If you have executed the script and understand all the SQL commands, feel free to proceed to the next section in this article (Developing a SOCI C++ Application).

The following subsections will walk you through each statement contained in the script above, and provide additional explanation of what the statement does.

Creating a New Database and Table

We will firstly log in to our MySQL server as the root user. Let’s open up a terminal and run the following command:

mysql -u root -p
Enter password: <your root password>

• The -u flag requires a username that represents the user we are logging in as. Because we would like administrative privileges on our MySQL server, we shall log in as the root user.
• The -p flag is necessary if the user you are logging in as requires a password. Go ahead and enter your root password when prompted, followed by the Enter key.

Let’s proceed to create a new database and use it via the following two statements:

CREATE DATABASE IF NOT EXISTS soci_db;
USE soci_db;

• We create a database called soci_db with the CREATE DATABASE statement. The IF NOT EXISTS clause makes sure that the database name is unique on the server.
• We then execute the USE statement in order to enter the soci_db database container so we can proceed to create objects within it.

For the purposes of our simple proof-of-concept application, we will create a table called users and store some rudimentary data — a first name, last name, email address, and a boolean representing if the user is active or not. The CREATE TABLE statement is invoked to create the users table:

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

Notice how we make an effort to adhere to robust database table design principles by adding a primary key and a few constraints to our table’s columns:

• The id column serves as the primary key, which means that all records in the users table can be uniquely identified with the id value. The id column of the first row inserted into the table will contain one. The AUTO_INCREMENT property will also increment id by one for each new record.
• The NOT NULL constraint is applied to the first_name, last_name and email columns. These columns must therefore always contain a value, and not contain a NULL value. The UNIQUE constraint is also applied to the email column, which ensures that all email address values stored in the table are different.
• Lastly, the DEFAULT constraint is applied to the active column. MySQL will automatically provide a TRUE value if nothing is provided for the active column when inserting a new user.

To verify that our table has been created successfully, lets describe it with the DESC statement:

DESC users;

It’s good practice to study the output of the DESC statement and confirm that the table has the correct column names, data types, and constraints set up as intend before moving forward.

Describing the users table with the DESC statement.

Creating a Role

Our next task is to create a role that permits operations relevant only to the soci_db database. This role will then be granted to a user on our database server.

Later on when we develop a simple C++ application, we will use the SOCI API to perform just two tasks on our database backend — inserting and retrieving users. With this in mind, the connecting user should not have access outside the soci_db database, or be able to perform tasks that a superuser would typically do.

To accomplish this, we will create a developer role that can be granted to developers working on the soci_db database. In other words, database users who have been granted this role will be able to execute many SQL statements within the soci_db database in order to develop the C++ application effectively.

Let’s go ahead and create a new role called role_soci_dev with the CREATE ROLE statement:

CREATE ROLE role_soci_dev;

Role names will be saved along with user names in the mysql.user system table. To easily query roles on our database server, we have prefixed the role name with role_.

Next, we will grant privileges to our newly created role. These privileges will dictate what statements a user will be able to perform on the database server.

In our case, we would like to give a developer a high degree of freedom inside the soci_db database — such as creating, deleting and updating database objects, as well as inserting data. Luckily, there is a privilege within MySQL called ALL that will satisfy this requirement.

Let’s run the GRANT statement to add the necessary privileges to the role_soc_dev role:

GRANT ALL ON soci_db.* TO role_soci_dev;

• The above statement can be read as: Give the role_soci_dev role ALL privileges for all tables when using the soci_db database.
• The asterisk (*) in soci_db.* represents all tables in the soci_db database. To grant these privileges to just a single table, replace the asterisk with a valid table name.

We can verify that the ALL privilege has been granted to our role by executing the SHOW GRANTS statement:

SHOW GRANTS FOR role_soci_dev;

The output to this statement should include:

• GRANT USAGE ON *.* TO `role_soci_dev`@`%`
The USAGE privilege will only permit users to be able to log in to the MySQL server. In other words, this privilege carries the meaning of “no privileges”, and is granted to every newly created user and role.
• GRANT ALL PRIVILEGES ON `soci_db`.* TO `role_soci_dev`@`%`
This output verifies that the ALL privilege on the soci_db database has been granted to our role successfully.

We are moving forward nicely! We have now created a role on our MySQL server that can be granted to any user that we create. In other words, if we hire five developers to work on our application, we can create five users on our MySQL server and give them the role we just created via a single statement.

Creating a User

Let’s go ahead and create a new user on the MySQL server called soci_dev1. This user will log in to the MySQL server from our C++ application. The CREATE USER statement creates a new user:

CREATE USER soci_dev1@localhost IDENTIFIED BY ‘Secure123’;

• The soci_dev1 user connects to the MySQL server from localhost. In other words, this user is only able to connect to MySQL from the localhost machine, and not remotely.
• The IDENTIFIED BY clause allows us to specify a password that will be required every time the user logs in to the MySQL server. Feel free to use a more secure password on your end!

If you now go ahead and log in to MySQL as the soci_dev1 user, you won’t be able to access any databases, or execute any statements for that matter. As mentioned previously, the USAGE privilege is the only privilege granted to the user at this point.

To enable the user to perform operations on the server, we need to execute just a few more statements. Lets start with using the GRANT statement to give the user the role_soci_dev role we created a moment ago:

GRANT role_soci_dev TO soci_dev1@localhost;

Note that we must specify both the username (soci_dev1) and hostname (localhost) when referencing our user.

Let’s confirm that the role is applied to the user by executing the SHOW GRANTS statement:

SHOW GRANTS FOR soci_dev1@localhost USING role_soci_dev;

Showing privileges granted to the soci_dev1@localhost user.

The USING clause allows us to view the privileges associated with the role_soci_dev role. If you were to execute this statement without the USING clause, the applied role name will only be displayed without its associated privileges — go ahead and give it a try.

Now, if we proceed to log in to MySQL with the soci_dev1 user, we still won’t be able to actually do anything! The role_soci_dev role will remain inactive until we tell MySQL what role to activate for the user after they have logged in. Therefore, we must set a default role that will be active when the user logs in.

To accomplish this, lets use the following SET DEFAULT ROLE statement:

SET DEFAULT ROLE ALL TO soci_dev1@localhost;

Now, every role that has been granted to our user will be active, by default, when they log in to MySQL.

Verifying the Database Environment

Let’s now confirm everything is working by logging out as the root user, and logging in as the soci_dev1 user:

/* 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>

Notice how we specify soci_dev1 after the -u flag to log in as our new user. Note that it is not necessary to append the the hostname (@localhost) to the user in this case. Also remember to input the password you gave the soci_dev1 user that was specified in the CREATE USER statement.

Before proceeding to write the C++ application, let’s confirm that we have set up our database environment correctly. More specifically, we would like to confirm that:

• The soci_dev1 user has the correct privileges.
• We can access and work with the soci_db database.
• We cannot access any other databases on the server.

Firstly, let’s confirm that we are logged in as the user we intend to be by running the following statement:

SELECT user();

soci_dev1@localhost should be returned after executing this statement.

You will sometimes see the current_user() and current_user (without parenthesis) functions used instead of user().

Secondly, let’s verify the privileges we have by running the SHOW GRANTS statement:

SHOW GRANTS;

Notice that we have used the short form of the SHOW GRANTS statement to display our own privileges.

Thirdly, we need to confirm that we only have access to the soci_db database:

SHOW DATABASES;

The only database name displayed in this list should be soci_db. As this user does not have the necessary privileges to access any other database, no other database names are displayed, including the MySQL system databases.

Lastly, lets enter the soci_db database and show the users table by running the following statements:

USE soci_db;
SHOW TABLES;

Feel free to run additional statements to verify other aspects of our database environment as the soci_dev1 user. It might be a good idea to examine the users table with the DESC statement, or to even start playing with data by using the INSERT, SELECT, UPDATE and DELETE statements.

With a database environment set up, let’s proceed to writing our C++ application with SOCI.

Developing a C++ Application Using SOCI

This section will walk through writing a simple C++ application using the SOCI library. The application will perform the following tasks:

• Connect to the soci_db database on our MySQL backend as the soci_dev1 user.
• Insert a new user into the users table. The application will display prompts allowing you to input values for the new user’s first name, last name, and email address.
• Retrieve and display all records from the users table. For every row that is retrieved from the database, the value inside each column will be output to the terminal.

The following gist contains the entire C++source code for this demonstration. Feel free to type out the code in your own hello.cpp source file while trying to understand how the application works.

Alternatively, you can download the source file and proceed to the Compiling and Running Our Application section to run the program. After running the program, you may wish to return to this section to type out the code yourself and study the explanations that follow:

Our entire C++ application code.

The following sub-sections will break down the application’s C++ code and provide an explanation on what is being performed.

Header Files and Namespaces

We start by including all the header files our application requires. In this case, our program needs to interface with the soci.h header file, as well as the header file relevant to the database backend we are adopting.

As our database backend is MySQL, the application must include the soci-mysql.h header file. If your solution has adopted an Oracle backend, you would be required to include the soci-oracle.h header file instead, and so on.

Additionally, we include three standard library header files in order to work with exceptions (exception), terminal input and output (iostream), and strings (string):

#include <soci/soci.h>
#include <soci/mysql/soci-mysql.h>
#include <exception>
#include <iostream>
#include <string>

It is considered good practice to firstly include external library header files, followed by header files local to your project, and finally standard library header files. Moreover, header files listed in alphabetical order adds another level of organisation to your includes section.

Moving forward, we bring the soci namespace and some C++ standard library items into scope:

using namespace soci;
using std::cin;
using std::cout;
using std::endl;
using std::string;

Everything in SOCI is declared in the soci namespace, so we expose the entire soci namespace to our application. From now on, any item we reference that is declared in the soci namespace will not require the soci:: prefix, which will improve the readability of our code.

Furthermore, we individually bring some standard library items into scope that will be used for handling data input and output to the terminal.

Exception Handling

The main function starts by defining a try ... catch block to handle errors appropriately:

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

It is important to implement some exception handling in our program as SOCI will throw an exception if an error occurs when it interacts with the backend database.

All database related errors will result in an exception being thrown — such as when the SOCI library is unable to make a successful connection to the backend database, is unable to retrieve data, and so on.

If an exception is thrown during runtime, it will propagate to the main function and be handled in the catch block. In our case, we call the exception::what() method to attempt to describe the error that occurred before the application terminates. The error message will be displayed in the terminal.

Connecting to the MySQL Database

Moving forward, our application immediately attempts to connect to our MySQL database backend. In order to do this, we create a soci::session object stored in a variable called sql, and pass it some information:

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

The soci:session object encapsulates the database connection and other backend related details. Therefore, we must pass it the following items:

• mysql: The MySQL backend factory object that enables interactions with a MySQL backend. This object is declared inside soci-mysql.h.
• "db=soci_db user=soci_dev1 password=Secure123": The generic connection string that specifies which database to use, and what user to log in as.

The generic connection string passed to the soci::session constructor includes:

• db=soci_db: It will connect to a database on our server called soci_db.
• user=soci_dev1: It will log in to our server as the soci_dev1 user.
• password=Secure123: The password Secure123 is used for the soci_dev1 user.

Remember to input the correct information that reflects your database environment — use the correct database name, user name, and password you created earlier in this article!

Getting Data

After the soci::session object is created, we are connected to the database backend and are ready to interact with it in some way.

Let’s take a look at inserting a new user record into the users table using SOCI. Furthermore, it would be nice if we could manually type in values for the new user’s first name, last name, and email columns.

To accomplish this, we start by creating three empty string objects that will store the user’s first name, last name, and email information. These string objects are then passed into a utility function called get_data in order to populate the string objects with values. We also pass a prompt string as the first parameter to this function:

// 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);

The get_data function is defined directly below our using statements:

template<typename T>
void get_data(const string prompt, T& value) { 
    cout << prompt; 
    cin >> value;
}

This function simply displays a prompt in the terminal, and allows us to enter a value that will be used to initialise the object T. Let’s take a closer look at the implementation:

• template<typename T>
The function get_data is in fact a template function. In the first line we declare a type called T, and use it as a placeholder for the function’s second parameter.
• void get_data(const string prompt, T& value)
The T placeholder serves as the type for the second parameter, called value. It is also passed by reference — denoted by the & symbol. The first parameter is of type const string, and will serve as a simple prompt.
• cout << prompt;
The prompt is output to the terminal, and should instruct the user of what type of data to enter.
• cin >> value;
Lastly, T is initialised with some data using cin.

The get_data template function is a useful utility that can be reused throughout the application. It prevents us from having to re-type the same code in multiple places to do the same task of initialising an item with some custom data.

Inserting Data

The application now proceeds to insert a new user in the users table with the string data we have collected via the keyboard. We call another utility function called insert_user to accomplish this:

// Insert a new row into users table
insert_user(sql, first_name, last_name, email);

insert_user is another utility function we have written ourselves that enables us to quickly add many users to the database with one function call. It is defined near the top of our source code:

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;
}

Firstly, we must provide four items to the insert_user function:

• session& sql: A reference to our session object, which contains our database connection. It is required to execute SQL statements.
• const string& parameters: Are references to string objects representing the user’s first_name, last_name and email values that we would like to insert into the database.

Inside the function, we use the session object to immediately execute an INSERT INTO SQL statement to add a new user to the database:

// 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");

Notice that the SQL statement we would like to execute is a string enclosed in double quotes, and comes after the insertion operator (<<). We realise from this statement that the insertion operator has been overloaded within the soci::session object to provide an easy mechanism for executing SQL statements. With SOCI, we also do not need to terminate the SQL statement with a semi colon.

In order to attach data to the SQL string, SOCI provides mechanisms to bind local buffers for input and output data. To use this data binding feature, we firstly specify placeholders within the SQL statement string in the form of :placeholder_name. For example, we have specified :fn, :ln, and :e placeholder names in the statement above, which are essentially “holes” that the first_name, last_name, and email strings will fill. The placeholders represent holes for input variables.

A comma follows the SQL statement string, and then the soci::use expression is called three times (also separated by commas) to bind our local variables to the SQL string. We can bind variables in two ways:

• Binding by position: Where soci::use expressions are used in order of the “holes” in the statement.
• Binding by name: Where the placeholder name is also passed to soci::use to clearly associate the local variable with the given placeholder.

Binding by name is adopted in our implementation because we also pass the placeholder name along with the variable it will be associated with. For example, the first_name string will be slotted into the :fn placeholder, and so on.

SOCI assumes local variables provided as soci::use elements live as long as it takes to execute the whole statement. Therefore, named variables should be used to ensure object lifetime is sufficient.

The function lastly outputs a message to the terminal, informing the application’s user that the SQL statement executed successfully:

cout << "> Successfully inserted user." << endl << endl;

Retrieving Data

In addition to inserting data, we would also like SOCI to retrieve data from the database. Therefore, this section will describe how we can retrieve all rows from the users table and display each row’s data to the terminal.

Another utility function named display_users is used that handles the task of displaying all records in the users table. The soci::session object containing the database connection is also passed by reference into this function:

// Retrieve all rows from users table and output data
display_users(sql);

The display_users function is defined just above the main function in our source file:

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; 
    }
}

Let’s firstly take a look at how we can retrieve row data from MySQL:

rowset<row> rs = (sql.prepare << "SELECT * FROM users");

The soci::rowset<T> container class provides a means of executing queries and accessing results dynamically using an STL-like interface. In the line above, we are retrieving rows from the users table and storing them in a soci::row object. In other words, the query results are bound to soci::row elements, which are accessible through the soci::rowset<T> interface.

We call the soci::session::prepare method to not only execute the SQL statement, but also set up a PREPARED statement on the MySQL server. As a result, the SELECT statement will be cached on the database server and will not need to be re-parsed by MySQL in future calls — meaning it can execute immediately, potentially speeding up data retrieval.

With the rows fetched from the database backend and returned to our application, we then enter a for loop and iterate through the result set:

// Iterate through the result set 
for (rowset<row>::const_iterator it = rs.begin(); 
        it != rs.end(); ++it) {
    // ...
}

Similar to iterating through a STL container such as a vector or list, we instantiate a constant input iterator that points to the first soci::row in the soci::rowset<T> result set with a call to rs.begin(). The loop will iterate on each row until it reaches the last row. The loop ends when the iterator reaches the last item in the soci::rowset<T>. The last soci::row is checked by calling rs.end().

Our for loop implementation is displayed in the following code snippet:

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;

Inside the for loop we create a variable called r that stores a constant reference to the soci::row that the iterator is currently pointing at in memory. From here we can use this variable to access the value of each column of the row, and output its value to the terminal.

The soci::row::get method is called multiple times within the cout statement. Let’s firstly examine its syntax:

row.get<column_data_type>(column_index)

• column_data_type : Represents the data type of the column we would like to retrieve.
• column_index: The column index number, where the first (far-left) column of the table has an index value of 0, the next column having an index value of 1, and so on.

The data type expressed in column_data_type must match up with the actual data in the database column. For example:

row.get<int>(0)    // Returns user's id as an int
row.get<string>(1) // Returns user’s first name as a string

If an incorrect type T is requested when calling row::get<T>(), an exception of type std::bad_cast is thrown.

With that, our application is complete. I hope it gives you some ideas of how you can develop simple applications with SOCI, and that it also encourages you to check out the official SOCI documentation to learn more about the workings of the API.

Let’s move forward and talk about compiling and running our application!

Compiling and Running Our Application

This section will walk through the process of compiling and running our application. The instructions presented in this section will primarily focus on UNIX environments — such as the Mac OS X and Linux operating systems.

For readers who are using Windows, feel free to read through the following instructions and try to mirror the process using your IDE’s compiler settings. As soon as I’m in possession of a Windows system, this section will be updated with specific instructions for Windows OS!

UNIX Environments (Mac OS X, Linux, UNIX)

We shall use the G++ compiler (also referred to as the GCC compiler) to compile our C++ application. Additionally, we will do the entire compilation process inside a terminal.

To check if your system has the g++ compiler installed, run the following command inside a terminal:

which g++

The path to the g++ binary should output to the terminal after running this command — a typical location is /usr/bin/g++. If nothing is returned, either:

• Mac OS X: Download the Xcode command line tools.
• Linux: Download and install the appropriate gcc package using your Linux distribution’s native package manager.

Moving forward, we now need to configure some search paths that the g++ compiler will consult to locate necessary header files and shared libraries on our filesystem that are required by our application. These search paths will be set via environment variables.

The following command exports the CPATH environment variable. The compiler will use the search paths set in this variable to find our application’s header files:

export CPATH="/usr/local/include:/usr/local/mysql/include"

• /usr/local/mysql/include is not a standard file location that compilers will check by default. Therefore, we specify it here so mysql.h can be found.

The next command exports the LIBRARY_PATH environment variable. The compiler will use the search paths set in this variable to locate necessary shared libraries during the linking phase:

export LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib"

• /usr/local/mysql/lib is also a custom location that the compiler doesn’t check by default. Since the libmysqlclient.so shared library is located here, we must export its location.

Additionally, we export the LD_LIBRARY_PATH environment variable, which is consulted when the actual program is executed (after it has been compiled and linked) to search for directories containing shared libraries:

export LD_LIBRARY_PATH="/usr/local/lib:/usr/local/mysql/lib"

Use the printenv command to verify your environment. Call printenv by itself to list all of your environment variables, or printenv VARIABLE_NAME to display the value of a single environment variable.

With the search paths set up we are now ready to compile the application using the g++ command. After navigating to the directory containing your hello.cpp source file, execute the following command:

g++ -std=c++11 hello.cpp -o hello -lsoci_core -lsoci_mysql -ldl 
    -lmysqlclient

The following list breaks down each argument we provide the g++ command:

• -std=c++11 : We will request to use the features of the C++ 11 standard to build our target.
• hello.cpp: The single source file representing our program.
• -o hello: An executable file called hello will be produced as output.
• -lsoci_core -lsoci_mysql -ldl: Shared libraries required to interface with the SOCI API.
• -lmysqlclient: The official MySQL shared library.

Providing everything went smoothly during the compiling and linking phases, we can now run our final executable from the terminal:

./hello

Running our final executable.

In Summary

We have covered a lot of ground in this article and explored the various stages that need considering if you are a developer of database software using C++.

We looked at installing our adopted database access library — SOCI, as well as the Boost libraries. We also set up a database environment with MySQL, which involved creating various database objects and a user with developer privileges. From there, we wrote a simple C++ application to demonstrate some of the SOCI API. Our application connects to a database backend, and is able to interact with it to insert and retrieve data. Lastly, we looked at compiling our application using the GCC compiler which involved exporting necessary environment variables, enabling the compiler to resolve the application’s header and library dependencies.

To continue learning about SOCI and implementing more sophisticated database applications, I certainly recommend reading the official SOCI documentation, including the User Guide section, which breaks down each feature of the library whilst also providing example code.

I have also be published an article that builds directly from this one and walks through developing a more sophisticated application with SOCI and MySQL:

Working with Databases in C++: Creating a Social Following App

In addition, you may wish to try the following challenges to build on and solidify the knowledge you have gained in this article:

• Use SOCI to connect to another backend database, such as PostgreSQL or Firebird.
• Write some unit tests with a testing library, such as Boost.Test.
• Expand your database environment by creating more tables with certain relationships, and then query them with the SOCI API.

Keep in mind that you will need to include the correct SOCI header file, and link to the correct shared library depending on your adopted database backend.