Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Problem inserting record into QSqlTableModel of Postgresql



  • Hi,
    First the environment:
    Qt version: 5.11.1
    Postgre version: 10.5
    Table structure:

    "CREATE TABLE datatest"
        "("
        " id serial"
        " ,kode character(1)"
        " ,nama character(10)"
        "  ,CONSTRAINT datatest_pkey PRIMARY KEY (id)"
        ")"
        " WITH ( OIDS = TRUE);";
    

    Problem:
    I can sucessfully insert record into a QSqlTableModel, the table has a primary key of type serial which is supplied by the database server, hence I set the setGenerated to false.

    QSqlRecord record = model.record();
     record.setGenerated("id",false);
     record.setValue("kode","P");
     record.setValue("nama","PATRICK");
     model.insertRecord(-1,record);
    

    I can also get the new primary key which is auto generated by the database server:

    // get the new record id
        int newRecId = model.query().lastInsertId().toInt();
        cout << ", field ID of the new record is " << newRecId << endl;
    

    However, apparently the model does not store the new id to the model, it cannot find the new record

    // find the new inserted record
        QModelIndexList listNewRec =model.match(model.index(0,0),Qt::DisplayRole,newRecId);
    

    result of the above statement (listNewRec) is empty, if we display the table on a QTableView, the new record will be displayed as blank record with an exclamation mark on the left.

    The only solution to update the model is call select() after the insert. However, I feel this is not the right way because it can be very inefficient with table with many records.

    Am I missing something or is it a bug?

    Here is the complete source code if anyone wants to try, just replace the databasename, username and password with yours.

    https://pastebin.com/ZT4xt4SK


  • Lifetime Qt Champion

    Hi,

    What if you don’t modify the generated Id propety ?



  • @SGaist , it fails inserting the record to the model



  • // insert record
        cout << "Inserting a record to the model...";
        QSqlRecord record = model.record();
        //record.setGenerated("id",false);
        record.setValue("kode","P");
        record.setValue("nama","PATRICK");
        if ( ! model.insertRecord(-1,record))
        {
            cout << "Error inserting record to the model" << endl;
            cout << model.lastError().databaseText().toStdString() <<endl;
            return 0;
        };
    

    the output:

    Inserting a record to the model...Error inserting record to the model
    ERROR:  null value in column "id" violates not-null constraint
    DETAIL:  Failing row contains (null, P, PATRICK   ).
    (23502)
    

  • Qt Champions 2019

    What SubmitStrategy do you use? Does primaryKey() return the correct key? Does it all work with QSqlite - I've the feeling that the postgres driver does not call QSqlField::setAutoValue() ... Please check if the QSqlField for the id returned with QSqlTableModel::record() has autoValue set.



  • @Christian-Ehrlicher,

    What SubmitStrategy do you use?

    I guess you mean editStrategy(), well in my demo test I don't explicitly setting it. After reading your reply, I found this:

    • onFieldChange and onRowChange: fail even after calling submitAll() after insert

    • onManualSubmit: OK after calling submitAll()

    Does primaryKey() return the correct key?

    Yes.

    cout << "Primary key : " << model.primaryKey().name().toStdString() << endl;
    

    output:

    Primary key : datatest_pkey
    

    I've the feeling that the postgres driver does not call QSqlField::setAutoValue() ... Please check if the QSqlField for the id returned with QSqlTableModel::record() has autoValue set.

    QSqlField fieldID = record.field("id");
    cout << "Field "<< fieldID.name().toStdString() << " isAutoValue = " << fieldID.isAutoValue() << endl;
    

    output:

    Field id isAutoValue = 0
    

    Does it all work with QSqlite

    I try with QSQLITE, the outcome is worse, it could not find the new record even after redoing select() after insert regardless the editStrategy()



  • I modify my source code to accomodate different scenarios: using postgres or sqlite, and what editstrategy we want to use.

    #include <QCoreApplication>
    #include <QSqlDatabase>
    #include <QSqlTableModel>
    #include <QSqlQuery>
    #include <QSqlError>
    #include <QSqlTableModel>
    #include <QSqlRecord>
    #include <QModelIndexList>
    #include <QSqlIndex>
    #include <QSqlField>
    #include <iostream>
    using namespace std;
    
    enum DB_DRIVER{ USE_POSTGRE, USE_SQLITE};
    
    int main(int argc, char *argv[])
    {
    
        // change the parameter here
        DB_DRIVER DRIVER = USE_POSTGRE;
        QSqlTableModel::EditStrategy EDIT_STRATEGY = QSqlTableModel::OnManualSubmit;
    
        QString dbDriver;
        switch (DRIVER) {
        case USE_POSTGRE:
            dbDriver = "QPSQL";
            break;
        case USE_SQLITE:
            dbDriver = "QSQLITE";
            break;
        default:
            cout << "What database do you want to use?" << endl ;
            return 0;
            break;
        }
        // DB Connection
        QSqlDatabase db = QSqlDatabase::addDatabase(dbDriver);
        switch (DRIVER) {
        case USE_POSTGRE:
            db.setHostName("127.0.0.1");
            db.setDatabaseName("your_database_name");
            db.setUserName("your_user_name");
            db.setPassword("your_pasword");
            break;
    
        case USE_SQLITE:
            db.setDatabaseName(":memory:");
            break;
        }
        if ( ! db.open())
        {
            cout << "Cannot open database" << endl;
        }
    
        // drop table
        cout << "drop table datatest..." << endl;
        QSqlQuery q = QSqlQuery(db);
        QString sql;
        sql = "drop table datatest;";
        q.exec(sql);
    
        // Create table
        cout << "create table datatest..." << endl;
        cout << db.driverName().toStdString() << endl;
    
        sql=
        "CREATE TABLE datatest"
        "("
        " id serial"
        " ,kode character(1)"
        " ,nama character(10)"
        "  ,CONSTRAINT datatest_pkey PRIMARY KEY (id)"
        ")"
        /*" WITH ( OIDS = TRUE);"*/;
    
    
        if ( ! q.exec( sql ))
        {
            cout << "error create table" << endl;
            cout << q.lastError().databaseText().toStdString() << endl;
            return 0;
        };
    
    
        // Populate 2 records
        cout << "Populate table..." << endl;
        sql = "insert into datatest (kode,nama) values (:kode,:nama)";
        q.prepare(sql);
    
        //for (int i = 0 ; i < 4999 ; i++)
        {
            q.bindValue(":kode","A");q.bindValue(":nama","ALBERT"); q.exec();
            q.bindValue(":kode","Z");q.bindValue(":nama","ZORRO"); q.exec();
        }
    
        // Create model
        cout << "Create QSqlTableModel..." << endl;
        QSqlTableModel model;
        model.setEditStrategy(EDIT_STRATEGY);
        model.setTable("datatest");
        cout << "Primary key : " << model.primaryKey().name().toStdString() << endl;
        //model.sort(1,Qt::AscendingOrder);
        model.select();
    
        // insert record
    
        QSqlRecord record = model.record();
    
        QSqlField fieldID = record.field("id");
        cout << "Field "<< fieldID.name().toStdString() << " isAutoValue = " << fieldID.isAutoValue() << endl;
        cout << "Inserting a record to the model...";
        record.setGenerated("id",false);
        record.setValue("kode","P");
        record.setValue("nama","PATRICK");
        if ( ! model.insertRecord(-1,record))
        {
            cout << "Error inserting record to the model" << endl;
            cout << model.lastError().databaseText().toStdString() <<endl;
            return 0;
        };
        if ( model.editStrategy() == QSqlTableModel::OnManualSubmit)
        {
            if ( ! model.submitAll())
            {
                cout << "Error submit after insert" << endl;
                return 0;
            }
        }
    
        // get the new record id
        int newRecId = model.query().lastInsertId().toInt();
        cout << ", field ID of the new record is " << newRecId << endl;
    
        // find the new inserted record
        QModelIndexList listNewRec = model.match(model.index(0,0),Qt::DisplayRole,newRecId);
        if ( listNewRec.size() == 1 )
        {
            cout << "Found the new record..." << endl;
        }
        else
        {
            cout << "Can not locate the new inserted record" << endl;
        }
    
        // reselect the model
        cout << "doing model.select()" << endl;
        model.select();
    
        // find the new inserted record
        listNewRec = model.match(model.index(0,0),Qt::DisplayRole,newRecId);
        if ( listNewRec.size() == 1 )
        {
            cout << "Found the new record  after reselect the model..." << endl;
        }
        else
        {
            cout << "Can not locate the new inserted record" << endl;
        }
    
    
        return 0;
    
    }
    

  • Qt Champions 2019

    Ok, from my understanding of the code, it will not work when isAutoValue() is not set correctly:

    qsqltablemodel.cpp:773ff
    if (success) {
        if (d->strategy != OnManualSubmit && mrow.op() == QSqlTableModelPrivate::Insert) {
             int c = mrow.rec().indexOf(d->autoColumn);
             if (c != -1 && !mrow.rec().isGenerated(c))
                mrow.setValue(c, d->editQuery.lastInsertId());
        }
    

    d->autoColumn is set to true only when isAutoValue() returns true.

    Therefore I don't see a chance to make it work with postgres without deriving from QSqlTableModel. Overwriting virtual bool select(); to return the pk with isAutoVal set to true should work.



  • Thank you Christian,

    The documentation says that QSqlField has a public value setAutoValue.

    However, this code:

    model.record().field("id").setAutoValue(true);
    cout << "Field "<< fieldID.name().toStdString() << " isAutoValue = " << model.record().field("id").isAutoValue() << endl;
    

    fails to set the AutoValue flag, I suspect because model.record().field("id") only a copy of the field, not the real field of the model. How can we access the real fields of the mode?


  • Qt Champions 2019

    Your code can't work, correct. Therefore I said: Derive from QSqlTableModel and overwrite select() to adjust the mentioned value.



  • If I call the defaultValue() on the record which obtains from the QSqlDatabase it returns correct value:

    QSqlRecord r = db.record("datatest");
    cout << r.field("id").defaultValue().toString().toStdString() << endl;
    

    output:

    nextval('datatest_id_seq'::regclass)
    

    However, isAutoValue still 0

    Tracing to postgresql driver source, it seems postgre driver never sets the autovalue. Here is code snippet from qsql_psql.cpp:

    QSqlRecord QPSQLDriver::record(const QString& tablename) const
    {......
        int len = query.value(3).toInt();
        int precision = query.value(4).toInt();
        // swap length and precision if length == -1
        if (len == -1 && precision > -1) {
            len = precision - 4;
            precision = -1;
        }
        QString defVal = query.value(5).toString();
        if (!defVal.isEmpty() && defVal.at(0) == QLatin1Char('\''))
            defVal = defVal.mid(1, defVal.length() - 2);
        QSqlField f(query.value(0).toString(), qDecodePSQLType(query.value(1).toInt()), tablename);
        f.setRequired(query.value(2).toBool());
        f.setLength(len);
        f.setPrecision(precision);
        f.setDefaultValue(defVal);
        f.setSqlType(query.value(1).toInt());
        info.append(f);
    

    There is no f.setAutoValue call at all, may be there should be an additional line :

          f.setAutoValue( defVal.contains( "(") && defVal.contains( ")");
    

    ?

    May be the better approach would be querying first the next id and set it on the insert statement.

    sql = "select nextval(\'datatest_id_seq\')";
    q.exec(sql);
    q.first();
    int newId = q.value(0).toInt();
    

    and then

    // record.setGenerated("id",false);
    record.setValue("id" , newId);
    record.setValue("kode","P");
    record.setValue("nama","PATRICK");
    if ( ! model.insertRecord(-1,record))
    { ....

  • Qt Champions 2019

    Why don't you just try out my suggestion?



  • I am still studying QSqlTableModel source code and still don't have idea which part should be overrided...


  • Qt Champions 2019

    @Bambang_P said in Problem inserting record into QSqlTableModel of Postgresql:

    still don't have idea which part should be overrided...

    I'm giving up...

    Derive from QSqlTableModel and overwrite select() to adjust the mentioned value.