Odd behaviors when submitting rows with NOT NULL constraints

Mark81

I have the following SQlite3 table schema:

CREATE TABLE bouquets         
(
    variant TEXT NOT NULL,          
    name TEXT NOT NULL,          
    capacity INT NOT NULL,          
    PRIMARY KEY (name)
);

here a minimal reproducible example, cutting down the obvious:

mainwindow.h

private slots:
    void on_pushAdd_clicked();

private:
    QSqlTableModel *model;

mainwindow.cpp

MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", DB_NAME);
    db.setDatabaseName(DB_FILENAME);
    db.open();

    model = new QSqlTableModel(this, db);
    model->setTable(TABLE_NAME);
    model->setEditStrategy(QSqlTableModel::OnRowChange);
    model->select();

    ui->tableView->setModel(model);
    ui->tableView->setItemDelegate(new QStyledItemDelegate(ui->tableView));

    connect(model, &QSqlTableModel::beforeInsert, this, [=](QSqlRecord &record)
    {
        switch (model->lastError().type())
        {
        case QSqlError::ConnectionError: qWarning() << "Insert connection error" << model->lastError().text(); break;
        case QSqlError::StatementError:  qWarning() << "Insert statement error" << model->lastError().text(); break;
        case QSqlError::TransactionError: qWarning() << "Insert transaction error" << model->lastError().text(); break;
        default: break;
        }
    });
    
    connect(model, &QSqlTableModel::beforeUpdate, this, [=](int row, QSqlRecord &record)
    {
        switch (model->lastError().type())
        {
        case QSqlError::ConnectionError: qWarning() << "Update connection error" << model->lastError().text(); break;
        case QSqlError::StatementError: qWarning() << "Update statement error" << model->lastError().text(); break;
        case QSqlError::TransactionError: qWarning() << "Update transaction error" << model->lastError().text(); break;
        default: break;
        }
   });
}

void MainWindow::on_btnAddRecord_clicked()
{
    model->insertRow(model->rowCount());
    QModelIndex index = model->index(model->rowCount() - 1, 0);
    if (!index.isValid()) return;
    ui->tableView->setCurrentIndex(index);
    ui->tableView->edit(index);
}

It just populates a QTableView using a QSqlTableModel from a SQlite database.
When the user presses the QPushButton a new record is appended to the table.

Here the steps to reproduce the issue:

click the button to add a new record
fill only the first column ("variant"), press enter to close the editor and click on another row to submit
as expected the row is not submitted and this error if fired:

"Insert connection error NOT NULL constraint failed: bouquets.name Unable to fetch row"

now go back to the row, fill the "name" column and press enter. The error is emitted for the third time (the second one was when you went back to the row, but this is ok). No matter what, you cannot submit this row even if you populate the "name" column!

The expected behavior should be: once fulfilled the NOT NULL constraint on the "name" column it should scream about the "capacity" column instead.

Now that we have the first two columns filled:

enter something in the last column ("capacity") and press enter

It now fires this other error:

Insert connection error NOT NULL constraint failed: bouquets.capacity Unable to fetch row"

It complains about a missing value (while it is there) and it throws an error about inserting the row to the database, but the row is actually stored into the db!

Surely I'm missing something in the error handling. In my real application I connected these errors to a message box to inform the user and it's a pain, because they don't seem to reflect the actual situation.

Can you please help me to understand how to handle this simple scenario?

SGaist

Hi,

Which version of Qt is it ?
Can you please provide a complete minimal example that shows this ?

Mark81

@SGaist Qt 6.8.2
The example above is complete.
I just left out the #include directives and the usual definitions. Often I read in other threads you guys suggest to avoid the obvious code. Anyway, no problem, I post here all the stuff.

mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlTableModel>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>

QT_BEGIN_NAMESPACE
namespace Ui {
class MainWindow;
}
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_pushAdd_clicked();

private:
    Ui::MainWindow *ui;
    QSqlTableModel *model;
};
#endif // MAINWINDOW_H

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QStyledItemDelegate>

MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", DB_NAME);
    db.setDatabaseName(DB_FILE);
    db.open();

    model = new QSqlTableModel(this, db);
    model->setTable(TABLE_NAME);
    model->setEditStrategy(QSqlTableModel::OnRowChange);
    model->select();

    ui->tableView->setModel(model);
    ui->tableView->setItemDelegate(new QStyledItemDelegate(ui->tableView));

    connect(model, &QSqlTableModel::beforeInsert, this, [=](QSqlRecord &record)
    {
        switch (model->lastError().type())
        {
        case QSqlError::ConnectionError: qWarning() << "Insert connection error" << model->lastError().text(); break;
        case QSqlError::StatementError: qWarning() << "Insert statement error" << model->lastError().text(); break;
        case QSqlError::TransactionError: qWarning() << "Insert transaction error" << model->lastError().text(); break;
        default: break;
        }
    });
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_pushAdd_clicked()
{
    model->insertRow(model->rowCount());
    QModelIndex index = model->index(model->rowCount() - 1, 0);
    if (!index.isValid()) return;
    ui->tableView->setCurrentIndex(index);
    ui->tableView->edit(index);
}

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">
   <widget class="QTableView" name="tableView">
    <property name="geometry">
     <rect>
      <x>40</x>
      <y>40</y>
      <width>321</width>
      <height>381</height>
     </rect>
    </property>
   </widget>
   <widget class="QPushButton" name="btnAdd">
    <property name="geometry">
     <rect>
      <x>400</x>
      <y>40</y>
      <width>88</width>
      <height>25</height>
     </rect>
    </property>
    <property name="text">
     <string>PushButton</string>
    </property>
   </widget>
  </widget>
 <resources/>
 <connections/>
</ui>

Christian Ehrlicher

Please minimze the code - no need for an ui (file) at all, no pushbutton. Simply all in main.cpp with a QSqlTableModel and a sqlite in-memory db should do the job.

Mark81

Well, I hope this time it is ok for you:

#include <QApplication>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlDatabase>
#include <QSqlError>
#include <QMainWindow>
#include <QTableView>
#include <QStyledItemDelegate>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.open();
    QSqlQuery query(db);
    query.exec("CREATE TABLE bouquets (variant TEXT NOT NULL, name TEXT NOT NULL, capacity INT NOT NULL, PRIMARY KEY (name));");
    query.exec("INSERT INTO bouquets (variant, name, capacity) VALUES ('A', 'foo', 10);");

    QMainWindow window;
    QSqlTableModel *model;
    model = new QSqlTableModel(&window, db);
    model->setTable("bouquets");
    model->setEditStrategy(QSqlTableModel::OnRowChange);
    model->select();

    QTableView tableView;
    window.setCentralWidget(&tableView);
    tableView.setModel(model);
    tableView.setItemDelegate(new QStyledItemDelegate(&tableView));

    QObject::connect(model, &QSqlTableModel::beforeInsert, [=](QSqlRecord &record)
    {
        switch (model->lastError().type())
        {
        case QSqlError::ConnectionError: qWarning() << "Insert connection error" << model->lastError().text(); break;
        case QSqlError::StatementError: qWarning() << "Insert statement error" << model->lastError().text(); break;
        case QSqlError::TransactionError: qWarning() << "Insert transaction error" << model->lastError().text(); break;
        default: break;
        }
    });

    model->insertRow(model->rowCount());
    QModelIndex index = model->index(model->rowCount() - 1, 0);
    tableView.setCurrentIndex(index);
    tableView.edit(index);

    window.show();
    return a.exec();
}

I write again the steps:

fill only the "variant" column, press enter and change row
check the (correct) error about the missing "bouquets.name" field is fired
come back to the row, check again the same error is firing (just because we changed the row, ok)
fill the "name" column and press enter

The error is fired again! But we set the column to a value different than NULL. No error should be fired.
Now if you still edit the same column and press again enter it will change (correctly) to the next missing column.

fill the "capacity" column and press enter. The row is submitted but:

a) it should not since we didn't change the row
b) a wrong error ("NOT NULL constraint failed: bouquets.capacity Unable to fetch row") is fired: it makes no sense since the field was set and the row correctly submitted