Problem inserting record into QSqlTableModel of Postgresql

[Bambang_P]

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

[SGaist]

Hi,

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

[Bambang_P]

@SGaist , it fails inserting the record to the model

[Bambang_P]

// 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)

[Christian Ehrlicher]

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.

[Bambang_P]

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

[Bambang_P]

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;

}

[Christian Ehrlicher]

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.

[Bambang_P]

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?

[Christian Ehrlicher]

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

[Bambang_P]

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

[Christian Ehrlicher]

Why don't you just try out my suggestion?

[Bambang_P]

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

[Christian Ehrlicher]

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

[Ghorwin]

Hi,

the solution is rather simple. You just have to remove all auto-generated columns from the record:

QSqlRecord record = model.record(); // record contains the first column with auto-generated values

// remove first field with auto-generated values
record.remove(0);

// set values as usual
record.setValue("kode","P");
record.setValue("nama","PATRICK");

// finally insert the record
model.insertRecord(-1,record);

-Andreas

[lvlvl]

To use it as a model for QTableView with PostgreSQL 9.1 on PySide2, I subclass QSqlTableModel and rewrite the function insertRowIntoTable (assuming the primary key of serial type is the first field of the record) :

def insertRowIntoTable(self, values):
							
	if QSqlTableModel.insertRowIntoTable(self, values):
		# returns the value of the primary key "autovalue" (serial) only when the record is added in database	
		rs = QSqlQuery()
		rs.exec_("SELECT CURRVAL (pg_get_serial_sequence('public." + self.tableName() + "','"+ self.primaryKey().field(0).name() +"'))")
		if rs.next():
			ID = rs.value(0)			
			# without this line the row displayed in the QTableView immediately after insertion cannot be updated
			self.setData (self.index(self.rowCount()-1,0), ID)
			
		
		# without this line the created row appears blank in the QTableView
		values.remove(0)
											
		return True
		
	return False