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
Forum Updated to NodeBB v4.3 + New Features

executing query after cloned connection is used in another thread

Scheduled Pinned Locked Moved Unsolved General and Desktop
26 Posts 3 Posters 3.4k Views 2 Watching
  • 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 20 Aug 2019, 18:56 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
    • S Offline
      S Offline
      SGaist
      Lifetime Qt Champion
      wrote on 20 Aug 2019, 18:58 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 20 Aug 2019, 19:03
      1
      • S SGaist
        20 Aug 2019, 18:58

        Hi,

        Are you using a SQLite database ?

        U Offline
        U Offline
        user4592357
        wrote on 20 Aug 2019, 19:03 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
        • S Offline
          S Offline
          SGaist
          Lifetime Qt Champion
          wrote on 20 Aug 2019, 21:05 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 20 Aug 2019, 21:11
          1
          • S SGaist
            20 Aug 2019, 21:05

            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 20 Aug 2019, 21:11 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
            • S Offline
              S Offline
              SGaist
              Lifetime Qt Champion
              wrote on 20 Aug 2019, 21:25 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 21 Aug 2019, 04:27
              1
              • S SGaist
                20 Aug 2019, 21:25

                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 21 Aug 2019, 04:27 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 21 Aug 2019, 19:12 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
                  • S Offline
                    S Offline
                    SGaist
                    Lifetime Qt Champion
                    wrote on 21 Aug 2019, 20:34 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 21 Aug 2019, 20:51
                    1
                    • S SGaist
                      21 Aug 2019, 20:34

                      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 21 Aug 2019, 20:51 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
                      • S Offline
                        S Offline
                        SGaist
                        Lifetime Qt Champion
                        wrote on 21 Aug 2019, 21:10 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 21 Aug 2019, 21:32
                        0
                        • S SGaist
                          21 Aug 2019, 21:10

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

                          U Offline
                          U Offline
                          user4592357
                          wrote on 21 Aug 2019, 21:32 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?

                          S 1 Reply Last reply 23 Aug 2019, 20:32
                          0
                          • U Offline
                            U Offline
                            user4592357
                            wrote on 22 Aug 2019, 08:46 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
                              21 Aug 2019, 21:32

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

                              S Offline
                              S Offline
                              SGaist
                              Lifetime Qt Champion
                              wrote on 23 Aug 2019, 20:32 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 23 Aug 2019, 20:36
                              0
                              • S SGaist
                                23 Aug 2019, 20:32

                                @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 23 Aug 2019, 20:36 last edited by
                                #15

                                @sgaist
                                okay and what about the process events solution?

                                1 Reply Last reply
                                0
                                • S Offline
                                  S Offline
                                  SGaist
                                  Lifetime Qt Champion
                                  wrote on 23 Aug 2019, 20:53 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 23 Aug 2019, 21:02
                                  0
                                  • S SGaist
                                    23 Aug 2019, 20:53

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

                                    U Offline
                                    U Offline
                                    user4592357
                                    wrote on 23 Aug 2019, 21:02 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
                                    • S Offline
                                      S Offline
                                      SGaist
                                      Lifetime Qt Champion
                                      wrote on 23 Aug 2019, 21:15 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 24 Aug 2019, 04:50
                                      0
                                      • S SGaist
                                        23 Aug 2019, 21:15

                                        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 24 Aug 2019, 04:50 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 Offline
                                          Christian EhrlicherC Offline
                                          Christian Ehrlicher
                                          Lifetime Qt Champion
                                          wrote on 24 Aug 2019, 13:40 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 24 Aug 2019, 14:45
                                          1

                                          7/26

                                          21 Aug 2019, 04:27

                                          topic:navigator.unread, 19
                                          • Login

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