QSqlTableModel insertRecord issue with AutoIncrement field

jbarrena

Hi.

I have a table with a couple of fields, one PK (id) with autoincremented property:

CREATE TABLE "test" (
	"id"	INTEGER,
	"value"	varchar(20),
	PRIMARY KEY("id" AUTOINCREMENT)
);

I use a QSqlTableModel to expose the table to C++, with OnManualSubmit strategy.
I want to insert a record from a QSqlTableModel, but I receive a non-valid id until I submit the changes.

I noticed that the record obtained from the table has generated: yes and autoValue: false, but if I print the primaryKey() values, autoValue is true.

Here is the code snippet:

void mainTest() {
    QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QSQLITE", "MultifunctionDB");
    db.setDatabaseName("D:\\prj\\FIND\\Products\\MultiCooker\\SW\\HMI\\Generated\\Common\\DB\\multifunction.sqlite");
    if (db.open()) {
        QSqlQuery query(db);
        if(!query.exec("create table IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, value varchar(20) )")) {
            qDebug() << "Error creating table:" << query.lastError().text();
        }
    }
    else {
        qDebug() << "Error opening database:" << db.lastError().text();
    }
    QSqlTableModel model(nullptr, db);

    model.setTable("test");
    model.setEditStrategy(QSqlTableModel::OnManualSubmit);
    model.select();

    QSqlRecord rec = model.record();
    qDebug() << rec;
    qDebug() << model.primaryKey();
    rec.setGenerated("id", false);
    rec.setValue("value", "Thing");

    int newRecNo = model.rowCount();
    if (model.insertRecord(-1, rec)) {
        rec = model.record(newRecNo);
        qDebug() << "OK" << rec.value("id") << rec.value("value");
    }
    else {
        // Failure
        qWarning() << "UNABLE TO ADD ITEM TO DATABASE" << model.lastError().databaseText().toStdString();
    }
}

Am I misunderstanding something or is it a known Qt and SQLite bug?

Thanks for your help.
Kind Regards.

Julen.

Christian Ehrlicher

@jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

model.setEditStrategy(QSqlTableModel::OnManualSubmit);

So where is your submitAll() call?

jbarrena

@Christian-Ehrlicher
Do I need to submit the changes in order to have a new ID? In DB Browser (for example) when you insert a new row in a table you have already an autoincremented ID, an then you can make queries to the database with the new added item. Then you can submit or revert the changes.

Is there any option to have this behavior in Qt? I mean, when autoincrement PK is defined in DB, is it possible to have a new ID when you insertRecord in a QSqlTableModel?

This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

Thanks for your response, Christian.
Julen.

Christian Ehrlicher

@jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

Do I need to submit the changes in order to have a new ID?

Why do you not simply following my link and read the doc?

QSqlTableModel::OnManualSubmit All changes will be cached in the model until either submitAll() or revertAll() is called.

Christian Ehrlicher

@jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

See https://doc.qt.io/qt-6/qsqlfield.html#isAutoValue even though QSqlite is not mentioned here. Not all values can be set during a normal select() due to a big impact in the runtime to retrieve those (mostly unneeded) values.
generated = yes is correct here. It has nothing to do with the sql table but with the models to determine if it should be included e.g. in the update statement: https://doc.qt.io/qt-6/qsqlfield.html#setGenerated

jbarrena

@Christian-Ehrlicher

Thanks for your response, Christian. I read the documentation and thought isAutoValue and isGenerated, along with OnManualSubmit edit strategy, should provide a behavior similar to sqlite BEGIN TRANSACTION functionality. Indeed, if you read the documentation it is easily linked one functionality to the other.

This is what we expect from QSqlTableModel OnManualSubmit strategy:

sqlite> SELECT * FROM Test;
54|Test 2
55|Test 1
56|Test 2
57|Thing
58|Thing
59|Thing
sqlite> BEGIN TRANSACTION;
sqlite> INSERT INTO test (value) VALUES ('FROMCMD');
sqlite> SELECT * FROM Test;
54|Test 2
55|Test 1
56|Test 2
57|Thing
58|Thing
59|Thing
61|FROMCMD
sqlite> ROLLBACK
...> ;
sqlite> SELECT * FROM Test;
54|Test 2
55|Test 1
56|Test 2
57|Thing
58|Thing
59|Thing
sqlite>

I don't think this is approchable with QSqlTableModel without a workaraound. But nevertheless, thanks for your help.