Published:

Jarle Aase

Getting QSqlRecord right

bookmark 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:

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;