QDataWidgetMapper with SQL Table AUTO_INCREMENT

ZNohre

All,

Is there a method to connect a QDataWidgetMapper to present an AUTO_INCREMENT primary key field (IDENTITY for SQL Server, which is what I'm using)?

I'm in the process of optimizing performance on my desktop app by replacing my UUID primary keys from testing with AUTO_INCREMENT fields to speed up filters and joins.

Testing with one of my simpler tables I'm getting the below error:

Error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.

which is a result of this line of code:

m_mapper->addMapping(ui->idEdit, MyTable::DataRole::Id);

If I comment this out, the query is executed properly and inspecting the table with SQL Server Management Studio shows the auto increment is working as expected.

There are many cases where I don't care about presenting the ID, but several where the ID field is used to filter a separate model/view. I also realize this could be a SQL design issue and for these specific instances, could retain the UUID key to filter subsequent tables.

Appreciate any insights.

Edit: PS I had tried connecting the ID field to a QLabel in the hopes it would be read only. When QDataWidgetMapper submits though it looks like it's still pulling the text in the label to submit back to the database.

ZNohre

@JonB Great insight! I had initially implemented the proposed solution by @SGaist which definitely works but doubles up calls to QDataWidgetMapper::setCurrentIndex(int) when navigating the records.

By subclassing QSqlTableModel and overriding the protected function insertRowIntoTable(const QSqlRecord &values) to modify the record's generated flag before the actual insertion to the database this can be accomplished with one mapper via the below code:

bool MySqlModel::insertRowIntoTable(const QSqlRecord &values)
{
    QSqlRecord temp = values;
    temp.setGenerated(MySqlTable::DataRole::Id, false);

    return QSqlTableModel::insertRowIntoTable(temp);
}

Appreciate the help here.

SGaist

Hi,

I don't think you can as is. One work around I can see is to use a secondary mapper for the read-only fields so you still have the benefits of the mapper and can use the main one to commit the data to the database.

JonB

@ZNohre said in QDataWidgetMapper with SQL Table AUTO_INCREMENT:

Edit: PS I had tried connecting the ID field to a QLabel in the hopes it would be read only. When QDataWidgetMapper submits though it looks like it's still pulling the text in the label to submit back to the database.

It is submitting the value of an auto-incrementing ID in, say, an INSERT statement generated from a QDataWidgetMapper? Although I have not used this myself, did you set the generated flag on the column/field in the model? void QSqlRecord::setGenerated(QAnyStringView name, bool generated)

Sets the generated flag for the field called name to generated. If the field does not exist, nothing happens. Only fields that have generated set to true are included in the SQL that is generated by QSqlQueryModel for example.

I would hope that QDataWidgetMapper would respect that?

ZNohre

@JonB Great insight! I had initially implemented the proposed solution by @SGaist which definitely works but doubles up calls to QDataWidgetMapper::setCurrentIndex(int) when navigating the records.

By subclassing QSqlTableModel and overriding the protected function insertRowIntoTable(const QSqlRecord &values) to modify the record's generated flag before the actual insertion to the database this can be accomplished with one mapper via the below code:

bool MySqlModel::insertRowIntoTable(const QSqlRecord &values)
{
    QSqlRecord temp = values;
    temp.setGenerated(MySqlTable::DataRole::Id, false);

    return QSqlTableModel::insertRowIntoTable(temp);
}

Appreciate the help here.

JonB

@ZNohre
I am glad this has helped/turned out to be solution.

Not long ago we had a report from someone that for some function call, and I cannot remember which, it was not possible to alter values, despite the Qt documentation stating that it should be. It was something like insertRowIntoTable(), might have been updateRowInTable(). If yours works that is fine. Otherwise if you run into such a problem then instead of subclassing and overriding insertRowIntoTable() you can call setGenerated() on the QSqlRecord prior to your explicit calls to e.g. QSqlTableModel::insertRecord().