QThreadPool and QSqlDatabase

antreimer

I've writin a client server application and am using the following. Are there any drawbacks I'm not aware of.

class Runnable : public QRunnable{
    QSqlDatabase db(){
            QString dbString = QString::number((qintptr)QThread::currentThreadId());
            QSqlDatabase db = QSqlDatabase::database(dbString);
            if(!db.isOpen()){
                db = QSqlDatabase::addDatabase("QPSQL",dbString);
                db.setHostName("localhost");
                db.setDatabaseName("test");
                db.setUserName("user");
                db.setPassword("password");
                db.open();
                qDebug() << "Opened db for thread:" << dbString << db.lastError().text();
        
                auto conn = std::make_shared<QMetaObject::Connection>();
                *conn = QObject::connect(QThread::currentThread(),&QThread::finished,[dbString, conn](){
                        QObject::disconnect(*conn);
                        QSqlDatabase::removeDatabase(dbString);
                        qDebug() << "Closed db for thread:" << dbString;
                    });
            }
            return db;
    }
    
    void run(){
        // do database related stuff
        QSqlQuery query("select * from persons", db());
    }
};

Edit: Fixed code

SGaist

Hi,

Since you are using PostgreSQL the multi-threaded part should work. However you are missing the check when opening the database as well as a mean to propagate that information.

Rather than connecting the destroyed signal, I'd use the finished signal that i'd emit before leaving run. That will make the database closing more predictable.

antreimer

@SGaist
So you are saying you would close the connection at the end of the run() command? The QRunable normally takes 100ms but ocasionly complex work is done that takes 20 or 30 seconds that I don't want to have blocking.
For checking if the database had opened I was simply outputing the lasterror text but I deleted the debug lines in the first post.
I noticed that I was creating a new signal slot connection during each run() and moved it into the if(!db.isOpen) block.
Are you saying that SQlite or MariaDB have issues with multiple connections to the same database?

class Runnable : public QRunnable{
    QSqlDatabase db(){
        QString dbString = QString::number((qintptr)QThread::currentThreadId());
        QSqlDatabase db = QSqlDatabase::database(dbString);
        if(!db.isOpen()){
            db = QSqlDatabase::addDatabase("QPSQL",dbString);
            db.setHostName("localhost");
            db.setDatabaseName("test");
            db.setUserName("user");
            db.setPassword("password");
            db.open();
            qDebug() << "Opened db for thread:" << dbString << db.lastError().text();
    
            QObject::connect(QThread::currentThread(),&QThread::finished,[dbString](){
                    QSqlDatabase::removeDatabase(dbString);
                    qDebug() << "Closed db for thread:" << dbString;
                });
        }
        return db;
    }
    
    void run(){
        // do database related stuff
        QSqlQuery query("select * from customers", db());
    }
};

SGaist

Yes, unless I'm mistaken the operations you'll be doing there even if long should be over once reaching the end of run. What do you mean by blocking in this case ?

No, I'm saying that you were using a real database server. MariaDB is also a real server. You need to check for SQLite that your are only using thread safe stuff. You have informations about it here and a thread about it on this forum there

antreimer

I'm using this code on the server. By blocking I simply meant that if one client requested something that took long to process it shouldn't block the other clients. I may upload a working sample of this code to github later.