Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Problem inserting record into QSqlTableModel of Postgresql
Forum Updated to NodeBB v4.3 + New Features

Problem inserting record into QSqlTableModel of Postgresql

Scheduled Pinned Locked Moved Unsolved General and Desktop
qsqltablemodelpostgresqlnew record
16 Posts 5 Posters 5.5k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • Bambang_PB Bambang_P

    @SGaist , it fails inserting the record to the model

    Bambang_PB Offline
    Bambang_PB Offline
    Bambang_P
    wrote on last edited by
    #4
    // 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)
    
    1 Reply Last reply
    0
    • Christian EhrlicherC Offline
      Christian EhrlicherC Offline
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on last edited by
      #5

      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.

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      Bambang_PB 1 Reply Last reply
      0
      • Christian EhrlicherC 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_PB Offline
        Bambang_PB Offline
        Bambang_P
        wrote on last edited by Bambang_P
        #6

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

        1 Reply Last reply
        0
        • Bambang_PB Offline
          Bambang_PB Offline
          Bambang_P
          wrote on last edited by
          #7

          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;
          
          }
          
          1 Reply Last reply
          0
          • Christian EhrlicherC Offline
            Christian EhrlicherC Offline
            Christian Ehrlicher
            Lifetime Qt Champion
            wrote on last edited by
            #8

            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.

            Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
            Visit the Qt Academy at https://academy.qt.io/catalog

            1 Reply Last reply
            0
            • Bambang_PB Offline
              Bambang_PB Offline
              Bambang_P
              wrote on last edited by Bambang_P
              #9

              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?

              1 Reply Last reply
              0
              • Christian EhrlicherC Offline
                Christian EhrlicherC Offline
                Christian Ehrlicher
                Lifetime Qt Champion
                wrote on last edited by
                #10

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

                Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                Visit the Qt Academy at https://academy.qt.io/catalog

                1 Reply Last reply
                0
                • Bambang_PB Offline
                  Bambang_PB Offline
                  Bambang_P
                  wrote on last edited by Bambang_P
                  #11

                  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))
                  { ....
                  
                  1 Reply Last reply
                  0
                  • Christian EhrlicherC Offline
                    Christian EhrlicherC Offline
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on last edited by
                    #12

                    Why don't you just try out my suggestion?

                    Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                    Visit the Qt Academy at https://academy.qt.io/catalog

                    Bambang_PB 1 Reply Last reply
                    0
                    • Christian EhrlicherC Christian Ehrlicher

                      Why don't you just try out my suggestion?

                      Bambang_PB Offline
                      Bambang_PB Offline
                      Bambang_P
                      wrote on last edited by
                      #13

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

                      Christian EhrlicherC 1 Reply Last reply
                      0
                      • Bambang_PB Bambang_P

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

                        Christian EhrlicherC Offline
                        Christian EhrlicherC Offline
                        Christian Ehrlicher
                        Lifetime Qt Champion
                        wrote on last edited by
                        #14

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

                        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                        Visit the Qt Academy at https://academy.qt.io/catalog

                        1 Reply Last reply
                        0
                        • G Offline
                          G Offline
                          Ghorwin
                          wrote on last edited by
                          #15

                          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

                          1 Reply Last reply
                          0
                          • L Offline
                            L Offline
                            lvlvl
                            wrote on last edited by
                            #16

                            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
                            
                            
                            1 Reply Last reply
                            0

                            • Login

                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • Users
                            • Groups
                            • Search
                            • Get Qt Extensions
                            • Unsolved