Installation

In the first place, we need to install the MySQL Connector for C++. I am using Ubuntu, so I will use apt-get. Please adapt if you are using another distro not based on Debian.

sudo apt-get install libmysqlcppconn-dev libmysqlcppconn7v5

We can try our database, for that I will use the example 2 included in the MySQL Connector. You can find the source code here

The test program is:

/* Standard C++ includes */
#include <stdlib.h>
#include <iostream>

/*
  Include directly the different
  headers from cppconn/ and mysql_driver.h + mysql_util.h
  (and mysql_connection.h). This will reduce your build time!
*/
#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

using namespace std;

int main(void)
{
    cout << endl;
    cout << "Let's have MySQL count from 10 to 1..." << endl;

    try {
    sql::Driver *driver;
    sql::Connection *con;
    sql::Statement *stmt;
    sql::ResultSet *res;
    sql::PreparedStatement *pstmt;

    /* Create a connection */
    driver = get_driver_instance();
    con = driver->connect("tcp://127.0.0.1:3306", "root", "secret");
    /* Connect to the MySQL test database */
    con->setSchema("datab");

    stmt = con->createStatement();
    stmt->execute("DROP TABLE IF EXISTS test");
    stmt->execute("CREATE TABLE test(id INT)");
    delete stmt;

    /* '?' is the supported placeholder syntax */
    pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");
    for (int i = 1; i <= 10; i++) {
        pstmt->setInt(1, i);
        pstmt->executeUpdate();
    }
    delete pstmt;

    /* Select in ascending order */
    pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");
    res = pstmt->executeQuery();

    /* Fetch in reverse = descending order! */
    res->afterLast();
    while (res->previous())
        cout << "\t... MySQL counts: " << res->getInt("id") << endl;
    delete res;

    delete pstmt;
    delete con;

    } catch (sql::SQLException &e) {
    cout << "# ERR: SQLException in " << __FILE__;
    cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
    cout << "# ERR: " << e.what();
    cout << " (MySQL error code: " << e.getErrorCode();
    cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    }

    cout << endl;

    return EXIT_SUCCESS;
}

We can compile the program with the following command and execute it:

(base) luis@BARE:~/projects/How to use MySQL/mysql_conn$ g++ test.cc -o test -l mysqlcppconn
(base) luis@BARE:~/projects/How to use MySQL/mysql_conn$ ./test 

Let's have MySQL count from 10 to 1...
        ... MySQL counts: 10
        ... MySQL counts: 9
        ... MySQL counts: 8
        ... MySQL counts: 7
        ... MySQL counts: 6
        ... MySQL counts: 5
        ... MySQL counts: 4
        ... MySQL counts: 3
        ... MySQL counts: 2
        ... MySQL counts: 1

(base) luis@BARE:~/projects/How to use MySQL/mysql_conn$

If everything went well, we should see the MySQL count down.

Using our database

Following the previous post, I will create a database to store pets. As we are going to use C++ the process won’t be so interactive as the previously Python examples.

I have created the following example.cc to ilustrate how to use our database

/* Standard C++ includes */
#include <stdlib.h>
#include <iostream>

/*
  Include directly the different
  headers from cppconn/ and mysql_driver.h + mysql_util.h
  (and mysql_connection.h). This will reduce your build time!
*/
#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

using namespace std;


sql::Connection * connect()
{    
    /* Create a connection */
    sql::Driver *driver = get_driver_instance();
    sql::Connection *con = driver->connect("tcp://127.0.0.1:3306", "root", "secret");
    /* Connect to the MySQL test database */
    con->setSchema("datab");
    return con;
}

void create_table(sql::Connection *con)
{
    sql::Statement *stmt;
    stmt = con->createStatement();
    stmt->execute("DROP TABLE IF EXISTS Pets");
    stmt->execute(" CREATE TABLE Pets ("
                    "PetID int NOT NULL AUTO_INCREMENT,"
                    "Name varchar(50) NOT NULL,"
                    "Category varchar(50) NOT NULL,"
                    "PRIMARY KEY (PetID)"
                    ");"
                );
    delete stmt;
}

void insert_pet(sql::Connection *con, string name, string category)
{
    sql::PreparedStatement *pstmt;
    pstmt = con->prepareStatement("INSERT into Pets(Name, Category) values (?, ?)");
    pstmt->setString(1, name);
    pstmt->setString(2, category);
    pstmt->executeUpdate();
    delete pstmt;
}

void list_pets(sql::Connection *con)
{
    sql::PreparedStatement *pstmt;
    pstmt = con->prepareStatement("SELECT * FROM Pets");

    sql::ResultSet *res;
    res = pstmt->executeQuery();

    while (res->next())
        cout << "[" << res->getInt("PetID") << "] " << res->getString("Name") << "\t" << 
        res->getString("Category") << endl;
    delete res;

}

void delete_pet(sql::Connection *con, int id)
{
    sql::PreparedStatement *pstmt;
    pstmt = con->prepareStatement("DELETE FROM Pets WHERE PetID=?");
    pstmt->setInt(1, id);
    pstmt->executeUpdate();
}

int main(void)
{
    cout << "Connecting" << endl;
    sql::Connection *con = connect();

    cout << "Creating the table" << endl;
    create_table(con);

    cout << "Adding Dog Luna" << endl;
    insert_pet(con, "Luna", "Dog");

    cout << "Adding Silvester and Twetee" << endl;
    insert_pet(con, "Silvester", "Cat");
    insert_pet(con, "Twetee", "Bird");
    
    cout << "Printing content of database" << endl;
    list_pets(con);

    cout << "Delete last pet" << endl;
    delete_pet(con, 3);

    cout << "Printing content of database" << endl;
    list_pets(con);

}

If everything goes well, you should see this output after compiling and running the program

(base) luis@BARE:~/projects/How to use MySQL/mysql_conn$ g++ example.cc -o example -l mysqlcppconn; ./example
Connecting
Creating the table
Adding Dog Luna
Adding Silvester and Twetee
Printing content of database
[1] Luna        Dog
[2] Silvester   Cat
[3] Twetee      Bird
Delete last pet
Printing content of database
[1] Luna        Dog
[2] Silvester   Cat
(base) luis@BARE:~/projects/How to use MySQL/mysql_conn$

Conclusion

I see a disadvantage of this method as we are limited to use MySQL as our SQL server

We can solve this limitation using the SQL access of Qt. In this way, we will be independent of the brand of the SQL server, the problem is that Qt can be an overkill if we only need it for SQL. Also, please check the license of Qt before using it in a commercial project.