How to work with SQL in C++ and Qt

You can work with SQL queries in Qt and SQL in many different ways, here I will show you how I came up with a working method. To keep things simple, I am not going to use any UI to print or edit queries. It will be just source code and stdout, as a database engine I will use SQLite3.

After you created your project, edit project.pro and locate line:

QT       += core

Modify this line by adding `sql`:

QT       += core sql

Since now, when you compile your project, QtSql module will be linked to the project:

g++ -Wl,-O1 -Wl,-O1,--sort-common,--as-needed,-z,relro -o QtSql main.o   -lQt5Sql -lQt5Core -lpthread


Start editing source code from adding those lines at the top:

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QTextStream>
#include <QDebug>

To create connection with the database and open it, use addDatabase, providing database engine name “SQLITE” and connection name “main”. To open connection with the database, you need to setDatabaseName for SQLite databases and provide relative or absolute path to the file. If the file doesn’t exits, it will be created:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "main");
db.setDatabaseName("myDatabase.sqlite3");

Let’s see if you can open connection now:

if(db.open()) {
    cout << "db opened!\n";
} else {
    cout << "problem: " << db.lastError().text();
}

Just to prove that it’s created and empty, list of tables (of course there is nothing):

cout << "list of tables: \n";
foreach (QString table, db.tables()) {
    cout << table;
}

Prints:

➜  QtSql  ./QtSql 
db opened!
list of tables: 
➜  QtSql

So let’s create a table. Just a very basic one:

CREATE TABLE people (id INTEGER PRIMARY KEY, name VARCHAR, age INTEGER, alive BOOLEAN);

To execute the query, you need an instance of QSqlQuery connected to proper QSqlDatabase object after the database is opened, so rewrite the if statement as follows:

if(db.open()) {
        cout << "db opened!\n";
        QSqlQuery query(db);
        cout << "number of tables: " << db.tables().count() << "\n";
        cout << "executing query\n";
        query.exec("CREATE TABLE people (id INTEGER PRIMARY KEY, name VARCHAR, age INTEGER, alive BOOLEAN);");
        cout << "number of tables: " << db.tables().count() << "\n";
}

That’s it, the table is created. Inserting object is a bit more complicated. Before inserting data, query must be prepared, binded and then executed:

count << "number of people before query: " << countPeople(query) << "\n";
 
query.prepare(
    "INSERT INTO people ( "
    "name, age, alive ) "
    "VALUES ( "
    ":name, :age, :alive );"
            );
query.bindValue(":name", "agilob");
query.bindValue(":age", 13);
query.bindValue(":alive", 1);
 
if(query.exec() == false)
    debugQuery(query);
 
cout << "number of people after query: " << countPeople(query) << "\n";

Things you should notice here are new methods debugQuery(QSqlQuery) and int countPeople(QSqlQuery). The first one is called when query was not successfully committed. It prints last query, number of affected or not affected rows and the error:

void debugQuery(QSqlQuery query) {
    qDebug() << "LAST QUERY: " << query.lastQuery();
    qDebug() << "ROWS AFFECTED: " << query.numRowsAffected();
    qCritical() << "LAST ERROR: " << query.lastError().text();
}

As you think, countPeople selects all rows from table `people` and counts them. There are at least two ways of doing it, one in Cpp and one by SQL:

int coutPeople(QSqlQuery query) {
    int counter = 0;
    query.exec("SELECT * FROM people");
 
    while(query.next())
        counter++;
 
    return counter;
}
int countPeople(QSqlQuery query) {
    query.exec("SELECT COUNT(*) FROM people");
    query.next();
    return query.value(0).toInt();
}

As you can see now, to get number of rows or any data from SELECT query, you need to call next()
QSqlQuery::value(int index) returns a QVariant object, which must be converted to your final type. It’s very easy to do so. The QVariant class provides plenty of methods to get int, double, QDate, QString…

Deleting a row is as simple as creating a table, but in this method I would like to know how many rows were deleted. After debugQuery add:

cout << "number of people before deleting: " << countPeople(query) << "\n";
cout << "rows affected: " << deleteRow(query) << "\n";
cout << "number of people after deleting: " << countPeople(query) << "\n";

and the method to delete rows is as follows:

int deleteRow(QSqlQuery query) {
    query.exec("DELETE FROM people WHERE id = 1;");
    return query.numRowsAffected();
}

Some of the common errors you might have are:

  • incorrect value binding
  • not calling exec() or finish() on QSqlQuery
  • stupid typos
  • query is still active, even after you called exec()

If you want to see the whole Qt project, it’s available on my Gitlab.

[Total: 0    Average: 0/5]