The right way to use QtSqlDatabase across multiple threads

[batcher]

Hello,

It's being two days that I am trying to make a multithreaded application that need to collect data from several databases.
I tried to make a QRunnable + QThreadPool but I get an first chance access violation when calling QSqlDatabase::open() on a db created in the QRunnable::run(). After this failed I tried to use Worker-Object+QThread this option work, but I didn't figure out how to put the thread on sleep when data collection is done (I collect data every 30m).
I need to know about the correct and the right way to use QSqlDatabase across multiple threads.

[VRonin]

Could you post your worker+thread solution?

[SGaist]

Hi,

To add to @VRonin, one thing you have to do is to name your connection on a per thread basis. Otherwise you'll have threads starting more or less at the "same time" thus trying to use the default connection and therefore shooting at each other.

[batcher]

I agree with you on naming connection which I did, every thread is using his unique connection name.
Worker object:

class AgencyMasterInfoFetcher: public QObject {
    Q_OBJECT

    QSqlDatabase* _db;
    bool _cant_fetch;

public:
    explicit AgencyMasterInfoFetcher(QSqlDatabase* db, QObject* parent = nullptr);

public slots:
    void fetch();
    void stop();

signals:
    void closing();
    void fetched(AgencyMaster);
    void error(QString);

};

Implementation

void AgencyMasterInfoFetcher::fetch()
{
    if(_cant_fetch) return;
    if(!_db->isOpen()) if(!_db->open()) { 
emit error(QString{"Couldn't connect to the database"}); qDebug() << "Done fetching"; return; }
    _cant_fetch = true;
// fetching some data

    emit fetched(agency);
    _cant_fetch = false;
}

void AgencyMasterInfoFetcher::stop()
{
    _db->close();
    emit closing();
}

starting threads from main.cpp

int main() {
  QVector<QThread*> threads {};
  for (int i = 0; i < dbs.size(); i++) threads << new QThread();

  QVector<AgencyMasterInfoFetcher*> workers{};
  for (int i = 0; i < dbs.size(); i++) {
      workers << new AgencyMasterInfoFetcher(&dbs[i]);
      workers[i]->moveToThread(threads[i]);
      QObject::connect(agencies[i],SIGNAL(needData()),workers[i],SLOT(fetch()),Qt::QueuedConnection);
      QObject::connect(workers[i],SIGNAL(fetched(AgencyMaster)),agencies[i],SLOT(setData(AgencyMaster)),Qt::QueuedConnection);
      QObject::connect(workers[i],SIGNAL(error(QString)),agencies[i],SLOT(error_fetching(QString)),Qt::QueuedConnection);
      QObject::connect(workers[i],SIGNAL(closing()),threads[i],SLOT(quit()),Qt::QueuedConnection);
      QObject::connect(threads[i],SIGNAL(finished()),workers[i],SLOT(deleteLater()),Qt::QueuedConnection);
      QObject::connect(threads[i],SIGNAL(finished()),threads[i],SLOT(deleteLater()),Qt::QueuedConnection);
      threads[i]->start();
  }
}

[VRonin]

Probably just worth telling us how you create dbs

I collect data every 30m

Just have your agencies emit needData every 30 minutes

[batcher]

Sorry I forget about it and it's the main concerne :p
dbs is QVector<QSqlDatabase> created in the main fonction with calls to QSqlDatabase::addDatabase("QODBC3","agency1") the connection name will vary (agency1,agency2,....etc)