submitAll() -> "no fields to update"

[JeanC]

Hello,

Porting some apps from linux to windows..
I get this error from a QSqlTableModel when trying to submit changes. In linux this works without error and updates the database, in windows it doesn't.
Changes made to individual records are done however, just not submitted back to the database.
Did I forget something?

And if anyone knows an ide for qt for windows which does debugging please tell...

[caduel]

This is an error in Qt 4.4.1 and 4.4.2; Trolls know about it and hopefully fix it soon.

[JeanC]

Too bad, thanks.

[JeanC]

I found two bug entries which seem about this bug
http://trolltech.com/developer/task-...ntry&id=227468
http://trolltech.com/developer/task-...ntry&id=182664

The latter is from 2007, so I guess the first is this bug and it says:

Version for fix set to 'Not scheduled', Priority changed to 'No Priority'

That does not indicate there's gonna be a bugfix soon. Somebody knows a workaround?

[caduel]

fyi: this bug was fixed in Qt 4.4.0 (which was release in 2008 iirc)
[that is to say: I get this bug in a lib of mine when saving a record. My code worked (and works) with 4.4.0; it does not work with newer releases]

[JeanC]

He caduel,

Well in linux I use some 3.x version and have no trubbles.
I'm wondering if I should downgrade and compile qt 4.4.0 or wait for a bugfix.
I'll see how it goes, if I miss the functionality too much I'll just downgrade.
Thanks.

[patrik08]

Hello,

Porting some apps from linux to windows..
I get this error from a QSqlTableModel when trying to submit changes. In linux this works without error and updates the database, in windows it doesn't.
Changes made to individual records are done however, just not submitted back to the database.
Did I forget something?

And if anyone knows an ide for qt for windows which does debugging please tell...

On window OS i found submitAll() bug only on mysql client driver mysql version < 5.0.2 (minor as).

try to build http://ppk.ciz.ch/qt_c++/mysql_model...YSQL_Model.zip screenshot
http://ppk.ciz.ch/qt_c++/mysql_model...wser_model.png if you use mysql ... on sqlite3 i never found submitAll() bug.

On window OS to close database MYSQL use delete &class to suppress all message....
QSqlDatabase::close () only run clean on linux & mac.

[caduel]

I get the error on Oracle + linux.

[patrik08]

I get the error on Oracle + linux.

debug your table if driver can find index field from table
If no index is found it not possibel to "auto update" or submitAll().

Mysql = SHOW INDEX FROM table
Ora = select index_name from dba_indexes where table_name='tablename';
on oracle must display a key PRIMARY
if not, set one...

update where what = xx

/* discovery total row whitout limit mysql / to play next prev ...  button */
void Mysql_Table::UpdateSumm()
{
   QString grepprimary,grepauto;
   /* reset index  fieldname */
    /* SHOW INDEX FROM table */
 
   INDEXFIELD = "";
   FieldNameList.clear();
   const QString info = QString("SHOW COLUMNS FROM %1").arg(table);
    QSqlQuery query(info,db);
       QSqlRecord rec = query.record();
       ////int sumcol = rec.count();
            while (query.next()) {
                grepprimary = query.value(3).toString();
                grepprimary = grepprimary.toLower();
                grepauto = query.value(5).toString();
                grepauto = grepauto.toLower();
                ///////Q_ASSERT(grepauto.size() > 1);  /* no mysql or not connect! or grand not...*/
                if (grepprimary == "pri" && grepauto.contains("auto")) {
                    INDEXFIELD = query.value(0).toString();
                    //////////qDebug() << "### INDEXFIELD have found " << INDEXFIELD  ;
                }
                FieldNameList.append(query.value(0).toString());
            }
 
 
     if (INDEXFIELD.size() < 1) {
      haveindex = false;
     } else {
      haveindex = true; 
     }
 
 
     totalrow = 0;
     if (haveindex) {
         const QString summitem = QString("SELECT COUNT(%2) FROM %1").arg(table).arg(INDEXFIELD);
         query.exec(summitem);
         while (query.next()) {
             totalrow = query.value(0).toInt();  
         }
 
     }
     qDebug() << "### totalrow " << totalrow;
 
}

