title: Qt SQL Threaded Database Queries
body: <p>I'm building a Qt based application to monitor and capture data streaming from a serial port. The data is plotted real-time, sent out via TCP, and stored into an SQLite database. Unfortunately, I've found that the SQLite insertions causes the GUI to become unresponsive, as I am performing serial data handling, plotting, TCP transmission, and database insertion all in the context of the main loop. I researched seperating the database insertions onto another thread and came up with the following code. </p>
  
  <pre><code> #include &lt;QObject&gt;
  #include &lt;QDebug&gt;
  #include &lt;QStringList&gt;
  #include &lt;QSqlDatabase&gt;
  #include &lt;QSqlQuery&gt;
  #include &lt;QSqlError&gt;
  #include &lt;QVariant&gt;
  #include &lt;QObject&gt;
  #include &lt;QList&gt;
  #include &lt;QThread&gt;
  #include &lt;QMutex&gt;
  #include &lt;QWaitCondition&gt;
  #include &lt;QSqlDatabase&gt;
  #include &lt;QSqlRecord&gt;
  #include &lt;QString&gt;
  #include "mMessage.h"
  // The class that does all the work with the database. This class will
  // be instantiated in the thread object's run() method.
  class Worker : public QObject
  {
    Q_OBJECT
     public:
      Worker( QObject* parent = 0);
      ~Worker();
      bool insertADC(mMessage* insertMessage);
    public slots:
      void slotExecute( mMessage* insertMessage );
    signals:
      void queryResult(bool);
     private:
       QSqlDatabase m_database;
       bool m_databaseOpen;
       void prepareQueries();
       QSqlQuery *m_accelerometerQuery;
       QSqlQuery *m_adcQuery;
       QSqlQuery *m_metricsQuery;
       QSqlQuery *m_rssiQuery;
  };
  
  class mDatabaseThread : public QThread
  {
    Q_OBJECT
    public:
      mDatabaseThread(QObject *parent = 0);
      ~mDatabaseThread();
      void executeInsertion( mMessage* insertMessage );
    signals:
      void progress( const QString&amp; msg );
  void ready(bool);
    protected:
      void run();
    signals:
      void executefwd( mMessage* insertMessage );
    private:
       Worker* m_worker;
  };
  </code></pre>
  
  <p><strong>CPP FILE</strong></p>
  
  <pre><code>    #include "mDatabaseThread.h"
      //
  
      Worker::Worker( QObject* parent )
          : QObject( parent )
      {
          // thread-specific connection, see db.h
          m_database = QSqlDatabase::addDatabase( "QSQLITE",
                                                  "WorkerDatabase" ); // named connection
          m_database.setDatabaseName("trainingX.db3");
          if ( !m_database.open() )
          {
              qWarning() &lt;&lt; "Unable to connect to database, giving up:" &lt;&lt; m_database.lastError().text();
              m_databaseOpen = false;
              return;
          }
       m_databaseOpen = true;
  
      }
  
      Worker::~Worker()
      {
          //close the database
         // m_database.close();
         // m_database.removeDatabase("trainingX.db3");
      }
      void Worker::prepareQueries()
      {
          if (m_databaseOpen)
          {
          m_accelerometerQuery-&gt;prepare("INSERT INTO accelerometer (key, timestamp, nickname, unitid,"
                                     "sectorid, acc_x, acc_y, acc_z) "
                                     "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                                     ":acc_x, :acc_y, :acc_z)");
  
          m_adcQuery-&gt;prepare("INSERT INTO adc (key, timestamp, nickname, unitid, sectorid,"
                           "adc0, adc1, adc2, adc3, adc4, adc5, adc6, adc7) "
                           "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                           ":adc0, :adc1, :adc2, :adc3, :adc4, :adc5, :adc6, :adc7)");
  
          m_metricsQuery-&gt;prepare("INSERT INTO metrics (key, timestamp, nickname, unitid, sectorid, "
                               "acc_temp, unit_temp, unit_pressure, bpm_instant, bpm_average, base_pressure, base_temp) "
                               "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                               ":acc_temp, :unit_temp, :unit_pressure, :bpm_instant, :bpm_average, :base_pressure, :base_temp)");
  
          m_rssiQuery-&gt;prepare("INSERT INTO rssi (key, timestamp, nickname, unitid, sectorid, "
                            "rssi_1, rssi_2, rssi_3, rssi_4, rssi_avg) "
                            "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                            ":rssi_1, :rssi_2, :rssi_3, :rssi_4, :rssi_avg)");
          }
      }
  
      void Worker::slotExecute( mMessage* insertMessage )
      {
          m_accelerometerQuery = new QSqlQuery("WorkerDatabase");
          m_adcQuery = new QSqlQuery("WorkerDatabase");
          m_metricsQuery = new QSqlQuery("WorkerDatabase");
          m_rssiQuery = new QSqlQuery("WorkerDatabase");
  
          prepareQueries();
  
          insertADC(insertMessage);
          //insertRSSI(insertMessage);
          //insertAccelerometer(insertMessage);
          //insertMetrics(insertMessage);
          emit queryResult( true );
      }
  bool Worker::insertADC(mMessage *insertMessage)
  {
      if (m_databaseOpen)
      {
             // m_adcQuery-&gt;bindValue(":key",0);
              m_adcQuery-&gt;bindValue(":timestamp",insertMessage-&gt;m_timestamp);
              m_adcQuery-&gt;bindValue(":nickname",insertMessage-&gt;m_nickname);
              m_adcQuery-&gt;bindValue(":unitid",insertMessage-&gt;m_unitId.toInt());
              m_adcQuery-&gt;bindValue(":sectorid",insertMessage-&gt;m_sectorId.toInt());
              m_adcQuery-&gt;bindValue(":adc0",insertMessage-&gt;m_adc0.toInt());
              m_adcQuery-&gt;bindValue(":adc1",insertMessage-&gt;m_adc1.toInt());
              m_adcQuery-&gt;bindValue(":adc2",insertMessage-&gt;m_adc2.toInt());
              m_adcQuery-&gt;bindValue(":adc3",insertMessage-&gt;m_adc3.toInt());
              m_adcQuery-&gt;bindValue(":adc4",insertMessage-&gt;m_adc4.toInt());
              m_adcQuery-&gt;bindValue(":adc5",insertMessage-&gt;m_adc5.toInt());
              m_adcQuery-&gt;bindValue(":adc6",insertMessage-&gt;m_adc6.toInt());
              m_adcQuery-&gt;bindValue(":adc7",insertMessage-&gt;m_adc7.toInt());
              if (m_adcQuery-&gt;exec())
              {
                  return true;
              }
              else
              {
                  qDebug() &lt;&lt; "SQL ADC failed.";
                  qDebug() &lt;&lt; m_adcQuery-&gt;lastError();
                  return false;
              }
      }
      else
      {
      //database isn't open
      return false;
      }
  }
  ////database thread
  
  mDatabaseThread::mDatabaseThread(QObject *parent)
      : QThread(parent)
  {
  }
  mDatabaseThread::~mDatabaseThread()
  {
      delete m_worker;
  }
  
  void mDatabaseThread::executeInsertion( mMessage* insertMessage )
  {
      emit executefwd( insertMessage ); // forwards to the worker
  }
  
  void mDatabaseThread::run()
  {
      emit ready(false);
       // Create worker object within the context of the new thread
      m_worker = new Worker();
      connect( this, SIGNAL( executefwd( mMessage* ) ),
               m_worker, SLOT( slotExecute( mMessage* ) ) );
      connect( m_worker, SIGNAL(queryResult(bool)), this, SIGNAL(ready(bool)));
      emit ready(true);
  
      exec();  // our event loop
  }
  </code></pre>
  
  <p>The mDatabaseThread class has a worker object which it sends the database work to. The signals and slots are all functioning and get fired correctly. However, the actual QSqlQuery fails complaining that the database is not open - but when I attempt to debug it, I see m_database is actually set to the right file/parameters. It is based on a solution here <a href="http://www.linuxjournal.com/article/9602" rel="noreferrer">http://www.linuxjournal.com/article/9602</a>. The mMessage class is a qObject which also gets passed to the m_worker object correctly. </p>
  
  <p>From the main class, I call m_databaseThread->start() in the constructor and then I call the executeInsertion(mMessage*) function. I've tried to change the way m_database is initialized as well as the QSqlQueries but it seems no matter what I do, it complains that the QSqlQuery cannot find the database connection.</p>