QSqlTableModel fails to do submitAll()...

[Haru]

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()...

[SGaist]

Hi,

What about printing the error value since it's failing ?

[Christian Ehrlicher]

You don't set any values in your new row but 'name' is defined as 'Not Null'

[Haru]

Christian Ehrlicher>>>

I retried to do after deleting NOT NULL constraint... But the same result...

SGaist>>>
Error value?
Can I get that by lastError()?

[SGaist]

This is the goal of the method.

[Haru]

                if (m->submitAll())
                {
                    m->database().commit();
                    qDebug() << "submit is success";
                }
                else
                {
                    qDebug() << m->database().lastError().text();
                }

the result is

empty text...

[Christian Ehrlicher]

Since you are using a QSqlTableModel you should ask for the error there...

[Haru]

That is to say,

else
{
qDebug() << m->database().lastError().text();
qDebug() << m->lastError();
}

No Fields to update,,, ?

[JonB]

@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. via QSqlRecord 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 own INSERT query. Did that work? Set the values in your inserted record similarly.

[Haru]

@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?