QSqlTableModel fails to do submitAll()...
-
CMAKELists.txt
cmake_minimum_required(VERSION 3.5) project(SqlTableModelQuestion VERSION 0.1 LANGUAGES CXX) set(CMAKE_AUTOUIC ON) set(CMAKE_AUTOMOC ON) set(CMAKE_AUTORCC ON) set(CMAKE_CXX_STANDARD 17) set(CMAKE_CXX_STANDARD_REQUIRED ON) find_package(QT NAMES Qt6 Qt5 REQUIRED COMPONENTS Widgets) find_package(Qt${QT_VERSION_MAJOR} REQUIRED COMPONENTS Widgets Sql) set(PROJECT_SOURCES main.cpp MainWindow.cpp MainWindow.h MainWindow.ui ) if(${QT_VERSION_MAJOR} GREATER_EQUAL 6) qt_add_executable(SqlTableModelQuestion MANUAL_FINALIZATION ${PROJECT_SOURCES} ) # Define target properties for Android with Qt 6 as: # set_property(TARGET SqlTableModelQuestion APPEND PROPERTY QT_ANDROID_PACKAGE_SOURCE_DIR # ${CMAKE_CURRENT_SOURCE_DIR}/android) # For more information, see https://doc.qt.io/qt-6/qt-add-executable.html#target-creation else() if(ANDROID) add_library(SqlTableModelQuestion SHARED ${PROJECT_SOURCES} ) # Define properties for Android with Qt 5 after find_package() calls as: # set(ANDROID_PACKAGE_SOURCE_DIR "${CMAKE_CURRENT_SOURCE_DIR}/android") else() add_executable(SqlTableModelQuestion ${PROJECT_SOURCES} ) endif() endif() target_link_libraries(SqlTableModelQuestion PRIVATE Qt${QT_VERSION_MAJOR}::Widgets Qt${QT_VERSION_MAJOR}::Sql) # Qt for iOS sets MACOSX_BUNDLE_GUI_IDENTIFIER automatically since Qt 6.1. # If you are developing for iOS or macOS you should consider setting an # explicit, fixed bundle identifier manually though. if(${QT_VERSION} VERSION_LESS 6.1.0) set(BUNDLE_ID_OPTION MACOSX_BUNDLE_GUI_IDENTIFIER com.example.SqlTableModelQuestion) endif() set_target_properties(SqlTableModelQuestion PROPERTIES ${BUNDLE_ID_OPTION} MACOSX_BUNDLE_BUNDLE_VERSION ${PROJECT_VERSION} MACOSX_BUNDLE_SHORT_VERSION_STRING ${PROJECT_VERSION_MAJOR}.${PROJECT_VERSION_MINOR} MACOSX_BUNDLE TRUE WIN32_EXECUTABLE TRUE ) include(GNUInstallDirs) install(TARGETS SqlTableModelQuestion BUNDLE DESTINATION . LIBRARY DESTINATION ${CMAKE_INSTALL_LIBDIR} RUNTIME DESTINATION ${CMAKE_INSTALL_BINDIR} ) if(QT_VERSION_MAJOR EQUAL 6) qt_finalize_executable(SqlTableModelQuestion) endif()
MainWindow.h
#ifndef MAINWINDOW_H #define MAINWINDOW_H #include <QMainWindow> QT_BEGIN_NAMESPACE namespace Ui { class MainWindow; } QT_END_NAMESPACE class MainWindow : public QMainWindow { Q_OBJECT public: MainWindow(QWidget *parent = nullptr); ~MainWindow(); QString m_databasePath; void createNewItem(); void setMapModel(); private: Ui::MainWindow *ui; }; #endif // MAINWINDOW_H
MainWindow.cpp
#include "MainWindow.h" #include "./ui_MainWindow.h" #include <QSqlTableModel> #include <QSqlDatabase> #include <QSqlQuery> #include <QSqlError> #include <QStandardPaths> #include <QDir> #include <QMessageBox> MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent) , ui(new Ui::MainWindow) { m_databasePath = "dummy.sqlite3"; ui->setupUi(this); QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE"); m_db.setDatabaseName(m_databasePath); if (!m_db.open()) { qDebug() << 27; QMessageBox::warning(nullptr, "Map Data", QString("Database Error: %1").arg(m_db.lastError().text())); } QSqlQuery query(m_db); // query.exec("""DROP TABLE IF EXISTS mapdata"""); query.exec("""CREATE TABLE IF NOT EXISTS mapdata (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,\ name VARCHAR(40) NOT NULL,\ row INTEGER,\ col INTEGER,\ data BLOB,\ empty BLOB)"""); query.exec("SELECT COUNT(*) FROM mapdata"); query.next(); if (query.value(0) == 0) { query.prepare("INSERT OR IGNORE INTO mapdata (id, name, row, col, data, empty) VALUES (:id, :name, :row, :col, :data, :empty)"); query.bindValue(":name", QVariant(QString("worldmap"))); // at random... query.bindValue(":row", QVariant(32)); query.bindValue(":col", QVariant(32)); QByteArray qb; QDataStream ds(&qb, QIODevice::WriteOnly); query.bindValue(":data", QByteArray()); } if (!query.exec()) { qDebug() << "Insert failed:" << query.lastError().text(); } connect(ui->pushButton, &QPushButton::clicked, this, &MainWindow::createNewItem); setMapModel(); } MainWindow::~MainWindow() { delete ui; } void MainWindow::setMapModel() { QSqlTableModel* mapDataTableModel = new QSqlTableModel(); ui->tableView->setModel(mapDataTableModel); mapDataTableModel->setHeaderData(0, Qt::Horizontal, QVariant("ID")); mapDataTableModel->setHeaderData(1, Qt::Horizontal, QVariant("Name")); mapDataTableModel->setHeaderData(2, Qt::Horizontal, QVariant("Row")); mapDataTableModel->setHeaderData(3, Qt::Horizontal, QVariant("Column")); mapDataTableModel->setHeaderData(4, Qt::Horizontal, QVariant("Data")); mapDataTableModel->setHeaderData(5, Qt::Horizontal, QVariant("Load")); mapDataTableModel->setTable("mapdata"); mapDataTableModel->setEditStrategy(QSqlTableModel::EditStrategy::OnManualSubmit); bool canSelect = mapDataTableModel->select(); ui->tableView->setColumnHidden(0, true); ui->tableView->setColumnHidden(4, true); if (canSelect) { mapDataTableModel->submitAll(); } } void MainWindow::createNewItem() { QAbstractItemModel* aim = ui->tableView->model(); QSqlTableModel* m = qobject_cast<QSqlTableModel*>(aim); QSqlQuery query(m->database()); if (!m->database().open()) { QMessageBox::warning(nullptr, "Map Data", QString("Database Error: %1").arg(m->database().lastError().text())); } if (query.exec("SELECT COUNT(*) FROM mapdata")) { if (query.next()) { if (query.value(0).toInt() > 0) { // query.prepare("INSERT OR IGNORE INTO mapdata (id, name, row, col, data, empty) VALUES (:id, :name, :row, :col, :data, :empty)"); // query.bindValue(":name", QVariant(QString("test"))); // // at random... // query.bindValue(":row", QVariant(32)); // query.bindValue(":col", QVariant(32)); // query.bindValue(":data", QByteArray()); // query.bindValue(":empty", QByteArray()); // query.exec(); qDebug() << "db is open?" << m->database().isOpen(); if (!m->insertRows(m->rowCount(), 1)) { qDebug() << "insertRows failed"; } else { // m->select(); in this case, submitAll success. But not inserted... if (m->submitAll()) { m->database().commit(); qDebug() << "submit is success"; } else { qDebug() << "submit is not success"; } } } } } }
MainWindow.ui
<?xml version="1.0" encoding="UTF-8"?> <ui version="4.0"> <class>MainWindow</class> <widget class="QMainWindow" name="MainWindow"> <property name="geometry"> <rect> <x>0</x> <y>0</y> <width>800</width> <height>600</height> </rect> </property> <property name="windowTitle"> <string>MainWindow</string> </property> <widget class="QWidget" name="centralwidget"> <layout class="QVBoxLayout" name="verticalLayout"> <item> <widget class="QPushButton" name="pushButton"> <property name="text"> <string>New</string> </property> </widget> </item> <item> <widget class="QTableView" name="tableView"/> </item> </layout> </widget> <widget class="QMenuBar" name="menubar"> <property name="geometry"> <rect> <x>0</x> <y>0</y> <width>800</width> <height>22</height> </rect> </property> </widget> <widget class="QStatusBar" name="statusbar"/> </widget> <resources/> <connections/> </ui>
Probably, my fundamental knowledge is lack... Please tell me why QSqlTableModel fails to do submitAll()...
-
Hi,
What about printing the error value since it's failing ?
-
You don't set any values in your new row but 'name' is defined as 'Not Null'
-
This is the goal of the method.
-
Since you are using a QSqlTableModel you should ask for the error there...
-
@Haru
Not sure we will get too far by trying to over-analyze the text of the message. I would start by setting all the fields of the inserted record (e.g. viaQSqlRecord record = model.record(row); record.setValue(...); model.setRecord(row, record);
) before submitting it. You show a commented out section where you set values and did your ownINSERT
query. Did that work? Set the values in your inserted record similarly. -
@JonB
About - "You show a commented out section where you set values and did your own INSERT query."
I wrote this code (now, comment out) because I wanted to check whether the database can insert a new line.
If I don't drop table and reexecute my code, there is the newly inserted item.
I thought I should have called submitAll function after that, but the result was the same ...
That is to say, I must close my application once, and reboot.And about "I would start by setting all the fields of the inserted record (e.g. via QSqlRecord record = model.record(row); record.setValue(...); model.setRecord(row, record);) before submitting it. "
I don't know what to do... This means I should set the values by using QSqlRecord again?