Click here to Skip to main content
15,861,168 members
Articles / Database Development / MySQL
Tip/Trick

SQL Database Access

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
18 Mar 2019MIT2 min read 10.2K   12   1
SQL database access

When I first set out to make a post about database access from C++, I was going to write about MySQL Connector/C++. But for some reason, that didn’t sit well with me. After sleeping on it, I realized it didn’t appeal to me because it was:

  1. limited to only one database backend and
  2. too low-level of an API

I wanted to write about a library that supports multiple database backends and abstracts the connection details as much as possible. Ideally, I wanted a library that brings you closest to SQL syntax rather than deal in C++ mechanics. So in my quest for cool and portable C++ libraries, I decided to keep looking…

And then, I came across SOCI – The C++ Database Access Library. 🙂 It has everything I was looking for: multiple backend support (DB2, Firebird, MySQL, ODBC, Oracle, PostgresSQL, and SQLite3), and a very natural way of issuing SQL queries, thanks to operator overloading and template sorcery. Even their first example is purposely left without comments because it is that easy to read and understand.

Besides, a very natural way of talking to a SQL backend what I like most about it is that it allows you, in a non-intrusive way (no existing code change needed), to store and retrieve your custom data structures to and from database tables.

So I installed MySQL server on my Mac using brew package manager and started coding. Within 30 minutes, I had a working example that connected to my database server, created a database and a table, inserted rows into the table from my custom Person data structure, counted the rows, retrieved a table entry with a given ID, and finally cleaned up after itself.

The only code that requires explaining is the struct type_conversion<Person> object. It is SOCI’s mechanism of converting to and from custom data structures and requires 2 methods: from_base which converts from a set of row values to a structure, and to_base which goes the other way. The rest is self explanatory! Here’s how you can get started:

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

using namespace std;
using namespace soci;

struct Person
{
	int ID;
	string FirstName;
	string LastName;
	int DOB;
	string EMail;
};

namespace soci
{
	template<>
	struct type_conversion<Person>
	{
		typedef values base_type;

		static void from_base(const values& v, indicator, Person& p)
		{
			p.ID = v.get<int>("ID");
			p.FirstName = v.get<string>("FirstName");
			p.LastName = v.get<string>("LastName");
			p.DOB = v.get<int>("DOB");
			p.EMail = v.get<string>("EMail");
		}

		static void to_base(const Person& p, values& v, indicator& ind)
		{
			v.set("ID", p.ID);
			v.set("FirstName", p.FirstName);
			v.set("LastName", p.LastName);
			v.set("DOB", p.DOB);
			v.set("EMail", p.EMail);
			ind = i_ok;
		}
	};
}

int main()
{
	try
	{
		session sql(mysql, "host=localhost user=root password=''");

		sql << "CREATE DATABASE blog";
		sql << "USE blog";
		sql << "CREATE TABLE people (ID INT, FirstName TEXT, LastName TEXT, DOB INT, EMail TEXT)";

		Person him{1, "Martin", "Vorbrodt", 19800830, "martin@vorbrodt.blog"};
		Person her{2, "Dorota", "Vorbrodt", 19800127, "dorota@vorbrodt.blog"};
		sql << "INSERT INTO people (ID, FirstName, LastName, DOB, EMail) 
                       VALUES (:ID, :FirstName, :LastName, :DOB, :EMail)", use(him);
		sql << "INSERT INTO people (ID, FirstName, LastName, DOB, EMail) 
                       VALUES (:ID, :FirstName, :LastName, :DOB, :EMail)", use(her);

		int count{};
		sql << "SELECT COUNT(*) FROM people", into(count);
		cout << "Table 'people' has " << count << " row(s)" << endl;

		Person pOut{};
		sql << "SELECT * FROM people WHERE ID = 1", into(pOut);
		cout << pOut.FirstName << ", " << pOut.LastName << ", " << pOut.DOB << ", " 
                                       << pOut.EMail << endl;

		sql << "DROP TABLE people";
		sql << "DROP DATABASE blog";
	}
	catch (exception& e)
	{
		cerr << e.what() << endl;
	}
}

Program output:

Table ‘people’ has 2 row(s)
Martin, Vorbrodt, 19800830, martin@vorbrodt.blog
This article was originally posted at https://vorbrodt.blog/2019/03/18/sql-database-access

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questiongetting rid of needless typespec in v.get<>("columnname")? Pin
Jan Heckman19-Mar-19 2:18
professionalJan Heckman19-Mar-19 2:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.