Qt - Handling multiple database connections simultaneously

Question

I am in the process of converting a .net / c# application over to Qt. I do not work with Qt / C++ everyday so bear with me.

The application uses SQLite and MS SQL Server simultaneously. Once I have established a connection, it is left open as the data transactions are quite high for both data stores and I don't want to waste time opening and closing the connection between transactions.

My aim was to create a generic manager class that could be used for both database type. The class works as it should when either connection is open without the other. However, when I attempt to establish a connection to both simultaneously, I receive the errors:

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

So, I have read about connection naming However, I then run into problems accessing the database. I have posted the contents of my database manager class without connection naming. I am looking for suggestions on how I should be handling this in a clean and generic way. Thanks!

.h file

#ifndef DATABASEMGR_H
#define DATABASEMGR_H

#include <QObject>
#include <QSqlDatabase>
#include <QVariant>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlRecord>

class DatabaseMgr : public QObject
{
public:
    DatabaseMgr(const QString &databaseType, const QString &connectionString, QObject *parent = 0);
    ~DatabaseMgr();
    QVariant ExecuteScalar(const QString &cmd);
    bool ExecuteNonQuery(const QString &cmd);
    bool IsOpen() const;
    bool Connect();
    bool Disconnect();
    QSqlQueryModel *GetQueryModel(const QString &cmd);
    QSqlQueryModel *GetQueryModel(const QString &cmd, const QMap<QString, QVariant> &params);

private:
    QSqlDatabase mDb;
};

#endif // DATABASEMGR_H

.cpp

#include "databasemgr.h"

DatabaseMgr::DatabaseMgr(const QString &databaseType, const QString &connectionString, QObject *parent) {
    mDb = QSqlDatabase::addDatabase(databaseType);
    mDb.setDatabaseName(connectionString);
}

DatabaseMgr::~DatabaseMgr() {
    if (mDb.open()) {
        mDb.close();
    }
}

QVariant DatabaseMgr::ExecuteScalar(const QString &cmd) {
    QVariant mVariant;
    if (mDb.isOpen()) {
        QSqlQuery query;
        if (query.exec(cmd)) {
            while (query.next()) {
                mVariant = query.value(0);
            }
        }
    }
    return mVariant;
}

bool DatabaseMgr::ExecuteNonQuery(const QString &cmd) {
    if (mDb.isOpen()) {
        QSqlQuery query;
        if (query.exec(cmd)) {
            return true;
        } else {
            //todo handle error
        }
    } else {
        //todo handle error
    }
    return false;
}

bool DatabaseMgr::IsOpen() const {
    return mDb.isOpen();
}

bool DatabaseMgr::Connect(){
    if (!mDb.open()) {
        //todo error opening database??
        return false;
    }
    return true;
}

bool DatabaseMgr::Disconnect() {
    return mDb.isOpen();
}

QSqlQueryModel *DatabaseMgr::GetQueryModel(const QString &cmd) {
    QSqlQueryModel *model = new QSqlQueryModel;

    if (mDb.isOpen()) {
        model->setQuery(cmd, mDb);
    }

    return model;
}

QSqlQueryModel *DatabaseMgr::GetQueryModel(const QString &cmd, const QMap<QString, QVariant> &params) {
    QSqlQueryModel *model = new QSqlQueryModel;

    if (mDb.isOpen()) {
        QSqlQuery query;
        query.prepare(cmd);

        if (params.count() > 0) {
            QMapIterator<QString, QVariant> i(params);
            while (i.hasNext()) {
                i.next();
                query.bindValue(i.key(), i.value());
            }
        }

        model->setQuery(query);
    }

    return model;
}

Example usages below:

main.cpp (the SQLite db is used for storing global application settings)

DatabaseMgr mSqliteDb("QSQLITE", app.applicationName() + ".db", &app);
mSqliteDb.Connect();
Program prgm(mSqliteDb, &app);

program.cpp

mMsSqlDb = new DatabaseMgr("QODBC3", GetMsSqlConnectionString());
if (mMsSqlDb->Connect()) {
    AddToActivityLog("Connected to MS SQL DB");
} else {
    AddToActivityLog("Error connecting to MS SQL DB");
}

Getting results from within program member function:

QString cmd = "SELECT DISTINCT some_things FROM the_table ORDER BY these_columns";

QSqlQueryModel *model = mMsSqlDb->GetQueryModel(cmd);

if (model->rowCount() > 0) {
    for (int i = 0; i < model->rowCount(); ++i) {
        //Do stuff...
    }
}

Answer 1

The first fundamental mistake you're making is that your database connection manager is redundant. What you're looking to achieve is already built into the QtSql framework.

When you create a new instance of QSqlDatabase with QSqlDatabase::addDatabase that object is persisted in the Qt internals until you call QSqlDatabase::removeDatabase. There is no need for you to keep a variable connected to it. If you need to access the connection in-between, you simply call QSqlDatabase::database.

The second fundamental mistake is that you're confusing connectionName with connectionString.

connectionName is the arbitrary name you give to the connection to identify it as a separate, unique object in the internal collection of database connections. This can be thought of like a variable name.

connectionString is the set of instructions used to connect to the database engine itself, containing the database name, username, password, etc. You seem to have an understanding of this so I won't explain further.

You can create a connection to a database without giving it a database name, the QtSql framework will create an anonymous connection which it uses as the default connection for any calls you make without specifying a connection name.

If you try to create a second connection without a name, while a default connection already exists, you will force the first one to be destroyed and replaced by your new connection. This is what happens when you get that message.

Your mistake:

QSqlDatabase sqliteConnection = QSqlDatabase::addDatabase("QSQLITE");
sqliteConnection.setConnectionString("blah blah");

QSqlDatabase sqlServerConnection = QSqlDatabase::addDatabase("QODBC3"); // no name specified means overwrite default connection, generates warning message
sqlServerConnection.setConnectionString("blah blah");

What you should do:

QSqlDatabase sqliteConnection = QSqlDatabase::addDatabase("QSQLITE", "myUniqueSQLITEConnection");
sqliteConnection.setConnectionString("blah blah");

QSqlDatabase sqlServerConnection = QSqlDatabase::addDatabase("QODBC3", "myUniqueMSSQLConnection"); 
sqlServerConnection.setConnectionString("blah blah");

As I mentioned, these connections will persist in the QtSql internals until you call QSqlDatabase::removeDatabase.

To do so:

QSqlDatabase myAlreadyExistingMSSqlConnection = QSqlDatabase::database("myUniqueMSSQLConnection");