QSqlRelationalTableModel with QDataWidgetMapper does not save to SQLite3 database

[A Former User]

Hey,
I have the following problem. I want to write my data back to my SQLite database via a QDataWidgetMapper. Let's start with the code:

My QSqlRelationalTableModel:

QSqlRelationalTableModel *DatabaseManager::fungusTable() const
{
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel();
    model->setTable("Pilze");
    const int redlistIndex = model->fieldIndex("RListID");
    model->setRelation(redlistIndex,QSqlRelation("RListe", "ID", "Zuordnung"));
    model->setJoinMode(QSqlRelationalTableModel::LeftJoin);
    model->setSort(1, Qt::AscendingOrder);
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();
    while (model->canFetchMore()) {
        model->fetchMore();
    }
    return model;
}

Then in my mainwindow.cpp I have the following in the construtctor:

[...]

mFungusMapper = new QDataWidgetMapper;
mFungusProxyModel = new QSortFilterProxyModel;

mFungusDataModel = mDatabaseManager->fungusTable();
mFungusProxyModel->setSourceModel(mFungusDataModel);

mFungusMapper->setModel(mFungusDataModel);
mFungusMapper->setItemDelegate(new QSqlRelationalDelegate(mFungusMapper));
mFungusMapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
mFungusMapper->addMapping(ui->number, 0, "text");
mFungusMapper->addMapping(ui->fullname, 1);
mFungusMapper->addMapping(ui->genus, 2);
mFungusMapper->addMapping(ui->kind, 3);
mFungusMapper->addMapping(ui->family, 4);
mFungusMapper->addMapping(ui->order, 5);
mFungusMapper->addMapping(ui->noteTextfield, 6);
mFungusMapper->addMapping(ui->redListSelection, 7);

[...]

In my application I have a QListView where I select the appropriate record. If I then click on an entry in the QListView, my data also appears in the QLineEdits, etc.. The function to do this looks like this:

void MainWindow::on_fungusList_pressed(const QModelIndex &index)
{
    mFungusMapper->setCurrentModelIndex(mFungusProxyModel->mapToSource(index));
}

Furthermore I have a button. When I click on it, the following should be executed:

void MainWindow::on_editButton_clicked()
{
    if(!isEditModeActivated())
    {
        activateEditMode();
    }
    else
    {
        mFungusMapper->submit();
    }
}
    

My model is also updated in the QListView, but is not written to the SQLite database. Do I need to pass the QSqlRelationalTableModel (mFungusDataModel) or the QSortFilterProxyModel (mFungusProxyModel) to the mapper?

And what do I do wrong?

Thanks for your help :)

[JonB]

@Gabber
Your code looks reasonable to me. You say the model is updated. I would start by doing a mFungusMapper->model()->submit() after mFungusMapper->submit();. Does that get anything into the database? Get the submitting into the database level functioning, then work back up the mapper.

[A Former User]

@JonB said in QSqlRelationalTableModel with QDataWidgetMapper does not save to SQLite3 database:

mFungusMapper->model()->submit() after mFungusMapper->submit();

Unfortunately, that does not work either. The data is not written to the database. The model is still updated. Do you have another idea what this can be?

[JonB]

@Gabber
Remove the QSqlRelationalTableModel, and the QSortFilterProxyModel and QDataWidgetMapper, and retry with a plain QSqTableModel. Does that work?

Then add each of the others back in till you hit a problem. Find out which of them matters and which is not relevant.

[A Former User]

@JonB

I am again a little further come by your tip with the try. I have changed the following:

void MainWindow::on_fungusList_pressed(const QModelIndex &index)
{
    mFungusMapper->setCurrentIndex(index.row());
}

And:

void MainWindow::on_editButton_clicked()
{
    if(!isEditModeActivated())
    {
        activateEditMode();
    }
    else
    {
        mFungusMapper->submit();
        mFungusDataModel->submitAll();

        deactivateEditMode();
    }
}

In my constructor of mainwindow

        mFungusMapper->addMapping(ui->redListSelection, mFungusDataModel->fieldIndex("Zuordnung"));

Now the data is also written to the database

I have a second problem. If I remove the text from a QLineEdit so that the QLineEdit field is empty and contains no letters, spaces or other characters, an empty string is written to the database instead of NULL.

Do you have any idea how I can solve these problem?

[JonB]

@Gabber
Bit by bit.

In the long run I don't think you should have the mFungusDataModel->submitAll(); as well as the mFungusMapper->submit();. That was for you to progress/debug, I think the docs say the mapper submit should do it all. Get it to that point.

EDIT
Qt docs QDataWidgetMapper::submit() say

Finally, the model's submit() method is invoked.

But https://stackoverflow.com/a/36456863/489865 says

So, to make changes in the database I had to call statusModel->submitAll() after calling mapper->submit().

So either way, you need to test.

I believe that to get NULL into the database you need to have a value of QVariant(), an invalid variant. You could test if that is true and works via setData(QVariant()) directly into the model and see what you get.