/* discovery total row whitout limit mysql / to play next prev ...  button */
void Mysql_Table::UpdateSumm()
{
   QString grepprimary,grepauto;
   /* reset index  fieldname */
    /* SHOW INDEX FROM table */
    
   INDEXFIELD = "";
   FieldNameList.clear();
   const QString info = QString("SHOW COLUMNS FROM %1").arg(table);
    QSqlQuery query(info,db);
       QSqlRecord rec = query.record();
       ////int sumcol = rec.count();
            while (query.next()) {
                grepprimary = query.value(3).toString();
                grepprimary = grepprimary.toLower();
                grepauto = query.value(5).toString();
                grepauto = grepauto.toLower();
                ///////Q_ASSERT(grepauto.size() > 1);  /* no mysql or not connect! or grand not...*/
                if (grepprimary == "pri" && grepauto.contains("auto")) {
                    INDEXFIELD = query.value(0).toString();
                    //////////qDebug() << "### INDEXFIELD have found " << INDEXFIELD  ;
                }
                FieldNameList.append(query.value(0).toString());
            }
            
            
     if (INDEXFIELD.size() < 1) {
      haveindex = false;
     } else {
      haveindex = true; 
     }
     
     
     totalrow = 0;
     if (haveindex) {
         const QString summitem = QString("SELECT COUNT(%2) FROM %1").arg(table).arg(INDEXFIELD);
         query.exec(summitem);
         while (query.next()) {
             totalrow = query.value(0).toInt();  
         }
    
     }
     qDebug() << "### totalrow " << totalrow;

}

To copy to clipboard, switch view to plain text mode 

[kaje44]

I get the error on postgresql + linux.

[Al_]

Hello

I get such error (i.e., error in submitAll after insertRows) using Sqlite and Qt 4.7.1. Is this still the previously mentioned unresolved bug, or do I something wrong?

The code below produces an ASSERT failure in the last row with the error message "No Fields to update". The two lines that are commented out are two options that I tried.

QSqlDatabase db = QSqlDatabase::addDatabase(QLatin1String("QSQLITE"));
   db.setDatabaseName(QLatin1String("testdb.db"));
   Q_ASSERT(db.open());
   QSqlQuery sqlQuery(db);
   bool ok;
//   ok = sqlQuery.exec(QLatin1String("CREATE TABLE Table1 (Content, Identifier INTEGER PRIMARY KEY AUTOINCREMENT, Parent INTEGER NOT NULL, Details);"));
   ok = sqlQuery.exec(QLatin1String("CREATE TABLE Table1 (Content, Identifier INTEGER PRIMARY KEY AUTOINCREMENT, Parent, Details);"));
//   ok = sqlQuery.exec(QLatin1String("CREATE TABLE Table1 (Content, Identifier, Parent, Details);"));
   Q_ASSERT_X(ok, "CREATE TABLE sql statement", "Table1 likely already exists");
   ok = sqlQuery.exec(QLatin1String("INSERT INTO Table1(Content, Parent, Details) VALUES ('first item', 0, 'Details for first item');"));
   Q_ASSERT(ok);
 
   QSqlTableModel* tableModel = new QSqlTableModel(this, db);
   tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
   tableModel->setTable(QLatin1String("Table1"));
   tableModel->select();
   tableView->setModel(tableModel);
   tableView->resizeColumnsToContents();
 
   ok = tableModel->insertRows(0, 1, QModelIndex());
   Q_ASSERT(ok);
 
   ok = tableModel->submitAll();
   Q_ASSERT_X(ok, "submitAll", qPrintable(tableModel->lastError().text()));

QSqlDatabase db = QSqlDatabase::addDatabase(QLatin1String("QSQLITE"));
   db.setDatabaseName(QLatin1String("testdb.db"));
   Q_ASSERT(db.open());
   QSqlQuery sqlQuery(db);
   bool ok;
//   ok = sqlQuery.exec(QLatin1String("CREATE TABLE Table1 (Content, Identifier INTEGER PRIMARY KEY AUTOINCREMENT, Parent INTEGER NOT NULL, Details);"));
   ok = sqlQuery.exec(QLatin1String("CREATE TABLE Table1 (Content, Identifier INTEGER PRIMARY KEY AUTOINCREMENT, Parent, Details);"));
//   ok = sqlQuery.exec(QLatin1String("CREATE TABLE Table1 (Content, Identifier, Parent, Details);"));
   Q_ASSERT_X(ok, "CREATE TABLE sql statement", "Table1 likely already exists");
   ok = sqlQuery.exec(QLatin1String("INSERT INTO Table1(Content, Parent, Details) VALUES ('first item', 0, 'Details for first item');"));
   Q_ASSERT(ok);

   QSqlTableModel* tableModel = new QSqlTableModel(this, db);
   tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
   tableModel->setTable(QLatin1String("Table1"));
   tableModel->select();
   tableView->setModel(tableModel);
   tableView->resizeColumnsToContents();

   ok = tableModel->insertRows(0, 1, QModelIndex());
   Q_ASSERT(ok);

   ok = tableModel->submitAll();
   Q_ASSERT_X(ok, "submitAll", qPrintable(tableModel->lastError().text()));

