QSqlTableModel and possible bug with setData?

[sylvainb]

I'm using QtSql (sqlite database in memory) and I need to remove the first column from my tables. Unfortunately, when I do this and I attempt to change a value using setData() I get a "QSqlQuery::value: not positioned on a valid record" error yet I'm able to retreive the current valus using a call to data() using the exact same QModelIndex. Has anyobody ever seen this problem or have a work around? Below is an example which show the problem. To remove the first column, you just need to run the compiled binary with at least one argument:

#include <QtGui>
#include <QtSql>
#include <QWidget>
#include <QDebug>
#include <iostream>
 
 
 
 
 int main(int argc, char *args[])
 {
    QApplication app(argc, args);
 
    // Create an im memory database
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
    db.setDatabaseName( ":memory:" );
    if ( !db.open() )
    {
        qCritical( "ERROR in opening the internal database" );
    }
 
    // Create a table in the database
    QSqlQuery query = db.exec(QString("create table test (id INTEGER PRIMARY KEY AUTOINCREMENT)"));
    // Add a column in the table using a query
    query.exec("ALTER TABLE test ADD col2 VARCHAR(50);");
    query.exec("ALTER TABLE test ADD col3 VARCHAR(50);");
    // Add a row to the column
    query.exec("INSERT INTO test ('col2','col3') VALUES ('something','last');");
 
 
 
    bool removeFirstColumn = argc > 1;
 
    // Create a table model for the table view
    QSqlTableModel *model= new QSqlTableModel;
    model->setTable("test");
    model->setEditStrategy(QSqlTableModel::OnFieldChange);
    if (removeFirstColumn) {
        model->removeColumns(0,1);
    }
    model->select();
    // Cteate a table view and display it
    QTableView table;
    table.setModel(model);
    table.show();
 
 
 
    int row = 0;
    int col ;
    if (removeFirstColumn) {
        col = 1 ;
    } else {
        col = 2;
    }
    QModelIndex index = model->index(row, col);
    //NOTE: current value should be "last", this works
    qDebug() << "value before change at row=" << row << "col=" << col << model->data( index ).toString();
 
 
    //Attempt to change the value
    QVariant newValue = "new value";
    model->setData( index, newValue);
    qDebug() << "value after change at row=" << row << "col=" << col << model->data( index ).toString();
 
    return app.exec();
 }

#include <QtGui>
#include <QtSql>
#include <QWidget>
#include <QDebug>
#include <iostream>




 int main(int argc, char *args[])
 {
    QApplication app(argc, args);

    // Create an im memory database
    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
    db.setDatabaseName( ":memory:" );
    if ( !db.open() )
    {
        qCritical( "ERROR in opening the internal database" );
    }

    // Create a table in the database
    QSqlQuery query = db.exec(QString("create table test (id INTEGER PRIMARY KEY AUTOINCREMENT)"));
    // Add a column in the table using a query
    query.exec("ALTER TABLE test ADD col2 VARCHAR(50);");
    query.exec("ALTER TABLE test ADD col3 VARCHAR(50);");
    // Add a row to the column
    query.exec("INSERT INTO test ('col2','col3') VALUES ('something','last');");



    bool removeFirstColumn = argc > 1;

    // Create a table model for the table view
    QSqlTableModel *model= new QSqlTableModel;
    model->setTable("test");
    model->setEditStrategy(QSqlTableModel::OnFieldChange);
    if (removeFirstColumn) {
        model->removeColumns(0,1);
    }
    model->select();
    // Cteate a table view and display it
    QTableView table;
    table.setModel(model);
    table.show();



    int row = 0;
    int col ;
    if (removeFirstColumn) {
        col = 1 ;
    } else {
        col = 2;
    }
    QModelIndex index = model->index(row, col);
    //NOTE: current value should be "last", this works
    qDebug() << "value before change at row=" << row << "col=" << col << model->data( index ).toString();


    //Attempt to change the value
    QVariant newValue = "new value";
    model->setData( index, newValue);
    qDebug() << "value after change at row=" << row << "col=" << col << model->data( index ).toString();

    return app.exec();
 }

To copy to clipboard, switch view to plain text mode 

Below is my output:
C:\Qt\examples\sql>sql remove
value before change at row= 0 col= 1 "last"
QSqlQuery::value: not positioned on a valid record
value after change at row= 0 col= 1 "last"

[wysota]

Use a filter proxy model to remove the column. The SQL table model is probably not designed to do such things. Besides removeColumns() from QSqlTableModel would probably delete the column from the database and that's probably not what you want.

[sylvainb]

I think your suggestion will work. As for the model deleting the column from the table, I haven't run into this problem because sqlite doesn't support removing columns from tables. That's probably why removing the column from the model worked for me until I wanted to set the data.

Thanks a lot for your help!