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.
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:
As an example, let’s create a small database called Students that will contain information on current students at a university. Let’s add
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.
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:
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.
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:
First, let’s create three objects in
Then we’ll call the Insert function to add them to
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:
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:
We can use the following command to delete students’ information by id:
After this operation, we’ll have an empty table.
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
If an error occurs when adding a student to the database, all insert operations will be cancelled.
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:
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:
By executing this query, we’ll get:
We can implement an easy way to get the desired string using several search parameters with the help of this function:
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.