Click here to Skip to main content
14,024,060 members
Click here to Skip to main content
Add your own
alternative version

Stats

6K views
10 bookmarked
Posted 18 Mar 2019
Licenced MIT

SQL Database Access

, 18 Mar 2019
Rate this:
Please Sign up or sign in to vote.
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:

#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

License

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

Share

About the Author

Martin Vorbrodt
Software Developer (Senior)
United States United States
No Biography provided

You may also be interested in...

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190419.4 | Last Updated 18 Mar 2019
Article Copyright 2019 by Martin Vorbrodt
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid