Update QSqlTableModel after QSqlQuery executing

Wuzi

Hi,

I'm trying to add a column from an sql database with Qsql. With QSqlTableModel this is not possible and therefore I created a query to do that:
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_);
This works fine and when opening the database in a db browser, the column was added

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.

How do I update the View/ model after querying sql commands manually?

Wuzi

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

Christian Ehrlicher

You have to set a new query afaics.

Wuzi

What do you mean?

Christian Ehrlicher

@Wuzi said in Update QSqlTableModel after QSqlQuery executing:

What do you mean?

call QSqlTableModel::setQuery() again

JonB

@Wuzi
You could also try https://doc.qt.io/qt-5/qabstractitemmodel.html#beginResetModel, you'd have to verify whether that works.

Wuzi

@Christian-Ehrlicher
should I call setQuery every time I'm executing a query or it is enough to call it at the end? After calling, nothing is shown anymore

Wuzi

@Christian-Ehrlicher
I tried to call beginResetModel() and endResetModel() but it did not change anything. But this methods are for signaling the views, but I think the model is not updated

Christian Ehrlicher

@Wuzi I never said anything about begin/endResetModel() - you should not call it. When you call setQuery() with the original query the view should not be empty - please show some code.

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.

Wuzi

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

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);

JonB

This post is deleted!

JonB

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

SGaist

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.

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?

JonB

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

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

JonB

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

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.

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?