Editing data in table view (row grater than 256) QSqlTableModel

nadamus

Hi,
I have some problems with editing data in tableview (QSqlTableModel). It is working OK until I have less than 256 records. If row is grater than 256, when cell editing is finished, all data in rows above no.256 dissipater leaving them empty. After scrolling records all the way up and down data appear back, but additional empty rows still remain. Each data edit increases number of empty rows. Underlying database is updated correctly.
Resetting view does not improving things for me.
Thanks in advance for your help.
Regards

JonB

@nadamus
At some level, this will be to do with the fact that the rows are read in batches of size maximum 256 in the QSqlTableModel Qt code. IIRC that number is hard-coded. You must look at calling https://doc.qt.io/qt-5/qsqlquerymodel.html#fetchMore in a loop as necessary if you want to ensure you have all read all records returned from a query/in a table.

nadamus

@JonB
Thanks for reply.
I tried to load all data using fetchMore() method, prior to data edit. I tried also to run fetchMore after cell edit. I connected dataChanged signal with my slot resetting view and loading all data. Result was the same.

JonB

@nadamus
Then show your code, because with the number being 256 I'm pretty sure this is the reason. And btw if you are inserting (or deleting) rows you will not get the dataChanged signal.

nadamus

I'm pretty sure that 256 is the reason. :)
I'm not referring to adding or removing rows which I have to check later. Now I'm focusing on editing data (double mouse click and edit). This emits dataChanged signal for sure. I'm using it for some data checks.

Some explanation. My table is showing sets of points with numbers, 3D coordinates and point type code. Only two columns are editable (point number) and Point code.
In my subclass I have re-implemented two methods:

data - for display coordinates decimal precission (4 digits) and change code from int to particular code name

flags - for setting particular columns editable

I did not rimperilment setData method - since it was working OK until this grater than 256 rows issue. Maybe this is a reason for described behavior?

This is my QSqlTableModel subclass:
PointTableModel.h

#ifndef POINTTABLEMODEL_H
#define POINTTABLEMODEL_H

#define REGULARTYPE QString("Regular")
#define FACETYPE QString("Face")
#define FLANGEFRONTTYPE QString("Flange front")
#define FLANGEBACKTYPE QString("Flange back")
#define BOLTTYPE QString("Bolt")
#define FLANGEFRONTFLOWERTYPE QString("Flange front-flower")
#define FLANGEBACKFLOWERTYPE QString("Flange back-flower")
#define MASTERGRIDTYPE QString("MasterGrid")
#define PIPEDIAMETERTYPE QString("Pipe diameter")
#define AUTO_CADTYPE QString("AutoCad")
#define UNSPECIFIEDTYPE QString("Unspecified")

#include <QDebug>
#include <QObject>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlTableModel>
#include <QComboBox>

class PointTableModel : public QSqlTableModel
{
public:


    PointTableModel(QSqlDatabase polaczenie);
    PointTableModel(QList<int>editList,int kolKodow);

    QVariant data ( const QModelIndex & index, int role = Qt::EditRole ) const override;
    Qt::ItemFlags flags(const QModelIndex & indeks) const override;
    //bool setData(const QModelIndex &index, const QVariant &value, int role) override;

private:
    QList<int> kolumnyEdytowalne;//editable collumns
    QList<int> kolumnyNumeryczne;//numeric collumns
    int kolumnaKodow;//codes column


};

PointTableMode.cpp

#include "pointtablemodel.h"

PointTableModel::PointTableModel(QSqlDatabase polaczenie)
    :QSqlTableModel (NULL,polaczenie)
{
    kolumnaKodow=6;
    kolumnyEdytowalne={2,6};//editable collumns
    kolumnyNumeryczne={3,4,5};//numeric collumns
    setEditStrategy(QSqlTableModel::OnManualSubmit);
    //setEditStrategy(QSqlTableModel::OnFieldChange);
}

PointTableModel::PointTableModel(QList<int>editList,int kolKodow)
{
    kolumnaKodow=kolKodow;//codes collmns
    kolumnyEdytowalne=editList;//editable collumns
}

QVariant PointTableModel::data(const QModelIndex &indeks, int rola) const
{
    QVariant wartosc= QSqlTableModel::data(indeks,rola);
    double wart;
    if(rola==Qt::DisplayRole && kolumnyNumeryczne.indexOf(indeks.column())!=-1){
      wart=wartosc.toDouble();
      return QString::number(wart,'f',4);
    }
    if( indeks.column()==kolumnaKodow){
        bool ok;
        int typPunktu=wartosc.toInt(&ok);
        if(ok){
            switch (typPunktu){
            case 0:
                return QVariant(REGULARTYPE);
            case 1:
                return QVariant(FACETYPE);
            case 2:
                return QVariant(FLANGEFRONTTYPE);
            case 3:
                return QVariant(FLANGEBACKTYPE);
            case 4:
                return QVariant(BOLTTYPE);
            case 5:
                return QVariant(FLANGEFRONTFLOWERTYPE);
            case 6:
                return QVariant(FLANGEBACKFLOWERTYPE);
            case 7:
                return QVariant(MASTERGRIDTYPE);
            case 8:
                return QVariant(PIPEDIAMETERTYPE);
            case 9:
                return QVariant(AUTO_CADTYPE);
            default:
                return QVariant(UNSPECIFIEDTYPE);
            }
        }
    }
    return QSqlTableModel::data(indeks,rola);
}

