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. QSqlTableModel insertRecord issue with AutoIncrement field
Forum Updated to NodeBB v4.3 + New Features

QSqlTableModel insertRecord issue with AutoIncrement field

Scheduled Pinned Locked Moved Unsolved General and Desktop
6 Posts 2 Posters 368 Views 1 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.
  • J Offline
    J Offline
    jbarrena
    wrote on last edited by
    #1

    Hi.

    I have a table with a couple of fields, one PK (id) with autoincremented property:

    CREATE TABLE "test" (
    	"id"	INTEGER,
    	"value"	varchar(20),
    	PRIMARY KEY("id" AUTOINCREMENT)
    );
    

    I use a QSqlTableModel to expose the table to C++, with OnManualSubmit strategy.
    I want to insert a record from a QSqlTableModel, but I receive a non-valid id until I submit the changes.

    I noticed that the record obtained from the table has generated: yes and autoValue: false, but if I print the primaryKey() values, autoValue is true.

    Here is the code snippet:

    void mainTest() {
        QSqlDatabase db;
        db = QSqlDatabase::addDatabase("QSQLITE", "MultifunctionDB");
        db.setDatabaseName("D:\\prj\\FIND\\Products\\MultiCooker\\SW\\HMI\\Generated\\Common\\DB\\multifunction.sqlite");
        if (db.open()) {
            QSqlQuery query(db);
            if(!query.exec("create table IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, value varchar(20) )")) {
                qDebug() << "Error creating table:" << query.lastError().text();
            }
        }
        else {
            qDebug() << "Error opening database:" << db.lastError().text();
        }
        QSqlTableModel model(nullptr, db);
    
        model.setTable("test");
        model.setEditStrategy(QSqlTableModel::OnManualSubmit);
        model.select();
    
        QSqlRecord rec = model.record();
        qDebug() << rec;
        qDebug() << model.primaryKey();
        rec.setGenerated("id", false);
        rec.setValue("value", "Thing");
    
        int newRecNo = model.rowCount();
        if (model.insertRecord(-1, rec)) {
            rec = model.record(newRecNo);
            qDebug() << "OK" << rec.value("id") << rec.value("value");
        }
        else {
            // Failure
            qWarning() << "UNABLE TO ADD ITEM TO DATABASE" << model.lastError().databaseText().toStdString();
        }
    }
    

    Am I misunderstanding something or is it a known Qt and SQLite bug?

    Thanks for your help.
    Kind Regards.

    Julen.

    Christian EhrlicherC 1 Reply Last reply
    0
    • J jbarrena

      Hi.

      I have a table with a couple of fields, one PK (id) with autoincremented property:

      CREATE TABLE "test" (
      	"id"	INTEGER,
      	"value"	varchar(20),
      	PRIMARY KEY("id" AUTOINCREMENT)
      );
      

      I use a QSqlTableModel to expose the table to C++, with OnManualSubmit strategy.
      I want to insert a record from a QSqlTableModel, but I receive a non-valid id until I submit the changes.

      I noticed that the record obtained from the table has generated: yes and autoValue: false, but if I print the primaryKey() values, autoValue is true.

      Here is the code snippet:

      void mainTest() {
          QSqlDatabase db;
          db = QSqlDatabase::addDatabase("QSQLITE", "MultifunctionDB");
          db.setDatabaseName("D:\\prj\\FIND\\Products\\MultiCooker\\SW\\HMI\\Generated\\Common\\DB\\multifunction.sqlite");
          if (db.open()) {
              QSqlQuery query(db);
              if(!query.exec("create table IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, value varchar(20) )")) {
                  qDebug() << "Error creating table:" << query.lastError().text();
              }
          }
          else {
              qDebug() << "Error opening database:" << db.lastError().text();
          }
          QSqlTableModel model(nullptr, db);
      
          model.setTable("test");
          model.setEditStrategy(QSqlTableModel::OnManualSubmit);
          model.select();
      
          QSqlRecord rec = model.record();
          qDebug() << rec;
          qDebug() << model.primaryKey();
          rec.setGenerated("id", false);
          rec.setValue("value", "Thing");
      
          int newRecNo = model.rowCount();
          if (model.insertRecord(-1, rec)) {
              rec = model.record(newRecNo);
              qDebug() << "OK" << rec.value("id") << rec.value("value");
          }
          else {
              // Failure
              qWarning() << "UNABLE TO ADD ITEM TO DATABASE" << model.lastError().databaseText().toStdString();
          }
      }
      

      Am I misunderstanding something or is it a known Qt and SQLite bug?

      Thanks for your help.
      Kind Regards.

      Julen.

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

      @jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

      model.setEditStrategy(QSqlTableModel::OnManualSubmit);

      So where is your submitAll() call?

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

      J 1 Reply Last reply
      1
      • Christian EhrlicherC Christian Ehrlicher

        @jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

        model.setEditStrategy(QSqlTableModel::OnManualSubmit);

        So where is your submitAll() call?

        J Offline
        J Offline
        jbarrena
        wrote on last edited by
        #3

        @Christian-Ehrlicher
        Do I need to submit the changes in order to have a new ID? In DB Browser (for example) when you insert a new row in a table you have already an autoincremented ID, an then you can make queries to the database with the new added item. Then you can submit or revert the changes.

        Is there any option to have this behavior in Qt? I mean, when autoincrement PK is defined in DB, is it possible to have a new ID when you insertRecord in a QSqlTableModel?

        This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

        Thanks for your response, Christian.
        Julen.

        Christian EhrlicherC 2 Replies Last reply
        0
        • J jbarrena

          @Christian-Ehrlicher
          Do I need to submit the changes in order to have a new ID? In DB Browser (for example) when you insert a new row in a table you have already an autoincremented ID, an then you can make queries to the database with the new added item. Then you can submit or revert the changes.

          Is there any option to have this behavior in Qt? I mean, when autoincrement PK is defined in DB, is it possible to have a new ID when you insertRecord in a QSqlTableModel?

          This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

          Thanks for your response, Christian.
          Julen.

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

          @jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

          Do I need to submit the changes in order to have a new ID?

          Why do you not simply following my link and read the doc?

          QSqlTableModel::OnManualSubmit All changes will be cached in the model until either submitAll() or revertAll() is called.

          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
          • J jbarrena

            @Christian-Ehrlicher
            Do I need to submit the changes in order to have a new ID? In DB Browser (for example) when you insert a new row in a table you have already an autoincremented ID, an then you can make queries to the database with the new added item. Then you can submit or revert the changes.

            Is there any option to have this behavior in Qt? I mean, when autoincrement PK is defined in DB, is it possible to have a new ID when you insertRecord in a QSqlTableModel?

            This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

            Thanks for your response, Christian.
            Julen.

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

            @jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

            This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

            See https://doc.qt.io/qt-6/qsqlfield.html#isAutoValue even though QSqlite is not mentioned here. Not all values can be set during a normal select() due to a big impact in the runtime to retrieve those (mostly unneeded) values.
            generated = yes is correct here. It has nothing to do with the sql table but with the models to determine if it should be included e.g. in the update statement: https://doc.qt.io/qt-6/qsqlfield.html#setGenerated

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

            J 1 Reply Last reply
            1
            • Christian EhrlicherC Christian Ehrlicher

              @jbarrena said in QSqlTableModel insertRecord issue with AutoIncrement field:

              This field (ID) should be generated=false and autoValue=true, but it seems that the driver does not manage these flags correctly...

              See https://doc.qt.io/qt-6/qsqlfield.html#isAutoValue even though QSqlite is not mentioned here. Not all values can be set during a normal select() due to a big impact in the runtime to retrieve those (mostly unneeded) values.
              generated = yes is correct here. It has nothing to do with the sql table but with the models to determine if it should be included e.g. in the update statement: https://doc.qt.io/qt-6/qsqlfield.html#setGenerated

              J Offline
              J Offline
              jbarrena
              wrote on last edited by
              #6

              @Christian-Ehrlicher

              Thanks for your response, Christian. I read the documentation and thought isAutoValue and isGenerated, along with OnManualSubmit edit strategy, should provide a behavior similar to sqlite BEGIN TRANSACTION functionality. Indeed, if you read the documentation it is easily linked one functionality to the other.

              This is what we expect from QSqlTableModel OnManualSubmit strategy:

              sqlite> SELECT * FROM Test;
              54|Test 2
              55|Test 1
              56|Test 2
              57|Thing
              58|Thing
              59|Thing
              sqlite> BEGIN TRANSACTION;
              sqlite> INSERT INTO test (value) VALUES ('FROMCMD');
              sqlite> SELECT * FROM Test;
              54|Test 2
              55|Test 1
              56|Test 2
              57|Thing
              58|Thing
              59|Thing
              61|FROMCMD
              sqlite> ROLLBACK
              ...> ;
              sqlite> SELECT * FROM Test;
              54|Test 2
              55|Test 1
              56|Test 2
              57|Thing
              58|Thing
              59|Thing
              sqlite>
              

              I don't think this is approchable with QSqlTableModel without a workaraound. But nevertheless, thanks for your help.

              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