Then you just need to write some code to recognise the line edit is empty and set the data to QVariant() instead of "". For which you need a custom delegate. And I think a very old thread from 2010, Custom QLineEdit to store NULL with QDataWidgetMapper, might just do you :)

Separately, when developing the first thing you want to do is write all available error checking as you go along. Those submit()s return success/failure, which you want to be reporting.

[A Former User]

@JonB said in QSqlRelationalTableModel with QDataWidgetMapper does not save to SQLite3 database:

QDataWidgetMapper::submit()

I managed that when I call QDataWidgetMapper->submit() the data is written to the database. The code for this looks like this:

In my constructor of mainwindow.cpp I have this code section

   mFungusMapper = new QDataWidgetMapper;
   
   mFungusProxyModel = new QSortFilterProxyModel;
  
   mFungusDataModel = mDatabaseManager->fungusTable();

   mFungusProxyModel->setSourceModel(*&mFungusDataModel);
   mRedlistModel = mFungusDataModel->relationModel(mFungusDataModel->fieldIndex("Zuordnung"));

   mFungusMapper->setModel(mFungusDataModel);
   mFungusMapper->setItemDelegate(new QSqlRelationalDelegate(mFungusMapper));
   mFungusMapper->addMapping(ui->number, mFungusDataModel->fieldIndex("ID"), "text");
   mFungusMapper->addMapping(ui->fullname, mFungusDataModel->fieldIndex("Vollname"));
   mFungusMapper->addMapping(ui->genus, mFungusDataModel->fieldIndex("Gattung"));
   mFungusMapper->addMapping(ui->kind, mFungusDataModel->fieldIndex("Art"));
   mFungusMapper->addMapping(ui->family, mFungusDataModel->fieldIndex("Familie"));
   mFungusMapper->addMapping(ui->order, mFungusDataModel->fieldIndex("Ordnung"));
   mFungusMapper->addMapping(ui->noteTextfield, mFungusDataModel->fieldIndex("Anmerkung"));
   mFungusMapper->addMapping(ui->redListSelection, mFungusDataModel->fieldIndex("Zuordnung"));


   ui->fungusList->setModel(mFungusProxyModel);
   ui->fungusList->setModelColumn(mFungusDataModel->fieldIndex("Vollname"));

   ui->redListSelection->setModel(mRedlistModel);
   ui->redListSelection->setModelColumn(mRedlistModel->fieldIndex("Zuordnung"));
   ui->redListSelection->setCurrentIndex(-1);

My function fungusTable() looks like this:

QSqlRelationalTableModel *DatabaseManager::fungusTable() const
{
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel;
    model->setTable("Pilze");
    model->setRelation(model->fieldIndex("RListID"), QSqlRelation("RListe", "ID", "Zuordnung"));
    model->setJoinMode(QSqlRelationalTableModel::LeftJoin);
    model->setSort(1, Qt::AscendingOrder);
    model->select();
    while (model->canFetchMore()) {
        model->fetchMore();
    }
    return model;
}

This is my on_fungusList_pressed function:

void MainWindow::on_fungusList_pressed(const QModelIndex &index)
{
    const QString redlist = mFungusProxyModel->data(index.siblingAtColumn(7)).toString();
    if(redlist.isEmpty()){
        ui->redListSelection->setCurrentIndex(-1);
    }
    mFungusMapper->setCurrentModelIndex(mFungusProxyModel->mapToSource(index));;
}

When I press the button to save to database I do only:

mFungusMapper->submit()

The only thing I still have to solve is the one with NULL in the database. For this I will try your posted links.

Thanks for your help :)

[A Former User]

@JonB
I have another question about the link. There a setItemDelegate is called. How does something like this work in my case since I already call a setItemDelegate based on the QSqlRelationalTableModel?

@Gabber said in QSqlRelationalTableModel with QDataWidgetMapper does not save to SQLite3 database:

mFungusMapper->setModel(mFungusDataModel);
mFungusMapper->setItemDelegate(new QSqlRelationalDelegate(mFungusMapper));

Can I use multiple setItemDelegate on one QDataWidgetMapper?

[JonB]

@Gabber
I haven't used delegates on QDataWidgetMapper, only on QTableView. That can gave delegates for specific rows/columns, as well as overall.

QDataWidgetMapper needs its own instance of a delegate (not one assigned to e.g. a view). It only allows one delegate.

The QSqlRelationalDelegate just adds the createEditor() & setModelData() methods to display a QComboBox when editing.

Remember than when these methods are called they are passed a const QModelIndex &index for the cell item they are editing. So you can always inspect index.column() to see whether it's one of your foreign-key columns when needs the combobox or not.

You could sub-class, or write code to cope with foreign-key/non-foreign-key columns in the delegate, or do your own combobox, or call one delegate's code from the other, or whatever seems easiest to achieve what you want for different columns. You will have to have just one overall instance of a delegate on your data widget mapper, and have its code deal with different columns as required.

[A Former User]

Everything works now as expected. You can find the solution here