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