SOCI is a free database access library that’s written in C++. The library itself is distributed via SourceForge and the documentation can be found both on the official website and SourceForge.
Initially designed as a C++ alternative to Oracle Call Interface, the scope of SOCI has greatly expanded over time. This library now serves as an easy way to access databases with C++ and supports different types of databases including MySQL, ODBC, Oracle, PostgreSQL, and SQLite3 with plans to add support for other popular database types as well.
As a company specializing in C++ development, we often use SOCI when we need to work with MySQL in C++, as it allows us to essentially create SQL queries within standard C++ code. In this article, we’ll show you a practical example of how to use SOCI with MySQL in order to create a simple SOCI MySQL backend.
Contents:
Creating a DbTableBase Class
One of the prerequisites to implementing a full-fledged application with MySQL and SOCI is having a class that contains Insert, Update, and Delete functions, among others. Let’s name this class DBTableBase.
Our DBTableBase class must contain a member with the name of the:
It must also include the main interface for communicating with SOCI:
Now we need to implement this template class for each table in the database that inherits the base class. In our example, it will look like this:
Creating a Table
As an example, let’s create a small database called Students that will contain information on current students at a university. Let’s add id
, name
, last_name
, and faculty
fields.
id | name | last name | faculty |
... | ... | ... | ... |
In order to create the table, first we need to define the structure of the fields within it. The best way to do this is with BOOST_FUSION_DEFINE_STRUCT, which defines and adapts the structure as a model of Random Access Sequence.
The id
field is set with the help of boost::optional
. This is necessary to determine how this field is initialized. If the id field hasn’t been initialized, it will contain the value boost::none
. This function is very useful for fields that can contain a null value (keeping in mind that in MySQL, null and 0 are different).
Next, we’ll define several constants:
Then we’ll create a Students object:
And then create the table:
Next, with the help of SOCI, we can construct a query to create the Students table:
The method DBTableBase::BeginCreateTableQuery
can be used as follows:
The GetColName template function is defined like this:
This construct will return the name of the specified column.
If you need to set a column for cascade deletion, you can use this:
At the end of our query, we need to specify:
We’ve now finished creating the Students_table.
Insert
Next, we need to fill this table with data. Let’s look at how we can insert a few lines into our Students table so it looks like this:
id | name | last_name | faculty |
0 | William | Taylor | FEIT |
1 | Mary | Davies | FEIT |
2 | Jack | Smith | FEIT |
First, let’s create three objects in db::Student:
Then we’ll call the Insert function to add them to DB:
Here’s the implementation of the Insert function:
Here’s the implementation of the GetColNames function:
And here’s the implementation of the Call function:
Update
Now let’s consider a situation when we’ve entered incorrect data into the table. For example, we’ve entered the first name of a student by the last name Davies as Emily instead of Mary. To correct this mistake, we need to update the entry for studentDavies.
Here’s the implementation of Update:
After this operation, we can see the change in the table:
id | name | last_name | faculty |
0 | William | Taylor | FEIT |
1 | Emily | Davies | FEIT |
2 | Jack | Smith | FEIT |
Delete
We can use the following command to delete students’ information by id:
where:
After this operation, we’ll have an empty table.
Transaction
If you have a series of operations that need to be performed together where an error in one operation must cause the cancellation of all previous operations, then you need to use soci::transaction:
If an error occurs when adding a student to the database, all insert operations will be cancelled.
Select
Let’s consider a situation when we need to get the records for a student with id 2. In order to do this with SOCI, we need to use:
As a result, we’ll get the following table:
id | name | last_name | faculty |
2 | Jack | Smith | FEIT |
We can implement the SelectByID function as follows:
Let’s try to make a more complex query now – for example, let’s say we need to select all students with the name William Taylor:
id | name | last_name | faculty |
0 | William | Willson | FEIT |
1 | William | Taylor | FEIT |
2 | Taylor | Davies | FEIT |
3 | Jack | Taylor | FEIT |
By executing this query, we’ll get:
id | name | last_name | faculty |
1 | William | Taylor | FEIT |
We can implement an easy way to get the desired string using several search parameters with the help of this function:
Conclusion
SOCI is one of the most popular and refined database access library examples out there. Our article shows only a few ways in which you can use SOCI to work with databases, but it’s a nice illustration of the basic methodology. We hope that this information will prove useful and will encourage you to look more into SOCI in the future.