SqlTableModel and autovalues/primary key for new records

Andy314

Hello !
I can not figure out, how to manage in a SqlTableModel automatic primary keys for new records.
The database should generate it automatically, so I cannot set its value in the SqlTableMode, but then I get an error like "cannot set a null value to a variable".

Here is my code:

QSqlTableModel m;
m.setEditStrategy(QSqlTableModel::OnManualSubmit);
m.setTable("tblTest");
QSqlRecord rec=m.record();
rec.setValue("Test", "test text");
//rec.setValue("ID", 7);
m.insertRecord(-1, rec);
bool ok=m.submitAll();
 if(!ok)
 {
     QMessageBox::critical(0, "Cannot create new record",
     m.lastError().text(),
     QMessageBox::Cancel);
    }

How can I store a new record and how can I get the new primary key back ?

ChrisW67

Have a look at QSqlRecord::setGenerated() for handling the insert of the autonumber field.
You should be able to see the allocated id in the newly inserted record through the model Interface

lukeQt

You need to call insert row. Something like this should work.

   self.pushbutton.connect(self.add_row)

    def add_row(self):
        rows = self.m.rowCount()
        self.m.insertRow(rows)
        self.m.setData(self.m.index(rows, 1), "fff")
        self.m.setData(self.m.index.(rows, 2),"ttt")
        self.m.submitAll()

Andy314

@lukeQt

My enviroment: Qt-5.4.1, minGW, Windows64, odbc, MS-Access-Database

I found out that QSqlTableModel::insertRecord( ...) never works !
The model tries to write a null value for the primary key in the database.

The methode from @lukeQt, to with instertRow(...) and setData(...) works.
The record in the DB gets a new gererated primary key automatically. The only problem is that, I can not get back this primary key in my program. The record in the model gets **always ** the ID of the **first **record in the database-table.

    QSqlTableModel m;
    m.setEditStrategy(QSqlTableModel::OnManualSubmit);
    m.setTable("tblTest");
    m.insertRow(0);
    m.setData(m.index(0,1), "test text");
    bool v1=m.record(0).field(0).isAutoValue();  // false
    bool v2=m.record(0).field(0).isGenerated(); // false
    bool ok=m.submitAll();
    int id = m.record(0).value(0).toInt();
    qDebug() << "new id =" << id;  // always id of first record in table
    QSqlQuery q;

    if(!ok)
    {
        QMessageBox::critical(0, "Cannot create new record",
            m.lastError().text(),
            QMessageBox::Cancel);
    }

Andy314

@Andy314
Correction:
insertRecord() works too. I missunderstood the isGenerated()-function and thought that true indicates that the DB gererates this value. But it is the opposite, false indicates that the current value in the record should not be used and should generated by the database.

Nevertheless, I cannot get this generated values back. Form MS-Access I know a reocrd-property "seeChanges" to solve the problem. Has Qt something similar.

ChrisW67

Here is the one-file example. Feel free to try it on your ODBC database connection. Also have a look at the help for QSqlTableModel::setRecord().

#include <QApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlTableModel>
#include <QSqlRecord>
#include <QTableView>
#include <QDebug>

int main(int argc, char **argv) {
    QApplication app(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (db.open()) {
        QSqlQuery query;
        query.exec("create table test (id INTEGER PRIMARY KEY AUTOINCREMENT, "
                   "value varchar(20) )");
        query.exec("insert into test (value)  values ('Test 1')");
        query.exec("insert into test (value)  values ('Test 2')");
    }

    QSqlTableModel model;
    model.setTable("test");
    model.select();

    QSqlRecord rec = model.record();
    rec.setGenerated("id", false);
    rec.setValue("value", "Floober");
    int newRecNo = model.rowCount();
    if (model.insertRecord(newRecNo, rec)) {
        rec = model.record(newRecNo);
        qDebug() << "OK" << rec.value("id") << rec.value("value");
    }

    QTableView v;
    v.setModel(&model);
    v.show();
    return app.exec();
}

and the output

OK QVariant(qlonglong, 3) QVariant(QString, "Floober")

lukeQt

You need to put self.m.select() in the initialization of the class. This is because your code needs to ask for fresh data anytime the database changes. This will allow the primary key value to appear in the model. If that does not work then send the entire code so we can look at it. I hope this was helpful.

Andy314

@lukeQt
I think I understand all now.
My mistake was that I forgot that the TableModel works on the whole data table (I did not set any filter). CommitAll() does not only save my new record, but gets all other records from the database. Therefore my new record was not at the position 0, but on the last position. Here I get the correct primary key.

I hope, the CommitAll() or Select() does not really retrievs data from the database! This would be a very inefficient way for inserting only one record via code.
Is the presented code the usual way for inserting a new record, if no tableview is needed for it?

Thanks for the help.

lukeQt

I think select is called anytime a change is made. You wouldn't know what changes in the db so I think select is called anytime a change is made. That is my understanding. I am by no means an expert in pyqt though.