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.4k 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.
  • SGaistS Offline
    SGaistS Offline
    SGaist
    Lifetime Qt Champion
    wrote on last edited by
    #2

    Hi,

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

    Interested in AI ? www.idiap.ch
    Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

    Bambang_PB 1 Reply Last reply
    0
    • SGaistS SGaist

      Hi,

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

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

      @SGaist , it fails inserting the record to the model

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