Skip to main content
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.
{code}
template<typename RowType = soci::row>
class DBTableBase
{
explicit DBTableBase(soci::session& session, const std::string& tableName)
: m_session(session)
, m_tableName(tableName)
{
}
/* Other methods */
}
{/code}
Our DBTableBase class must contain a member with the name of the:
{code}
std::string m_tableName;
{/code}
It must also include the main interface for communicating with SOCI:
{code}soci::session& m_session;{/code}
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:
{code}
class Students: public DBTableBase<Student>
{
Students(soci::session&am; session):
DBTableBase(session, g_studentsTableName)
{
}
/* Other methods */
}
{/code}
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.
{code}
typedef boost::optional<int64_t> IdType;
BOOST_FUSION_DEFINE_STRUCT((db), Student,
(db::IdType, id)
(std::string, name)
(std::string, last_name)
(std::string, faculty)
)
{/code}
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:
{code}const char g_nameId[] = "id";
const char g_nameIdType[] = "BIGINT PRIMARY KEY AUTO_INCREMENT";
const char g_notNull[] = "NOT NULL";
const char g_studentsTableName[] = "Students_table";
const std::string g_nameType("VARCHAR(255)");
{/code}
Then we’ll create a Students object:
{code}Students students(session);{/code}
And then create the table:
{code}students.CreateTable();{/code}
Next, with the help of SOCI, we can construct a query to create the Students table:
{code}
void Students::CreateTable() const
{
m_session << BeginCreateTableQuery()
<< ", " << GetColName<1>() << " " << g_nameType << g_notNull
<< ", " << GetColName<2>() << " " << g_nameType << g_notNull
<< ", " << GetColName<3>() << " " << g_nameType << g_notNull
<< ", " << DeleteCascadeQuery(GetColName<3>(), "Faculty_table")
<< EndCreateTableQuery();
}
{/code}
The method DBTableBase::BeginCreateTableQuery can be used as follows:
{code}
std::string DBTableBase::BeginCreateTableQuery() const
{
return std::string("CREATE TABLE" + m_tableName + " (" + g_nameId + " " + g_nameIdType;
}
{/code}
The GetColName template function is defined like this:
{code}
template<int col>
static constexpr std::string DBTableBase::GetColName()
{
return boost::fusion::extension::struct_member_name<RowType, col>::call();
}
{/code}
This construct will return the name of the specified column.
If you need to set a column for cascade deletion, you can use this:
{code}
std::string DBTableBase::DeleteCascadeQuery(const std::string& colName, const std::string& tableName) const
{
return std::string("CONSTRAINT fk_") + m_tableName + "_"+ colName + " FOREIGN KEY(" + colName +") REFERENCES " + tableName + "(" + g_nameId + ") ON DELETE CASCADE";
}
{/code}
At the end of our query, we need to specify:
{code}
std::string DBTableBase::EndCreateTableQuery() const
{
return ");";
}
{/code}
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:
{code}db::Student studentTaylor(db::IdType(), "William", "Taylor", "FEIT");
db::Student studentDavies(db::IdType(), "Mary", "Davies", "FEIT");
db::Student studentSmith(db::IdType(), "Jack", "Smith", "FEIT");
{/code}
Then we’ll call the Insert function to add them to DB:
{code}students.Insert(studentTaylor);
students.Insert(studentDavies);
students.Insert(studentSmith);
{/code}
Here’s the implementation of the Insert function:
{code}
int64_t DBTableBase::Insert(const RowType& row) const
{
std::stringstream query;
query << "INSERT INTO " << m_tableName << " (";
std::vector<std::string> colNames = GetColNames<RowType>();
std::stringstream colsNames;
std::stringstream colsValues;
for (const auto& col : colNames)
{
if (!colsNames.str().empty())
{
colsNames << ", ";
colsValues << ", ";
}
colsNames << col;
colsValues << ":" << col;
}
query << colsNames.str() << ") VALUES (" << colsValues.str() << ")";
DB_TRY
{
m_session <<query.str(), soci::use(row);
int64_t id = 0;
m_session.get_last_insert_id(m_tableName, id);
return id;
}
DB_CATCH
return 0;
}{/code}
Here’s the implementation of the GetColNames function:
{code
}static std::vector<std::string> DBTableBase::GetColNames()
{
std::vector<std::string> res;
GetColNames<RowType>::Call(res);
return res;
}
{/code}
And here’s the implementation of the Call function:
{code}template<typename RowType, int index = boost::fusion::size(*(RowType*)nullptr) - 1>
struct GetColNames
{
static void Call(std::vector<std::string>& val)
{
GetColNames<RowType, index - 1>::Call(val);
std::string name = boost::fusion::extension::struct_member_name<RowType, index>::call();
val.push_back(name);
}
};
{/code}
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.
{code}studentDavies.name = "Emily";
students.Update(studentDavies);
{/code}
Here’s the implementation of Update:
{code}bool DBTableBase::Update(const RowType& row) const
{
std::stringstream query;
query << "UPDATE " << m_tableName << " SET ";
std::vector<std::string> colNames = GetColNames();
for (const auto& col : colNames)
{
if (col != colNames.front())
{
query << ", ";
}
query << col << " = :" << col;
}
query << " WHERE " << g_nameId << " = " << row.id;
m_session << query.str(), soci::use(row);
return true;
}
{/code}
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:
{code}students.Delete(0);
students.Delete(1);
students.Delete(2);
{/code}
where:
{code}bool DBTableBase::Delete(int64_t id) const
{
m_session << "DELETE FROM " << m_tableName << " WHERE " << g_nameId << " = :id", soci::use(id);
return true;
}
{/code}
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:
{code}
std::unique_ptr<soci::transaction> transaction(new soci::transaction(m_session));
try
{
students.Insert(studentTaylor);
students.Insert(studentDavies);
students.Insert(studentSmith);
transaction->commit();
}
catch(const std::exception& ex)
{
std::cout << “Transaction error” << ex.what() << std::endl;
transaction->rollback();
}
{/code}
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:
{code}soci::rowset<db::Student> student = students.SelectByID(2);{/code}
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:
{code}soci::rowset<db::Student> DBTableBase::SelectByID(const int64_t id) const
{
std::stringstream query;
query << "SELECT id FROM " << m_tableName << " WHERE id = " << id;
return m_session.prepare << query.str();
}
{/code}
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 |
{code}typedef std::map<std::string, std::stringstream> KeyValuePairs;
KeyValuePairs value;
value["name"] << "William";
value["last_name"] << "Taylor";
soci::rowset<db::Student> rows = students.SelectByValues(value);
{/code}
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:
{code}
soci::rowset<rowtype> DBTableBase::SelectByValues(const KeyValuePairs& pairs) const
{
std::stringstream query;
query << "SELECT * FROM " << m_tableName << " WHERE ";
bool first = true;
for (const auto& pair: pairs)
{
if (first)
{
first = false;
}
else
{
query << " AND ";
}
query << pair.first << " = "" << pair.second.str() << """;
}
return m_session.prepare << query.str();
}
{/code}
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.