QSqlTableModel submit fails

MasterQ

Hi,

I read data from a database, alter some data fields and want to submit the changes.

reading correct data? check!
alter correct data? check!
data altered correctly? check!
submit? -->

submit fails (gives back false), so does submitAll. The lasError() is empty. I am now lacking any idea why this happens.

Where and how to get more information why the submit fails? Just a "false" and an empty error message seems not sufficient to come closer to a solution.

BTW.: the IsEditable flag is set in the models flag method!

Joachim

MasterQ

Since the code between the two classes to access the database are more or less the same (#2 is a slightly modified copy of #1) I now assumed that the problem is not coming from my code but from the database.

While the working version is writing to a database table the not working one tries to write to a database view. Changing this solves the issue.

Trying to modify the view by another tool gives the error message: a view cannot be modified. The backend is SQLite3.

I was not aware that sqlite do not support update commands to a view. For other databases this is not an issue. OK, I have to rethink my backend data models.

thnx so far for your support

JonB

@MasterQ
First thing to check is that you are doing the submit on the correct table model! I have seen cases where posters have (inadvertently) more than one model and (accidentally) perform some operations on one instance and other operations on another instance.....

Are you using manual submit or row/field change? What happens if you change that to check behaviour? Check isDirty() as you go along making changes? Are you just setting the table name for the QSqlTableModel or do you specify your own SQL quesry/select statement?

Are you able to produce an absolutely minimal example code which goes wrong for you?

Pl45m4

@MasterQ said in QSqlTableModel submit fails:

Where and how to get more information why the submit fails?

Through debugging?!

I am now lacking any idea why this happens.

How should we know? You don't provide any code about what you did exactly

MasterQ

@Pl45m4 said in QSqlTableModel submit fails:

@MasterQ said in QSqlTableModel submit fails:

Where and how to get more information why the submit fails?

Through debugging?!

Oh, come on.

I am debugging the whole day already. My question was about some general or standard means. One tool is lastError() but this gives no hint. The documentation gives only "false means no success". ...

I am now lacking any idea why this happens.

How should we know? You don't provide any code about what you did exactly

I was not asking what is wrong with my code but about debugging tools to find out by myself. Sry if I was misleading.

@JonB said in QSqlTableModel submit fails:

@MasterQ
First thing to check is that you are doing the submit on the correct table model! ...

this is checked and correct

Are you using manual submit or row/field change? What happens if you change that to check behaviour? Check isDirty() as you go along making changes? Are you just setting the table name for the QSqlTableModel or do you specify your own SQL quesry/select statement?

I am using OnManualSubmit
Dirty-Flag is checked and is true!
Changing the EditStrategy changes nothing, means no update of the database table

        qDebug() << "dirty? " << m_controller.EditDataModel()->isDirty();
        qDebug() << "editsubmit " << m_controller.editsubmit();

2024-10-09 15:40:04.616 [debug] dirty?  true
2024-10-09 15:40:04.616 [debug] editsubmit  false

with

bool BuildingsController::editsubmit() {
    return m_editdatamodel->submit();
}

Are you able to produce an absolutely minimal example code which goes wrong for you?

Puhh could be complicated! I do it the same way in another class and that's working. I cannot see a code difference between.

Let's ask some questions first about fundamentals. I am confused about the qt solution about database access.

I am reading data by

m_controller.EditDataModel()->setFilter(QString("id=%1").arg(id)); // set filter to unique primary key id
m_controller.EditDataModel()->select(); // gives one dataset back

But how to alter that data? I have two possibilities?

by setData

 m_controller.EditDataModel()->setData(indexName, ui->lineName->text(), Qt::EditRole);

or

by setRecord

auto _record = m_controller.EditDataModel()->record(0); // makes a copy
_record.field("Name").setValue(ui->lineName->text()); // alters copy
m_controller.EditDataModel()->setRecord(0, _record); //writes copy back

in case 1 I can see the data in the model were altered, submit gives false

in case 2 I cannot see any changed data in the model but submit gives true.

What is the right/better way? If this is clear to me, maybe I can do some further testing/debugging why the data are

not written back/submitted to the database
not altered

Pl45m4

@MasterQ said in QSqlTableModel submit fails:

I am debugging the whole day already. My question was about some general or standard means. One tool is lastError() but this gives no hint. The documentation gives only "false means no success".

Debugging is more than just checking the output of some error function :)

Puhh could be complicated! I do it the same way in another class and that's working. I cannot see a code difference between.

As usual: Break it down until both approaches behave the same... then add more details until it fails again.

Are you aware of:

`bool QSqlTableModel::setRecord(int row, const QSqlRecord &values)`

Applies values to the row in the model. The source and target fields are mapped by field name, not by position in the record.

Note that the generated flags in values are preserved to determine whether the corresponding fields are used when changes are submitted to the database. By default, it is set to true for all fields in a QSqlRecord. You must set the flag to false using setGenerated(false) for any value in values, to save changes back to the database.

For edit strategies OnFieldChange and OnRowChange, a row may receive a change only if no other row has a cached change. Changes are submitted immediately. Submitted changes are not reverted upon failure.

( https://doc.qt.io/qt-6/qsqltablemodel.html#setRecord )

bool BuildingsController::editsubmit() {
   return m_editdatamodel->submit();
}

Have you already tried submitAll() instead?

JonB

@MasterQ said in QSqlTableModel submit fails:

I do it the same way in another class and that's working. I cannot see a code difference between.

Well I think that is the $64k question! It sounds like you need to find the difference to see why they behave differently!

So far as I know (which may not be perfect) you can go down either the setRecord() or the setData() route and they should both work, per the documentation.

in case 1 I can see the data in the model were altered, submit gives false

in case 2 I cannot see any changed data in the model but submit gives true.

Agreed confusing!

For case #2 we know that QSqlTableModel offers revertAll() (works for manual submit). That implies to me that Qt must be keeping both the new values and the original values somewhere in order to revert. Depending on implementation we don't know for sure which of the two is stored in data(EditRole) and hence what you see in the model?

For manual submit I think you should be calling submitAll() rather than submit().

A few things:

Does the "bad" table have either generated columns or "unusual" columns like "blob"?
Which back end db are you using?
Try subclassing and overriding any/all of the protected functions (e.g. updateRowInTable() should be called when doing the actual db update) or connect to the beforeUpdate() signal.
In case you are having problems with updating existing rows/data, try the insert or delete row methods. Do they work?

MasterQ

Since the code between the two classes to access the database are more or less the same (#2 is a slightly modified copy of #1) I now assumed that the problem is not coming from my code but from the database.

While the working version is writing to a database table the not working one tries to write to a database view. Changing this solves the issue.

Trying to modify the view by another tool gives the error message: a view cannot be modified. The backend is SQLite3.

I was not aware that sqlite do not support update commands to a view. For other databases this is not an issue. OK, I have to rethink my backend data models.

thnx so far for your support

Pl45m4

@MasterQ said in QSqlTableModel submit fails:

Trying to modify the view by another tool gives the error message: a view cannot be modified. The backend is SQLite3.

In "regular" SQL / SQL Server you can insert add rows to a view, while in SQLite views are read-only.
I guess that's one of the drawbacks when using the "lite" version...

OK, I have to rethink my backend data models.

Maybe you can work around this by using the view to insert the data to the full underlying model/table directly, which then updates your view.
(Don't ask me how and if it also works with QSQL)