Getting QSqlRecord right

1 min read

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 from QSqlDatabase::record()
• The QSqlTableModel::EditStrategy must be OnRowChange or OnManualSubmit
• If the QSqlTableModel::EditStrategy is OnManualSubmit, we must call submitAll()
• 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.

 1void ContactsModel::onContactCreated(const Contact &contact)
 2{
 3    Strategy strategy(*this, QSqlTableModel::OnManualSubmit);
 4    QSqlRecord rec{getDb().record(tableName())};
 5    rec.setValue(h_uuid_, QUuid().toString());
 6    if (!contact.nickname.isEmpty()) {
 7        rec.setValue(h_nickname_, contact.nickname);
 8    }
 9    if (contact.group.isEmpty()) {
10        rec.setValue(h_group_, QStringLiteral("uncategorized"));
11    } else {
12        rec.setValue(h_group_, contact.group);
13    }
14    rec.setValue(h_initiated_by, contact.getInitiatedBy());
15    rec.setValue(h_hash_, contact.hash);
16    rec.setValue(h_pubkey_, contact.pubkey);
17    rec.setValue(h_address_, contact.address);
18    rec.setValue(h_name_, contact.name);
19    rec.setValue(h_initiated_by, contact.getInitiatedBy());
20    if (!contact.notes.isEmpty()) {
21        rec.setValue(h_notes_, contact.notes);
22    }
23    if (!contact.avatar.isNull()) {
24        rec.setValue(h_avatar_, DsEngine::imageToBytes(contact.avatar));
25    }
26    rec.setValue(h_created_, QDateTime::currentDateTime().toTime_t());
27
28    if (!insertRecord(-1, rec) || !submitAll()) {
29        qWarning() << "Failed to add contact: " << contact.name
30                   << this->lastError().text();
31        return;
32    }
33
34    qDebug() << "Added contact " << contact.name << " to the database";
35}
36

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:

 1class Strategy {
 2public:
 3    Strategy(QSqlTableModel& model, QSqlTableModel::EditStrategy newStrategy)
 4        : model_{model}, old_strategy_{model.editStrategy()}
 5    {
 6        model_.setEditStrategy(newStrategy);
 7    }
 8
 9    ~Strategy() {
10        model_.setEditStrategy(old_strategy_);
11    }
12
13private:
14    QSqlTableModel& model_;
15    const QSqlTableModel::EditStrategy old_strategy_;
16};

The full code is available here;