QSqlTableModel

Xilit

Hi guys!

I wrote some code to show my info from Sqlite db table in tableView. But something went wrong. ~~Unfortunatelly as usual.~~ So to be short this is my code.

dialog.h

#include <QDialog>
#include <QtSql>
#include <QSqlDatabase>
#include <QMessageBox>
#include <QSqlTableModel>

private:
    Ui::Dialog *ui;
    QSqlDatabase db;
    QSqlTableModel *sqlTableModel;

dialog.cpp

//creating DB connection
    QString path = "CountriesDB/countries.sqlite3";
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(path);

    QFileInfo checkDBFile(path);
    if(checkDBFile.isFile())
    {
        if(db.open())
        {
            sqlTableModel = new QSqlTableModel();
            sqlTableModel->setTable("countries");
            sqlTableModel->select();
            ui->tableView->setModel(sqlTableModel);
        }
        else{
            QMessageBox::critical(this,"Error","Connection Failed!");
        }
    }

My programm launch successfully but shows nothing. DB is not empty (I've double checked it). Count on your help. As usual.)

jsulm

@Xilit said in QSqlTableModel:

QString path = "CountriesDB/countries.sqlite3";

You're using relative file path: are you sure it is found?
Also, you did not say what exactly happens? Does checkDBFile.isFile() return true/false? Does db.open() return true?

VRonin

As a side note, you should never have a member variable of type QSqlDatabase

From https://doc.qt.io/Qt-5/qsqldatabase.html

Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

Xilit

@jsulm
Thank you! I added some check in my code and change path to absolute:

//Absolute path
    QString path = "E:/Qt/build-Countries-Desktop_Qt_5_12_5_MinGW_64_bit-Debug/debug/CountriesDB/countries.sqlite3";
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(path);

    QFileInfo checkDBFile(path);
    if(checkDBFile.isFile())
    {
        //Check 1
        QMessageBox::information(this,"Info","DB is File!");
        if(db.open())
        {
            //Check 2
            QMessageBox::information(this,"Info","DB is opened!");
            sqlTableModel = new QSqlTableModel();
            sqlTableModel->setTable("countries");
            sqlTableModel->select();
            ui->tableView->setModel(sqlTableModel);
        }
        else{
            QMessageBox::critical(this,"Error","Connection Failed!");
        }
    }else{
        //Check 3
        QMessageBox::critical(this,"Error","DB is Not a File!");
    }

You were right, when I add check 3 I've got DB is Not a File! When I replace path to absolute I've got two messages DB is File! and DB is opened! but still nothing in
my tableView.(((

@VRonin
Thank you! Do you mean that I should change this

db = QSqlDatabase::addDatabase("QSQLITE");

on this?

db.addDatabase("QSQLITE");

VRonin

You are leaking sqlTableModel
sqlTableModel->select(); returns a boolean you can use to check if it worked or not
"Do you mean that I should change this on this?" No, you should remove QSqlDatabase db; from dialog.h and add auto before db = QSqlDatabase::addDatabase("QSQLITE");

Xilit

@VRonin
sqlTableModel->select(); returns a boolean you can use to check if it worked or not

Hmmm... It is really doesn't work. It returns false. So what can I do with is? There is nothing in docs about it. It just says:

returns true if successful; otherwise returns false.

But how can I fix it? What went wrong?

Chrisw01

@Xilit Take a look at lastError(); to see if there is any errors available.

From the Docs:

[virtual]void QSqlTableModel::setTable(const QString &tableName)
Sets the database table on which the model operates to tableName. Does not select data from the table, but fetches its field information.

To populate the model with the table's data, call select().

Error information can be retrieved with lastError().

Thanks..

Xilit

@Chrisw01

Thank you!

db.lastError().text() is empty string, db.isValid() returns true.

The problem is here:

if(sqlTableModel->select())
            {
                QMessageBox::information(this,"Info","Selected!");
            }else{
                QMessageBox::critical(this,"Error","Failed!");
            }

And I always get "Failed!". Why? It seems that my DB is valid, it is file, it is opened but it can't select data from database?

SGaist

Hi,

Check the lastError of your QSqlTableModel.

Xilit

@SGaist

Thank you! I've already checked that.(

Problem solved! I've got updated table with another table name. So I just update information in .cpp and all is working now! Thank you all for helping me out!

BTW, are there any possibility to display double number without exponential format in QSqlTableModel? I can use something like this QString::number(result, 'f', 2), but how can I select all date from colomn "Population"? Should I use SqlQuery / .next() instead of this

            sqlTableModel->select();
            ui->tableView->setModel(sqlTableModel);

Thank you!

JonB

@Xilit
Yes, use number format to select desired format!

You already have all rows read in. model->data(model->index(row, column)) gives you the column data in a row, e.g. sqlTableModel->data(sqlTableModel->index(0, 1)), sqlTableModel->data(sqlTableModel->index(1, 1)) for the two items in your picture.

Xilit

@JonB

Hi! Glad to see you!

I've tried this

QModelIndex index = sqlTableModel->index(0,3);
sqlTableModel->setData(index, QString::number(sqlTableModel->data(index).toDouble(), 'f', 3));
ui->tableView->setModel(sqlTableModel);

But it nothing change. It still display in exponential format.

JonB

@Xilit
Don't do this via setData(). You don't want to set/change any data, only the way it is displayed.

(BTW, my guess here is that if you looked at the return result of your setData() [which you ought always do] you might see yours is failing, trying to store a string in a table column known to be a number.)

Sub-class from Qt-supplied QSqlTableModel. Override the data() method, and have it return the desired conversion to formatted string in whatever columns are numeric to be displayed like that. Do so for the (default) DisplayRole case only.

That's how I believe in doing it. I think there are some who would do the conversion/display in a QStyledItemDelegate instead.

In general, you should only need to set the view's model once at the start, not each time a value changes. However if you change as above you won't be changing any values anyway.

Xilit

@JonB

Thank you! I'll try to do that.

I think there are some who would do the conversion/display in a QStyledItemDelegate instead.

You know, when I write previous comment I thought about using QStyledItemDelegate . If you say so maybe I realy should use it.

In general, you should only need to set the view's model once at the start, not each time a value changes. However if you change as above you won't be changing any values anyway.

So if I want to change some values I need to derived from abstractModel and override methods, right? Ok, if I derived from QAbstractTableModel and reimplement rowCount(), columnCount(), data(), setData() and flags() in my countriestablemodel.cpp and pass this model to mainwindow.cpp to show. And to mainwindow.cpp I also give QStyledItemDelegate from countriesstyleditemdelegate.cpp.

Something like this:

countriestablemodel.cpp -> mainwindow.cpp (provide model with data from DB)
countriesstyleditemdelegate.cpp -> mainwindow.cpp (provide delegate to model)

So, model with data and delegate to this model must "meet" each other in mainwindow.cpp. Is this scheme correct? If you have any suggesions it will be appreciated.

Thank you!

JonB

@Xilit
Yes, you can do something like this.

As I said, personally I do not do this in a QStyledItemDelegate at all. I suggested you do it in data() override for Qt::DisplayRole. Personally I think this is simpler to write, but perhaps that's opinion.

Rationale: QStyledItemDelegate is only usable in a QTableView or similar. However, to me the choice to display a numeric in a particular format like you have is not restricted to/has nothing to do with whether you happen to be displaying it as an item in table. If, say, you want to display it in its own widget, or export it to a text file, you would (presumably) still want to display/output it in your chosen format. So I see it best as an attribute of the data, putting the logic in data(Qt::DisplayRole) makes that available/shareable everywhere.

Xilit

@JonB

Thank you! I'll try it out.)

Xilit

BTW is there are recommendation / tutorial how to reimplement QVariant QAbstractItemModel::data(const QModelIndex &index, int role = Qt::DisplayRole) const? To know how to reimplement method I need to know how this method works but I don't. Were I can find realization of it? There are no information about it in docs.

JonB

@Xilit
No mention about it in the docs?? Oh, I think you mean you have found the reference page (https://doc.qt.io/qt-5/qabstractitemmodel.html#data), but not examples? One is https://doc.qt.io/qt-5/model-view-programming.html#a-read-only-example-model, another is https://doc.qt.io/qt-5/model-view-programming.html#read-only-access. Read around those two areas. There are perhaps better links, I don't know. Understanding how data() & setData() methods work and can be overridden is important for Qt models.

SGaist

Did you follow the Model View Programming Guide ?
There are several examples linked with some of them implementing custom models and views.

VRonin

@JonB said in QSqlTableModel:

I suggested you do it in data() override for Qt::DisplayRole. Personally I think this is simpler to write, but perhaps that's opinion.

Strongly disagree. The model is data, it shouldn't care how the data is represented. Also, the model is locale unaware

Just subclass QStyledItemDelegate and override displayText:

class NoScNotationDelegate : public QStyledItemDelegate{
Q_OBJECT
Q_DISABLE_COPY(NoScNotationDelegate)
public:
using QStyledItemDelegate::QStyledItemDelegate;
QString displayText(const QVariant &value, const QLocale &locale) const override{
switch(value.type()){
case QMetaType::Double:
return locale.toString(value.toDouble(),'f');
case QMetaType::Float:
return locale.toString(value.toFloat(),'f');
default:
return QStyledItemDelegate::displayText(value,locale);
}
}
};

Then you can just use it with tableView->setItemDelegate(new NoScNotationDelegate(tableView)); (you can also use setItemDelegateForColumn to apply it ta a single column)