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. Update QSqlTableModel after QSqlQuery executing
Forum Updated to NodeBB v4.3 + New Features

Update QSqlTableModel after QSqlQuery executing

Scheduled Pinned Locked Moved Solved General and Desktop
22 Posts 4 Posters 2.7k Views 1 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.
  • JonBJ Offline
    JonBJ Offline
    JonB
    wrote on last edited by
    #9

    It was I who suggested trying beginResetModel(), I did say you would need to verify. Evidently that is not correct, it was a suggestion which was wrong.

    W 1 Reply Last reply
    0
    • JonBJ JonB

      It was I who suggested trying beginResetModel(), I did say you would need to verify. Evidently that is not correct, it was a suggestion which was wrong.

      W Offline
      W Offline
      Wuzi
      wrote on last edited by
      #10

      @JonB
      Sorry for the wrong quote. I just wrote it down to track that this was tested :)

      1 Reply Last reply
      0
      • W Offline
        W Offline
        Wuzi
        wrote on last edited by
        #11

        @Christian-Ehrlicher
        Here the code. I can also publish the complete code if needed, but I think it is too much. You can find it here

        class SqlTableModel : public QSqlTableModel
        {
            Q_OBJECT
        public:
            SqlTableModel(QObject *parent = nullptr, QSqlDatabase db = QSqlDatabase());
            void setQuery(QSqlQuery& query);
            void reset();
        };
        
        void SqlTableModel::setQuery(QSqlQuery &query) {
            QSqlTableModel::setQuery(query);
        }
        
                QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                QSqlQuery query("", *m_db);
                bool status = query.exec(query_);
        
                if (!status) {
                    QString err = query.lastError().text();
                    //emit errorOccured(err);
                }
                m_model->setQuery(query);
        
        JonBJ SGaistS 3 Replies Last reply
        0
        • W Wuzi

          @Christian-Ehrlicher
          Here the code. I can also publish the complete code if needed, but I think it is too much. You can find it here

          class SqlTableModel : public QSqlTableModel
          {
              Q_OBJECT
          public:
              SqlTableModel(QObject *parent = nullptr, QSqlDatabase db = QSqlDatabase());
              void setQuery(QSqlQuery& query);
              void reset();
          };
          
          void SqlTableModel::setQuery(QSqlQuery &query) {
              QSqlTableModel::setQuery(query);
          }
          
                  QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                  QSqlQuery query("", *m_db);
                  bool status = query.exec(query_);
          
                  if (!status) {
                      QString err = query.lastError().text();
                      //emit errorOccured(err);
                  }
                  m_model->setQuery(query);
          
          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #12
          This post is deleted!
          1 Reply Last reply
          0
          • W Wuzi

            @Christian-Ehrlicher
            Here the code. I can also publish the complete code if needed, but I think it is too much. You can find it here

            class SqlTableModel : public QSqlTableModel
            {
                Q_OBJECT
            public:
                SqlTableModel(QObject *parent = nullptr, QSqlDatabase db = QSqlDatabase());
                void setQuery(QSqlQuery& query);
                void reset();
            };
            
            void SqlTableModel::setQuery(QSqlQuery &query) {
                QSqlTableModel::setQuery(query);
            }
            
                    QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                    QSqlQuery query("", *m_db);
                    bool status = query.exec(query_);
            
                    if (!status) {
                        QString err = query.lastError().text();
                        //emit errorOccured(err);
                    }
                    m_model->setQuery(query);
            
            JonBJ Offline
            JonBJ Offline
            JonB
            wrote on last edited by JonB
            #13

            @Wuzi

            After calling, nothing is shown anymore

                    QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                    QSqlQuery query("", *m_db);
                    bool status = query.exec(query_);
                    m_model->setQuery(query);
            

            At the end of this, I wonder what the model gets as its query for populating from the table? Are you expecting it to discover that the table's columns have changed?

            1 Reply Last reply
            0
            • W Wuzi

              @Christian-Ehrlicher
              Here the code. I can also publish the complete code if needed, but I think it is too much. You can find it here

              class SqlTableModel : public QSqlTableModel
              {
                  Q_OBJECT
              public:
                  SqlTableModel(QObject *parent = nullptr, QSqlDatabase db = QSqlDatabase());
                  void setQuery(QSqlQuery& query);
                  void reset();
              };
              
              void SqlTableModel::setQuery(QSqlQuery &query) {
                  QSqlTableModel::setQuery(query);
              }
              
                      QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                      QSqlQuery query("", *m_db);
                      bool status = query.exec(query_);
              
                      if (!status) {
                          QString err = query.lastError().text();
                          //emit errorOccured(err);
                      }
                      m_model->setQuery(query);
              
              SGaistS Offline
              SGaistS Offline
              SGaist
              Lifetime Qt Champion
              wrote on last edited by SGaist
              #14

              Hi,

              @Wuzi said in Update QSqlTableModel after QSqlQuery executing:

              QSqlQuery query("", *m_db);

              Two things here looks wrong:

              • why do you have a pointer to QSqlDatabase object ?
              • why do you have a QSqlDatabase member variable ? There's a warning about that in the QSqlDatabase documentation.

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

              1 Reply Last reply
              1
              • W Offline
                W Offline
                Wuzi
                wrote on last edited by
                #15

                @JonB said in Update QSqlTableModel after QSqlQuery executing:

                At the end of this, I wonder what the model gets as its query for populating from the table? Are you expecting it to discover that the table's columns have changed?

                Yes I expect that the model resets and adds the column. So thats wrong?

                @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                why do you have a pointer to QSqlDatabase object ?
                Because I stored a pointer to the database, otherwise I have multiple objects on the same database?

                @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                why do you have a QSqlDatabase member variable ? There's a warning about that in the QSqlDatabase documentation.
                The link is broken.

                What is the best way to update the model and the view?

                JonBJ SGaistS 2 Replies Last reply
                0
                • W Wuzi

                  @JonB said in Update QSqlTableModel after QSqlQuery executing:

                  At the end of this, I wonder what the model gets as its query for populating from the table? Are you expecting it to discover that the table's columns have changed?

                  Yes I expect that the model resets and adds the column. So thats wrong?

                  @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                  why do you have a pointer to QSqlDatabase object ?
                  Because I stored a pointer to the database, otherwise I have multiple objects on the same database?

                  @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                  why do you have a QSqlDatabase member variable ? There's a warning about that in the QSqlDatabase documentation.
                  The link is broken.

                  What is the best way to update the model and the view?

                  JonBJ Offline
                  JonBJ Offline
                  JonB
                  wrote on last edited by
                  #16

                  @Wuzi said in Update QSqlTableModel after QSqlQuery executing:

                  Yes I expect that the model resets and adds the column. So thats wrong?

                  Let's start with clarity of what you typed:

                  I'm trying to remove a column from an sql database with Qsql.

                  Here the code
                  QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3")

                  So you're trying to remove a column by adding a column? It would really help if there was some consistency between the question you ask and the code you show....

                  Earlier you said:

                  The problem now is, that the view is not updated. I already tried

                  QSqlTableModel::select() to repopulate the model, but the removed columns still occur.

                  I suspect/wonder that the model does not get updated after column add/remove, rather than the view? I don't know what you think will cause the model to see the changed columns. It should be easy for you discover whether the issue lies at model side or view. You could check the query from QSqlTableModel::query(), or you could sub-class QSqlTableModel (IMHO always advisable) so that you can check QSqlTableModel::selectStatement().

                  1 Reply Last reply
                  1
                  • W Offline
                    W Offline
                    Wuzi
                    wrote on last edited by Wuzi
                    #17

                    @JonB said in Update QSqlTableModel after QSqlQuery executing:

                    So you're trying to remove a column by adding a column? It would really help if there was some consistency between the question you ask and the code you show....

                    Sorry for that. The problem is the same for adding a column, but in sqlite removing column does not exist and therefore multiple queries must be executed. To make it easier, I took Add instead Remove.
                    I updated the question to get consistence

                    JonBJ 1 Reply Last reply
                    0
                    • W Wuzi

                      @JonB said in Update QSqlTableModel after QSqlQuery executing:

                      So you're trying to remove a column by adding a column? It would really help if there was some consistency between the question you ask and the code you show....

                      Sorry for that. The problem is the same for adding a column, but in sqlite removing column does not exist and therefore multiple queries must be executed. To make it easier, I took Add instead Remove.
                      I updated the question to get consistence

                      JonBJ Offline
                      JonBJ Offline
                      JonB
                      wrote on last edited by
                      #18

                      @Wuzi
                      That's OK, I'm often irritable with the way people write questions ;-) Check out the later stuff in my post, I think you need to narrow down for sure what is going on with your table model query after you alter columns before you know whether you are looking at a model or view issue.

                      1 Reply Last reply
                      0
                      • W Wuzi

                        @JonB said in Update QSqlTableModel after QSqlQuery executing:

                        At the end of this, I wonder what the model gets as its query for populating from the table? Are you expecting it to discover that the table's columns have changed?

                        Yes I expect that the model resets and adds the column. So thats wrong?

                        @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                        why do you have a pointer to QSqlDatabase object ?
                        Because I stored a pointer to the database, otherwise I have multiple objects on the same database?

                        @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                        why do you have a QSqlDatabase member variable ? There's a warning about that in the QSqlDatabase documentation.
                        The link is broken.

                        What is the best way to update the model and the view?

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

                        @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                        why do you have a pointer to QSqlDatabase object ?
                        Because I stored a pointer to the database, otherwise I have multiple objects on the same database?

                        QSqlDatabase manages the connections. When needed ask it for the connection you want to use.

                        @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                        why do you have a QSqlDatabase member variable ? There's a warning about that in the QSqlDatabase documentation.
                        The link is broken.

                        Link fixed.

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

                        W 1 Reply Last reply
                        1
                        • SGaistS SGaist

                          @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                          why do you have a pointer to QSqlDatabase object ?
                          Because I stored a pointer to the database, otherwise I have multiple objects on the same database?

                          QSqlDatabase manages the connections. When needed ask it for the connection you want to use.

                          @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                          why do you have a QSqlDatabase member variable ? There's a warning about that in the QSqlDatabase documentation.
                          The link is broken.

                          Link fixed.

                          W Offline
                          W Offline
                          Wuzi
                          wrote on last edited by Wuzi
                          #20

                          @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                          QSqlDatabase manages the connections. When needed ask it for the connection you want to use.
                          Ah ok thanks.

                          @JonB said in Update QSqlTableModel after QSqlQuery executing:

                          I suspect/wonder that the model does not get updated after column add/remove, rather than the view? I don't know what you think will cause the model to see the changed columns. It should be easy for you discover whether the issue lies at model side or view. You could check the query from QSqlTableModel::query(), or you could sub-class QSqlTableModel (IMHO always advisable) so that you can check QSqlTableModel::selectStatement().

                          Ok I don't see there my new column.

                              void redo() override {
                                  QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                                  QSqlQuery query("", *m_db);
                                  bool status = query.exec(query_);
                          
                                  if (!status) {
                                      QString err = query.lastError().text();
                                      //emit errorOccured(err);
                                  }
                                  query.finish();
                                  m_model->selectStatement(); // I don't see the new column here
                                  m_model->setQuery(query);
                                  m_model->selectStatement(); // The selectStatement is empty
                                  m_model->setTable(m_model->tableName());
                                  m_model->selectStatement(); // query is fine and the new column is shown in the table view
                              }
                          

                          So the question is, why in the second selectStatement() call the query is empty?
                          The idea is now to update the query of the model every time I'm executing a new query?

                          JonBJ 1 Reply Last reply
                          0
                          • W Wuzi

                            @SGaist said in Update QSqlTableModel after QSqlQuery executing:

                            QSqlDatabase manages the connections. When needed ask it for the connection you want to use.
                            Ah ok thanks.

                            @JonB said in Update QSqlTableModel after QSqlQuery executing:

                            I suspect/wonder that the model does not get updated after column add/remove, rather than the view? I don't know what you think will cause the model to see the changed columns. It should be easy for you discover whether the issue lies at model side or view. You could check the query from QSqlTableModel::query(), or you could sub-class QSqlTableModel (IMHO always advisable) so that you can check QSqlTableModel::selectStatement().

                            Ok I don't see there my new column.

                                void redo() override {
                                    QString query_ = QString("ALTER TABLE %1 ADD COLUMN %2 %3").arg(m_table).arg(m_name).arg(m_type);
                                    QSqlQuery query("", *m_db);
                                    bool status = query.exec(query_);
                            
                                    if (!status) {
                                        QString err = query.lastError().text();
                                        //emit errorOccured(err);
                                    }
                                    query.finish();
                                    m_model->selectStatement(); // I don't see the new column here
                                    m_model->setQuery(query);
                                    m_model->selectStatement(); // The selectStatement is empty
                                    m_model->setTable(m_model->tableName());
                                    m_model->selectStatement(); // query is fine and the new column is shown in the table view
                                }
                            

                            So the question is, why in the second selectStatement() call the query is empty?
                            The idea is now to update the query of the model every time I'm executing a new query?

                            JonBJ Offline
                            JonBJ Offline
                            JonB
                            wrote on last edited by JonB
                            #21

                            @Wuzi said in Update QSqlTableModel after QSqlQuery executing:

                                m_model->selectStatement(); // I don't see the new column here
                                m_model->setQuery(query);
                                m_model->selectStatement(); // The selectStatement is empty
                                //m_model->setTable(m_model->tableName());
                                m_model->selectStatement(); // query is fine and the new column is shown in the table view
                            

                            I simply do not believe the "query is fine" in the third selectStatement() you show here. I can believe it would be fine if you uncommented the setTable() statement, but that is not what you show. Nonetheless is that what you mean??

                            The idea is now to update the query of the model every time I'm executing a new query?

                            Not for every query, but yes for the very unusual case where you add/remove/alter columns. You have to tell QSqlTableModel to re-read the table definition, and setTable() should do that.

                            Finally, I cannot imagine why you persist in m_model->setQuery(query);. Your query is an ALTER TABLE statement. that is totally unsuitable as the statement for reading data from a table, which should be a SELECT statement. You don't need to set that (setTable() will sort it out), but don't set it to some completely unrelated statement.

                            W 1 Reply Last reply
                            1
                            • JonBJ JonB

                              @Wuzi said in Update QSqlTableModel after QSqlQuery executing:

                                  m_model->selectStatement(); // I don't see the new column here
                                  m_model->setQuery(query);
                                  m_model->selectStatement(); // The selectStatement is empty
                                  //m_model->setTable(m_model->tableName());
                                  m_model->selectStatement(); // query is fine and the new column is shown in the table view
                              

                              I simply do not believe the "query is fine" in the third selectStatement() you show here. I can believe it would be fine if you uncommented the setTable() statement, but that is not what you show. Nonetheless is that what you mean??

                              The idea is now to update the query of the model every time I'm executing a new query?

                              Not for every query, but yes for the very unusual case where you add/remove/alter columns. You have to tell QSqlTableModel to re-read the table definition, and setTable() should do that.

                              Finally, I cannot imagine why you persist in m_model->setQuery(query);. Your query is an ALTER TABLE statement. that is totally unsuitable as the statement for reading data from a table, which should be a SELECT statement. You don't need to set that (setTable() will sort it out), but don't set it to some completely unrelated statement.

                              W Offline
                              W Offline
                              Wuzi
                              wrote on last edited by Wuzi
                              #22

                              @JonB said in Update QSqlTableModel after QSqlQuery executing:

                              I simply do not believe the "query is fine" in the third selectStatement() you show here. I can believe it would be fine if you uncommented the setTable() statement, but that is not what you show.

                              @JonB said in Update QSqlTableModel after QSqlQuery executing:

                              Finally, I cannot imagine why you persist in m_model->setQuery(query);. Your query is an ALTER TABLE statement. that is totally unsuitable

                              You are right. SELECT is the correct one

                              Sorry. I played around to check and forgott to enable it again. I edited the code above

                              Yes I think setTable and does the job. I thought there is a different way doing it.

                              Thank you all for helping!

                              1 Reply Last reply
                              0

                              • Login

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