Qt::ItemFlags PointTableModel::flags(const QModelIndex &indeks) const
{
    Qt::ItemFlags flagi=QSqlTableModel::flags(indeks);
    flagi.setFlag(Qt::ItemIsEditable,false);
    int iterator=0;
    int max=kolumnyEdytowalne.size();
    bool znalazl=false;
    while(iterator<max && !znalazl){
        if(indeks.column()==kolumnyEdytowalne[iterator]){
            znalazl=true;
        }
        iterator++;
    }
    if(znalazl){
        return flagi|Qt::ItemIsEditable;
    }else{
        return flagi;
    }
}

New model creation method.

PointTableModel* DbConnection::GetTabelaPktStanowiska(int indeksStanowiska)const
{
    PointTableModel* nowymodelPtr=NULL;
    QString zapytanie;
    if(SprawdzPolaczenie()){
        zapytanie=QString("stanowisko_Id=%1").arg(indeksStanowiska);//
        nowymodelPtr=new PointTableModel(polaczenieGlowne);
        if(nowymodelPtr) {
            nowymodelPtr->setTable(TBL_PKT);
            nowymodelPtr->setFilter(zapytanie);
            nowymodelPtr->select();
        }
        return  nowymodelPtr;
    }else{
        throw int(53);
    }
    
}

Then I'm setting this model to tableVeiw in mainWindow class
Regards

JonB

@nadamus
Your code shows no call at all to fetchMore(). You said:

I tried to load all data using fetchMore() method, prior to data edit. I tried also to run fetchMore after cell edit.

Since fetchMore() is going to be required to deal with the 256 rows, I don't see how the large amount of code you posted will address that? I was expecting just a couple of lines of your code, showing how & where you call fetchMore().

nadamus

@JonB
I created slot in attempt of deal with issue, but there was no change in table behavior. That's why I skipped it here. It was just long shot.
it looks like that:
Connection:

ui->tableView->setModel(modelTabeliPunktowPtr);
connect(modelTabeliPunktowPtr,SIGNAL(dataChanged(const QModelIndex&,const QModelIndex&)),this,SLOT(SrpawdzPolaczeniaPunktu(const QModelIndex &)));
connect(modelTabeliPunktowPtr,SIGNAL(dataChanged(const QModelIndex&,const QModelIndex&)),this,SLOT(AktualizujTabele(const QModelIndex&,const QModelIndex& )));

slot:

void MainWindow::AktualizujTabele(const QModelIndex& indeksJeden,const QModelIndex& indeksDwa)
{

    if(modelTabeliPunktowPtr){
        ui->tableView->reset();
        modelTabeliPunktowPtr->rowCount();
        while(modelTabeliPunktowPtr->canFetchMore()){
            modelTabeliPunktowPtr->fetchMore();
        }
    }
    ui->tableView->scrollTo(indeksJeden);
}

nadamus

OK
I have managed to deal with issue. It is more walk around than decent way to do it, but it works for now.

void MainWindow::AktualizujTabele(const QModelIndex& indeksJeden,const QModelIndex& indeksDwa)
{
    int wiersz=indeksJeden.row();
    if(wiersz>256){
        if(modelTabeliPunktowPtr){
            ui->tableView->setModel(modelTabeliPunktowPtr);//reassigning model
            while(modelTabeliPunktowPtr->canFetchMore()){
                modelTabeliPunktowPtr->fetchMore();
            }
            if(wiersz>modelTabeliPunktowPtr->rowCount()){
                wiersz=modelTabeliPunktowPtr->rowCount();
            }
            ui->tableView->selectRow(wiersz);
            ui->tableView->scrollTo(ui->tableView->currentIndex());
        }
    }
}

JonB

@nadamus
If you have something which now works, and the reason is the 256 rows and the fetchMore() calls I said you will need, that is good for you.

I will just say that your code should have no reference to the 256 (your if(wiersz>256)) as that could change at any time and there is no need to rely/hard-code it. Nor certainly should you be "reassigning model" anywhere as you do, that is wrong and slow. I don't know where you call your function from, but it may not be the correct/best place to be doing the fetchMore() from.

However, I leave this to you now.