I want to show SQL data from a local db-File in a QML-Tableview and than would like to do some edits to the sql-database.
What I managed to do after about three weeks: Showing my data in a QML-Tableview. I am not sure if I really need to subclass QSqlTableModel and I definitly would be glad if subclassing would not be neccessary at all.
In my main.cpp following should create my model and directly add a record.
// Create an instance of the SqlModel for accessing the data
SqlDataModel *sqlModel;
sqlModel = new SqlDataModel(0,base.database());
sqlModel->setTable("diaBaneDatabase");
sqlModel->setSort(0, Qt::AscendingOrder);
sqlModel->setEditStrategy(QSqlTableModel::OnFieldChange);
sqlModel->select();
QSqlRecord record(sqlModel->record());
record.setValue(0,50);
record.setValue(3,222);
sqlModel->insertRecord(-1, record);
sqlModel->submitAll();
This should add 222 to the 4th column. But nothing will be stored in my sqlDatabase
My SqlDataModel::setData loolks like this:
bool SqlDataModel::setData(const QModelIndex &index, const QVariant &value, int role)
{
qDebug() << index.column() << " " << index.row() << " " << value << " ---- " << role;
qDebug() << roles[Qt::UserRole + 1];
//qDebug() << QSqlTableModel::setData(modelIndex, value);
qDebug() << QSqlQueryModel::setData(index, value);
return false;
}
The output will be:
0 39 QVariant(int, 50) ---- 2
"id"
false
1 39 QVariant(QString, "") ---- 2
"id"
false
2 39 QVariant(QString, "") ---- 2
"id"
false
3 39 QVariant(int, 222) ---- 2
"id"
false
4 39 QVariant(double, 0) ---- 2
"id"
false
5 39 QVariant(int, 0) ---- 2
"id"
false
6 39 QVariant(double, 0) ---- 2
"id"
false
7 39 QVariant(double, 0) ---- 2
"id"
false
For sure my setData method is wrong but I don't understand what should happen there and I didn't find any example for this.
Am I wrong with my assumption that I need to subclass QSqlTableModel to be able to put the model through QQmlContext to QML and than show the columns with roles named like my column-namings? If not how could I put the content of column 1 to QMLTableview:
TableViewColumn {
role: "id" // what should be the role if I don't subclass???
title: "ID"
width: 80
}
I'm happy for any help, comment, example, other posts or whatever brings me further forward ... thanks
I've been working on an example.
Some notes:
- QML items such as
TableView require a model so I think a QSqlTableModel is a very good option. Of course, you have other models that could be used to handle items of data.
- In the class
MySqlTableModel you will see the required role names. MySqlTableModel reimplements roleNames() to expose the role names, so that they can be accessed via QML.
- You could reimplement the
setData method in MySqlTableModel, but I think it is better to use the method insertRecord provided by QSqlTableModel.
I hope this example will help you to fix your errors.
main.cpp
#include <QApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>
#include "mysqltablemodel.h"
int main(int argc, char *argv[])
{
QApplication app(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("mydb");
if(!db.open()) {
qDebug() << db.lastError().text();
return 0;
}
QSqlQuery query(db);
if(!query.exec("DROP TABLE IF EXISTS mytable")) {
qDebug() << "create table error: " << query.lastError().text();
return 0;
}
if(!query.exec("CREATE TABLE IF NOT EXISTS mytable \
(id integer primary key autoincrement, name varchar(15), salary integer)")) {
qDebug() << "create table error: " << query.lastError().text();
return 0;
}
MySqlTableModel *model = new MySqlTableModel(0, db);
model->setTable("mytable");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();
QSqlRecord rec = model->record();
rec.setValue(1, "peter");
rec.setValue(2, 100);
model->insertRecord(-1, rec);
rec.setValue(1, "luke");
rec.setValue(2, 200);
model->insertRecord(-1, rec);
if(model->submitAll()) {
model->database().commit();
} else {
model->database().rollback();
qDebug() << "database error: " << model->lastError().text();
}
QQmlApplicationEngine engine;
QQmlContext *ctxt = engine.rootContext();
ctxt->setContextProperty("myModel", model);
engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
return app.exec();
}
mysqltablemodel.h
#ifndef MYSQLTABLEMODEL_H
#define MYSQLTABLEMODEL_H
#include <QSqlTableModel>
#include <QSqlRecord>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>
class MySqlTableModel : public QSqlTableModel
{
Q_OBJECT
public:
MySqlTableModel(QObject *parent = 0, QSqlDatabase db = QSqlDatabase());
Q_INVOKABLE QVariant data(const QModelIndex &index, int role=Qt::DisplayRole ) const;
Q_INVOKABLE void addItem(const QString &name, const QString &salary);
protected:
QHash<int, QByteArray> roleNames() const;
private:
QHash<int, QByteArray> roles;
};
#endif // MYSQLTABLEMODEL_H
mysqltablemodel.cpp
#include "mysqltablemodel.h"
MySqlTableModel::MySqlTableModel(QObject *parent, QSqlDatabase db): QSqlTableModel(parent, db) {}
QVariant MySqlTableModel::data ( const QModelIndex & index, int role ) const
{
if(index.row() >= rowCount()) {
return QString("");
}
if(role < Qt::UserRole) {
return QSqlQueryModel::data(index, role);
}
else {
return QSqlQueryModel::data(this->index(index.row(), role - Qt::UserRole), Qt::DisplayRole);
}
}
QHash<int, QByteArray> MySqlTableModel::roleNames() const
{
QHash<int, QByteArray> roles;
roles[Qt::UserRole + 1] = "name";
roles[Qt::UserRole + 2] = "salary";
return roles;
}
void MySqlTableModel::addItem(const QString &name, const QString &salary)
{
QSqlRecord rec = this->record();
rec.setValue(1, name);
rec.setValue(2, salary.toInt());
this->insertRecord(-1, rec);
if(this->submitAll()) {
this->database().commit();
} else {
this->database().rollback();
qDebug() << "database error: " << this->lastError().text();
}
}
main.qml
import QtQuick 2.4
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
ApplicationWindow {
title: qsTr("Hello World")
width: 640
height: 480
visible: true
TableView {
id: tableView
anchors.fill: parent
TableViewColumn {
role: "name"
title: "Name"
width: 200
}
TableViewColumn {
role: "salary"
title: "Salary"
width: 200
}
model: myModel
}
Button {
id: addButton
anchors.verticalCenter: parent.verticalCenter
text: "Add item"
onClicked: {
if (nameBox.text || salaryBox.text) {
myModel.addItem(nameBox.text, salaryBox.text)
}
}
}
TextField {
id: nameBox
placeholderText: "name"
anchors.verticalCenter: parent.verticalCenter
anchors.left: addButton.right
anchors.leftMargin: 5
}
TextField {
id: salaryBox
placeholderText: "salary"
anchors.verticalCenter: parent.verticalCenter
anchors.left: nameBox.right
anchors.leftMargin: 5
}
}
