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...
    }
}

Please show are you using this class to create two objects for the two databases. I have done this with MySQL and SQLite without problems. — john elemans
– john elemans, Commented Feb 3, 2017 at 17:56
See posted examples above, I have created two instances of the manager class within the program, one for the sqlite and one for the ms sql. The sqlite instance is passed by reference and is available to all classes with the application. The ms sql instance is a private member pointer within the program class. After instantiation and connection, I then call whatever public function of the db manager class that is needed at the time i.e.: for updating records, retrieving records etc. — DonJoe
– DonJoe, Commented Feb 3, 2017 at 18:19
The difference between your code and mine is that I sent in a connection name; myDB = QSqlDatabase::addDatabase(databaseType,connectionName); I think this may address your problem. (different connection name for each db) — john elemans
– john elemans, Commented Feb 3, 2017 at 18:41
@johnelemans, Thanks for the assistance. I had attempted to use a connection name earlier without any luck. However, after reading a bit deeper in the usage of QSqlQuery: doc.qt.io/qt-5/qsqlquery.html#QSqlQuery-2, I needed to name the connection and pass the database instance into the QSqlQuery constructor. All seems to work as expected. — DonJoe
– DonJoe, Commented Feb 3, 2017 at 22:05

RobbieE · Accepted Answer · 2017-02-04 08:07:48Z

15

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");

answered Feb 4, 2017 at 8:07

RobbieE

4,3703 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DonJoe Over a year ago

Thanks for the response. Prior to your response, I had resolved my issues. You are correct, I found by reading a little deeper into the documentation that I needed to provide a connection name and I also needed to pass the database instance to the qsqlquery so that it would not use the default instance. As for not needing the manager class due to redundancy, I will not argue that I as am relatively new to Qt. However, I yet to find built in functions similar to the ones defined in the manager class. In other words keep all of my database code separate from the class where it is used.

Paul-Sebastian Manole Over a year ago

Does calling QSqlDatabase::database() (which is marked thread-safe) return the same instance of a connection every time, or does it return a new instance of a connection every time such that after you've added a connection with QSqlDatabase::addDatabase() (thread-safe as well), and you don't use the instance that it returns, you can get a new connections with the same settings from different threads without memory violations? In other words, is QSqlDatabase::database() a sort of a factory method?

RobbieE Over a year ago

Calling QSqlDatabase::database() will return the exact same instance that was created with QSqlDatabase::addDatabase() for a given connectionName (or the default connection). As mentioned in the Thread Support in Qt, you cannot use connections across thread boundaries, you should create new connections for each thread.

RobbieE Over a year ago

Hey @DonJoe . I see that this question still does not have a selected answer. If you managed to solve your own question, you should post your solution and mark it as the correct answer so that others with a similar problem can benefit.

Collectives™ on Stack Overflow

Qt - Handling multiple database connections simultaneously

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related