To copy to clipboard, switch view to plain text mode 

Any help appreciated; either to correct my code or to find a work-around.

Al_

Edit:
others have issues with removeRows ..., see http://www.qtcentre.org/threads/3876...ght=insertRows

[norobro]

Couldn't find anything in the docs (probably in there somewhere ) but by looking at the source code it appears you cannot submit an empty record to a database. And the question that bears asking is why would you?

From qsqltablemodel.cpp:

bool QSqlTableModel::insertRowIntoTable(const QSqlRecord &values)
{
    Q_D(QSqlTableModel);
    QSqlRecord rec = values;
    emit beforeInsert(rec);
 
    bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries);
    QString stmt = d->db.driver()->sqlStatement(QSqlDriver::InsertStatement, d->tableName,
                                                rec, prepStatement);
 
    if (stmt.isEmpty()) {
        d->error = QSqlError(QLatin1String("No Fields to update"), QString(),
                                 QSqlError::StatementError);
        return false;
    }
 
    return d->exec(stmt, prepStatement, rec);
}

bool QSqlTableModel::insertRowIntoTable(const QSqlRecord &values)
{
    Q_D(QSqlTableModel);
    QSqlRecord rec = values;
    emit beforeInsert(rec);

    bool prepStatement = d->db.driver()->hasFeature(QSqlDriver::PreparedQueries);
    QString stmt = d->db.driver()->sqlStatement(QSqlDriver::InsertStatement, d->tableName,
                                                rec, prepStatement);

    if (stmt.isEmpty()) {
        d->error = QSqlError(QLatin1String("No Fields to update"), QString(),
                                 QSqlError::StatementError);
        return false;
    }

    return d->exec(stmt, prepStatement, rec);
}

To copy to clipboard, switch view to plain text mode 

You can test this in your code:

...
ok = tableModel->insertRows(0, 1, QModelIndex());
Q_ASSERT(ok);
 
   QSqlRecord record = tableModel->record(0);       // retrieve inserted record
   QString stmt = db.driver()->sqlStatement(QSqlDriver::InsertStatement, "Table1", record, false);     // test in source code above
   qDebug() << stmt;                               //  QString stmt above is empty which causes your assert failure
   record.setValue(0,"inserted item");                       
   tableModel->setRecord(0,record);                // record is no longer empty 
   stmt =  db.driver()->sqlStatement(QSqlDriver::InsertStatement, "Table1", record, false);  // test modified record
   qDebug() << stmt;                              // stmt !isEmpty so submitAll returns true 
 
ok = tableModel->submitAll();
Q_ASSERT_X(ok, "submitAll", qPrintable(tableModel->lastError().text()));
...

...
ok = tableModel->insertRows(0, 1, QModelIndex());
Q_ASSERT(ok);
       
   QSqlRecord record = tableModel->record(0);       // retrieve inserted record
   QString stmt = db.driver()->sqlStatement(QSqlDriver::InsertStatement, "Table1", record, false);     // test in source code above
   qDebug() << stmt;                               //  QString stmt above is empty which causes your assert failure
   record.setValue(0,"inserted item");                       
   tableModel->setRecord(0,record);                // record is no longer empty 
   stmt =  db.driver()->sqlStatement(QSqlDriver::InsertStatement, "Table1", record, false);  // test modified record
   qDebug() << stmt;                              // stmt !isEmpty so submitAll returns true 

ok = tableModel->submitAll();
Q_ASSERT_X(ok, "submitAll", qPrintable(tableModel->lastError().text()));
...

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

The QSqlTableModel code will not attempt to "update" a row that has been inserted but not modified. It strikes me that, even if it worked as you expect, the empty row in this circumstance violates the NOT NULL constraint placed on the Parent column. Setting that column of the blank row to zero allows the insert to go ahead.

[Al_]

@norobro: many thanks, this solved it. It also means that I need to use QSqlTableModel (as in the example provided) and not the more general case of any derivative of virtual QAbstractTableModel; however, I now realize that in that general case no guarantee is given that the non-abstract table model class supports insertions.

@ChrisW67: yes, this is true for the first (commented out) version of the table definition, but not for the not-commented.

Al_