Getting QSqlRecord right
I have used QT for a few projects recently, and one place where I really struggled was when I wanted to insert new rows in a database. Eventually I worked around the problem by manually adding the records with an INSERT INTO
statement. But this require the model to call select
, and reset the model. That's not optimal.
So today I took the time to look deeper into the issue. My problem was a few flawed assumptions, and the fact that QSqlRecord::setValue
simply ignores it when you try to assign value to a field it don't have. (Initially, I default-constructed QSqlRecord
, called QSqlRecord::setValue()
and ended up with an empty data-container. Since QT hides the implementation of this class, the problem was not even visible in the debugger).
My table contains NOT NULL
columns, and columns with constraints, so a recored can only be inserted if it contains actual, valid data.
The important things were:
QSqlRecord
must be constructed fromQSqlDatabase::record()
- The
QSqlTableModel::EditStrategy
must beOnRowChange
orOnManualSubmit
- If the
QSqlTableModel::EditStrategy
isOnManualSubmit
, we must callsubmitAll()
- All required data must be assigned to the
QSqlRecord
So, to take one example, I have a slot in a QSqlTableModel
derived class that is used to submit data into the database.
void ContactsModel::onContactCreated(const Contact &contact) { Strategy strategy(*this, QSqlTableModel::OnManualSubmit); QSqlRecord rec{getDb().record(tableName())}; rec.setValue(h_uuid_, QUuid().toString()); if (!contact.nickname.isEmpty()) { rec.setValue(h_nickname_, contact.nickname); } if (contact.group.isEmpty()) { rec.setValue(h_group_, QStringLiteral("uncategorized")); } else { rec.setValue(h_group_, contact.group); } rec.setValue(h_initiated_by, contact.getInitiatedBy()); rec.setValue(h_hash_, contact.hash); rec.setValue(h_pubkey_, contact.pubkey); rec.setValue(h_address_, contact.address); rec.setValue(h_name_, contact.name); rec.setValue(h_initiated_by, contact.getInitiatedBy()); if (!contact.notes.isEmpty()) { rec.setValue(h_notes_, contact.notes); } if (!contact.avatar.isNull()) { rec.setValue(h_avatar_, DsEngine::imageToBytes(contact.avatar)); } rec.setValue(h_created_, QDateTime::currentDateTime().toTime_t()); if (!insertRecord(-1, rec) || !submitAll()) { qWarning() << "Failed to add contact: " << contact.name << this->lastError().text(); return; } qDebug() << "Added contact " << contact.name << " to the database"; }
Here I change the edit strategy to OnManualSubmit
, create an instance of QSqlRecord
, assign the data, and then I call insertRecord()
and submitAll()
.
I prefer to change the edit-strategy when I add a new record, and use this helper-class to make sure it is set back when I'm done:
class Strategy { public: Strategy(QSqlTableModel& model, QSqlTableModel::EditStrategy newStrategy) : model_{model}, old_strategy_{model.editStrategy()} { model_.setEditStrategy(newStrategy); } ~Strategy() { model_.setEditStrategy(old_strategy_); } private: QSqlTableModel& model_; const QSqlTableModel::EditStrategy old_strategy_; };
The full code is available here;