executing query after cloned connection is used in another thread
-
i have the following situation:
i have a gui application, where i need to do heavy database updating. i execute a query (stored as member) and display the results. then, when the user performs some gui action, i update the db, and in order to display the changes i re-execute the query.
this approach worked when i was doing the update queries in the gui thread.now i've changed the implementation:
the initial query is still executed in the main thread (i might move it to another thread in the future...), then when the user changes the values, i use this code:auto db = QSqlDatabase::cloneDatabase(QSqlDatabase::database(), "new_conn"); QFuture<bool> future = QtConcurrent::run(this, &MyClass::updateData, db, newVal); future.waitForFinished(); db.close(); reexecuteQuery();
after the first attempt to re-execute the query, i get this error:
database is locked Unable to fetch row
why does this happen? -
Hi,
Are you using a SQLite database ?
-
@sgaist
yes, sorry forgot to mention.i also tried this:
auto orig = QSqlDatabase::database(); auto db = QSqlDatabase::cloneDatabase(orig, "new_conn"); QFuture<bool> future = QtConcurrent::run(this, &MyClass::updateData, db, newVal); future.waitForFinished(); db.close(); orig.open(); reexecuteQuery();
but in this case i get
Parameter count mismatch
error from withinreexecuteQuery();
-
Why are you using QtConcurrent::run if you use waitForFinished ?
As for the parameter count mismatch, did you check that your query is correct ? -
Why are you using QtConcurrent::run if you use waitForFinished ?
As for the parameter count mismatch, did you check that your query is correct ?@sgaist
i'm following this example https://doc.qt.io/qt-5/qtconcurrent-runfunction-example.html
yes the query is fine when i do everything in a single thread -
It's just a basic example to illustrate how to use the
QtConcurrent::run
with a method to call with the other parameters given.What you are currently doing is blocking your main thread while executing the query in a different thread so there's no benefit of using a different thread to execute the query.
As for your database related error, how are you using the
db
parameter ? -
It's just a basic example to illustrate how to use the
QtConcurrent::run
with a method to call with the other parameters given.What you are currently doing is blocking your main thread while executing the query in a different thread so there's no benefit of using a different thread to execute the query.
As for your database related error, how are you using the
db
parameter ?@sgaist
i need the result from the background thread. then how can i not block and get the result of that thread and use it in main thread?
you mean in theupdateData()
function? i open the db, create aQSqlQuery query(db)
and then use the query. -
update:
okay i got it, future is blocking.
i looked at this example of async access to dbs: https://www.linuxjournal.com/article/9602
and changed my implementation to useQThread
in this fashion (inform about the result of background thread via signal/slot mechanism).
but i still don't see much difference than when i executed the update queries in the main thread. -
Adding threads to an application doesn't mean you are going to get a sudden boost in all use cases. If the method you move to the thread was already really fast, then you won't notice much.
-
Adding threads to an application doesn't mean you are going to get a sudden boost in all use cases. If the method you move to the thread was already really fast, then you won't notice much.
@sgaist
i don't think the method was fast. this is the method in short: i need to update three tables. the how part depends on whether a row in each table exists. so i do "if exists, update. else, insert" a few times in the same function.
by the way, should i perform the "is exists then update else insert" part in c++ code, or it should be in a query? I'd say in query because the operations might be optimized at database level. but currently I'm doing them in c++ codeactually, i don't need the method in thread to execute immediately. i just need the gui not to freeze
-
Indeed, let the database do that kind of stuff since it's made for that.
-
@sgaist
okay. and one thing i don't get is, the function that does the updating is called from a menu (action). now when the function is executed is executed in another thread I'd expect the menu to close, by it is not.here is another thing. the gui action calls a function, and in this function i instruct the thread to call the internal update function. should i do this? or call the first function initiated by the gui action from the new thread?
-
okay so o tried to call
qApp->processEvents()
in the loop that updates the db. and i got rid of the thread. what about this solution?for (auto row : rows) { // update the db for the table row // ... // process pending events QCoreApplication::processEvents(); }
-
@sgaist
okay. and one thing i don't get is, the function that does the updating is called from a menu (action). now when the function is executed is executed in another thread I'd expect the menu to close, by it is not.here is another thing. the gui action calls a function, and in this function i instruct the thread to call the internal update function. should i do this? or call the first function initiated by the gui action from the new thread?
@user4592357 said in executing query after cloned connection is used in another thread:
@sgaist
okay. and one thing i don't get is, the function that does the updating is called from a menu (action). now when the function is executed is executed in another thread I'd expect the menu to close, by it is not.here is another thing. the gui action calls a function, and in this function i instruct the thread to call the internal update function. should i do this? or call the first function initiated by the gui action from the new thread?
Since I don't know how you implemented the thread more the menu setup, I can't comment on that.
-
@user4592357 said in executing query after cloned connection is used in another thread:
@sgaist
okay. and one thing i don't get is, the function that does the updating is called from a menu (action). now when the function is executed is executed in another thread I'd expect the menu to close, by it is not.here is another thing. the gui action calls a function, and in this function i instruct the thread to call the internal update function. should i do this? or call the first function initiated by the gui action from the new thread?
Since I don't know how you implemented the thread more the menu setup, I can't comment on that.
@sgaist
okay and what about the process events solution? -
Using processEvents is a last resort mesure that should be avoided.
-
@sgaist
okay so it means i need to go back to my thread implementation
here's what i havebool doWork(const QString &newVal) { for (each of selected table rows) { if (!table->updateRow(row, newVal)) return false; // QCoreApplication::processEvents(); } return true; } bool Table::updateRow(int row, const QString &newVal) { m_thread.execute(updateQuery, row, newVal); //... }
when the processEvents is commented, the gui freezes, since the call to
updateRow
is executed in gui thread.
what should i do? put the for loop ofdoWork()
in the thread? (the table is generic and in my case i need to update the database but another table might do something else where multithreaded update is not needed... although it won't hurt maybe) -
How proficient are you in the threading field ?
Because from the looks of it, you are directly calling methods on your thread class and expecting them to actually run in that other thread. It's not how it works.
-
How proficient are you in the threading field ?
Because from the looks of it, you are directly calling methods on your thread class and expecting them to actually run in that other thread. It's not how it works.
@sgaist
sorry for no context. i create the database and then use slots to execute the queries (because i want to execute the queries on some event, which is when the user decided to modify the data in my table widget).
roughly speaking, this is my implementation:#pragma once #include <QThread> #include <QDebug> #include <QtSql/QSqlError> #include <QtSql/QSqlDatabase> #include <QtSql/QSqlQuery> class queryExecutor : public QObject { Q_OBJECT public: explicit queryExecutor(QObject *parent = nullptr) noexcept; void setDatabase(const QString &sDbPath); public slots: void execute(const QString &query, const QHash<QString, QString> &mapBoundVals); signals: void finished(bool success, const QList<QSqlRecord> &records); private: QSqlDatabase m_db; }; class QueryThread : public QThread { Q_OBJECT public: explicit QueryThread(const QString &sDbPath) noexcept; protected: void run() override; signals: void queryFinished(bool success, const QList<QSqlRecord> &records); public slots: void execute(const QString &query, const QHash<QString, QString> &mapBoundVals); private: queryExecutor m_queryExecutor; QString m_sDbPath; }; queryExecutor::queryExecutor(QObject *parent /* = nullptr */) noexcept { } void queryExecutor::setDatabase(const QString &sDbPath) { // create a thread-specific connection m_db = QSqlDatabase::addDatabase("QSQLITE", QString("%1_%2") .arg(sDbPath) .arg(QString::number((quint64)QThread::currentThreadId(), 16))); qDebug() << "conn name" << QString("%1_%2") .arg(sDbPath) .arg(QString::number((quint64)QThread::currentThreadId(), 16)); m_db.setDatabaseName(sDbPath); if (!m_db.open()) { qDebug() << "db not open:" << m_db.lastError().text(); } } void queryExecutor::execute(const QString &query, const QHash<QString, QString> &mapBoundVals) { QList<QSqlRecord> records; QSqlQuery sqlQuery(m_db); sqlQuery.prepare(query); for (const auto &sPlaceholder : mapBoundVals.keys()) { auto bound = mapBoundVals[sPlaceholder]; sqlQuery.bindValue(sPlaceholder, bound); } if (!sqlQuery.exec()) { qDebug() << "query failed:" << sqlQuery.lastError().text(); emit finished(false, records); return; } while (sqlQuery.next()) records.push_back(sqlQuery.record()); emit finished(true, records); } QueryThread::QueryThread(const QString &sDbPath) noexcept : m_sDbPath(sDbPath) { } void QueryThread::execute(const QString &query, const QHash<QString, QString> &mapBoundVals) { // forward the execution to the worker m_queryExecutor.execute(query, mapBoundVals); } void QueryThread::run() { qDebug() << "started thread" << QThread::currentThreadId(); m_queryExecutor.setDatabase(m_sDbPath); // forward final signal connect(&m_queryExecutor, &queryExecutor::finished, this, &QueryThread::queryFinished); exec(); }
-
Your function queryExecutor::execute() is not run in your thread since the slot from where it's called is a function from an object which lives in the main thread. I would suggest reading the QThread documentation on how to properly use Signals/Slots with QThread: https://doc.qt.io/qt-5/threads-qobject.html