Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. executing query after cloned connection is used in another thread
QtWS25 Last Chance

executing query after cloned connection is used in another thread

Scheduled Pinned Locked Moved Unsolved General and Desktop
26 Posts 3 Posters 3.3k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • U Offline
    U Offline
    user4592357
    wrote on last edited by
    #1

    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?

    1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      Hi,

      Are you using a SQLite database ?

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      U 1 Reply Last reply
      1
      • SGaistS SGaist

        Hi,

        Are you using a SQLite database ?

        U Offline
        U Offline
        user4592357
        wrote on last edited by user4592357
        #3

        @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 within reexecuteQuery();

        1 Reply Last reply
        0
        • SGaistS Offline
          SGaistS Offline
          SGaist
          Lifetime Qt Champion
          wrote on last edited by
          #4

          Why are you using QtConcurrent::run if you use waitForFinished ?
          As for the parameter count mismatch, did you check that your query is correct ?

          Interested in AI ? www.idiap.ch
          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

          U 1 Reply Last reply
          1
          • SGaistS SGaist

            Why are you using QtConcurrent::run if you use waitForFinished ?
            As for the parameter count mismatch, did you check that your query is correct ?

            U Offline
            U Offline
            user4592357
            wrote on last edited by
            #5

            @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

            1 Reply Last reply
            0
            • SGaistS Offline
              SGaistS Offline
              SGaist
              Lifetime Qt Champion
              wrote on last edited by
              #6

              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 ?

              Interested in AI ? www.idiap.ch
              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

              U 1 Reply Last reply
              1
              • SGaistS SGaist

                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 ?

                U Offline
                U Offline
                user4592357
                wrote on last edited by
                #7

                @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 the updateData() function? i open the db, create a QSqlQuery query(db) and then use the query.

                1 Reply Last reply
                0
                • U Offline
                  U Offline
                  user4592357
                  wrote on last edited by
                  #8

                  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 use QThread 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.

                  1 Reply Last reply
                  0
                  • SGaistS Offline
                    SGaistS Offline
                    SGaist
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

                    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.

                    Interested in AI ? www.idiap.ch
                    Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                    U 1 Reply Last reply
                    1
                    • SGaistS SGaist

                      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.

                      U Offline
                      U Offline
                      user4592357
                      wrote on last edited by user4592357
                      #10

                      @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++ code

                      actually, i don't need the method in thread to execute immediately. i just need the gui not to freeze

                      1 Reply Last reply
                      0
                      • SGaistS Offline
                        SGaistS Offline
                        SGaist
                        Lifetime Qt Champion
                        wrote on last edited by
                        #11

                        Indeed, let the database do that kind of stuff since it's made for that.

                        Interested in AI ? www.idiap.ch
                        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                        U 1 Reply Last reply
                        0
                        • SGaistS SGaist

                          Indeed, let the database do that kind of stuff since it's made for that.

                          U Offline
                          U Offline
                          user4592357
                          wrote on last edited by
                          #12

                          @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?

                          SGaistS 1 Reply Last reply
                          0
                          • U Offline
                            U Offline
                            user4592357
                            wrote on last edited by user4592357
                            #13

                            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();
                            }
                            
                            1 Reply Last reply
                            0
                            • U user4592357

                              @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?

                              SGaistS Offline
                              SGaistS Offline
                              SGaist
                              Lifetime Qt Champion
                              wrote on last edited by
                              #14

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

                              Interested in AI ? www.idiap.ch
                              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                              U 1 Reply Last reply
                              0
                              • SGaistS SGaist

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

                                U Offline
                                U Offline
                                user4592357
                                wrote on last edited by
                                #15

                                @sgaist
                                okay and what about the process events solution?

                                1 Reply Last reply
                                0
                                • SGaistS Offline
                                  SGaistS Offline
                                  SGaist
                                  Lifetime Qt Champion
                                  wrote on last edited by
                                  #16

                                  Using processEvents is a last resort mesure that should be avoided.

                                  Interested in AI ? www.idiap.ch
                                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                                  U 1 Reply Last reply
                                  0
                                  • SGaistS SGaist

                                    Using processEvents is a last resort mesure that should be avoided.

                                    U Offline
                                    U Offline
                                    user4592357
                                    wrote on last edited by user4592357
                                    #17

                                    @sgaist
                                    okay so it means i need to go back to my thread implementation
                                    here's what i have

                                    bool 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 of doWork() 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)

                                    1 Reply Last reply
                                    0
                                    • SGaistS Offline
                                      SGaistS Offline
                                      SGaist
                                      Lifetime Qt Champion
                                      wrote on last edited by
                                      #18

                                      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.

                                      Interested in AI ? www.idiap.ch
                                      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                                      U 1 Reply Last reply
                                      0
                                      • SGaistS SGaist

                                        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.

                                        U Offline
                                        U Offline
                                        user4592357
                                        wrote on last edited by user4592357
                                        #19

                                        @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();
                                        }
                                        
                                        1 Reply Last reply
                                        0
                                        • Christian EhrlicherC Online
                                          Christian EhrlicherC Online
                                          Christian Ehrlicher
                                          Lifetime Qt Champion
                                          wrote on last edited by
                                          #20

                                          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

                                          Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                                          Visit the Qt Academy at https://academy.qt.io/catalog

                                          U 1 Reply Last reply
                                          1

                                          • Login

                                          • Login or register to search.
                                          • First post
                                            Last post
                                          0
                                          • Categories
                                          • Recent
                                          • Tags
                                          • Popular
                                          • Users
                                          • Groups
                                          • Search
                                          • Get Qt Extensions
                                          • Unsolved