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.