Query when submitAll() of QSqlTableModel fails in OnManualSubmit mode
-
Hi,
After reading the documentation I had understood that when submitAll() fails in OnManualSubmit mode, a transaction can be rolled back, the problem corrected in the table model, and then submitAll() tried again. Is this not the case?
I tried the following simple code after having an issue with this and the result isn't what I'd expect to see (output shown in the second post). I'd of expected all 3 rows to be present in the database but only 2 are. Could someone please confirm if I've misunderstood this and if so, what should I do to prevent the missing row.
@
//Create a table model in manual submit mode.
QSqlTableModel testModel;
testModel.setTable("customer");
testModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
//Load the model (currently empty).
testModel.select();
//Display the row count.
qDebug() << "Current Customers:" << testModel.rowCount();
//Insert 3 customers into the model. The second one inserted is incomplete and will cause
//submitAll() to fail.
for(int i = 0; i < 3; i++)
{
testModel.insertRow(0);
testModel.setData(testModel.index(0, testModel.fieldIndex("customer_id")), 100 + i);
testModel.setData(testModel.index(0, testModel.fieldIndex("customer_id_rev")), 1);
if(i != 1)
{
QString custName("Customer_");
custName.append(QString::number(100 + i));
testModel.setData(testModel.index(0, testModel.fieldIndex("customer_name")), custName);
}
}
//Display the model before attempting submitAll().
qDebug() << "Current Customers (after insert with incomplete record and before submit):" << testModel.rowCount();
for(int i = 0; i < testModel.rowCount(); i++)
{
qDebug() << testModel.record(i);
}
//Create a transaction and submit the model.
testModel.database().transaction();
if(!testModel.submitAll())
{
//Display the expected error.
qDebug() << testModel.lastError().databaseText();
//Rollback to remove any rows that were inserted into the database prior to the error.
testModel.database().rollback();
//Add the missing name to the problematic record.
testModel.setData(testModel.index(1, testModel.fieldIndex("customer_name")), QString("Customer_101"));
//Display the model before attempting submitAll() again.
qDebug() << "Current Customers (all records now complete):" << testModel.rowCount();
for(int i = 0; i < testModel.rowCount(); i++)
{
qDebug() << testModel.record(i);
}
//Create a new transaction and submit the model.
testModel.database().transaction();
if(testModel.submitAll())
{
//Commit the transaction.
testModel.database().commit();
//Display the model. Only 2 customers have been added.
qDebug() << "Current Customers (model submitted):" << testModel.rowCount();
for(int i = 0; i < testModel.rowCount(); i++)
{
qDebug() << testModel.record(i);
}
}
else
{
testModel.database().rollback();
}
}
@ -
Output I receive:
Current Customers: 0
Current Customers (after insert with incomplete record and before submit): 3
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "102"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: yes, typeID: 1043, autoValue: false, readOnly: false) "Customer_102"
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "101"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: no, typeID: 1043, autoValue: false, readOnly: false) ""
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "100"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: yes, typeID: 1043, autoValue: false, readOnly: false) "Customer_100""ERROR: null value in column "customer_name" violates not-null constraint
DETAIL: Failing row contains (101, 1, null).
(23502)"Current Customers (all records now complete): 3
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: no, typeID: 23, autoValue: false, readOnly: false) "102"
1: QSqlField("customer_id_rev", int, length: 4, generated: no, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: no, typeID: 1043, autoValue: false, readOnly: false) "Customer_102"
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "101"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: yes, typeID: 1043, autoValue: false, readOnly: false) "Customer_101"
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "100"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: yes, typeID: 1043, autoValue: false, readOnly: false) "Customer_100"Current Customers (model submitted): 2
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "101"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: yes, typeID: 1043, autoValue: false, readOnly: false) "Customer_101"
QSqlRecord(3)
0: QSqlField("customer_id", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "100"
1: QSqlField("customer_id_rev", int, length: 4, generated: yes, typeID: 23, autoValue: false, readOnly: false) "1"
2: QSqlField("customer_name", QString, length: 50, generated: yes, typeID: 1043, autoValue: false, readOnly: false) "Customer_100" -
Hi,
Not really an answer but why don't you do the input validation directly in your widget ? To me trying to send wrong data to the database and recover from it is not a good practice. Sure it can happen but why not do everything you can to ensure that the data is good in the first place since you know what is allowed and not in your database ?
-
Hi,
It's not really possible to fully validate it (i.e. what if the problem is due to a duplicate entry in the row rather than a null entry?). The only way to make sure it wouldn't fail is to check every entry in the database before trying to commit.
The above is just a simple example to demonstrate the issue. I was hoping to find out if I've understood the documentation correctly.
Thanks.
-
I agree it's not always possible to catch every mistake. However writing your logic such as it's relying on the database to handle validation is a bad idea.
Did you check the recommendation from the "rollback":http://doc.qt.io/qt-5/qsqldatabase.html#rollback method documentation ?
-
Hi,
Are you referring to the rollback() failing if there is an active query? The rollback is not failing. This is what I think is happening:
When the first submitAll() is called:
Customer102 is written to the database.
An attempt to write Customer101 to the database is made but fails because the name is null.
SubmitAll() fails.
At this point the database is rolled back (i.e. removing Customer102).
The missing name for Customer101 is added to the model (i.e. fixing the problem) and submitAll() called again. However, this time, the model appears to not write Customer102 to the database and instead writes just Customer101 and Customer100.
This isn't how I understood the documentation which states:
bq. Note: In OnManualSubmit mode, already submitted changes won't be cleared from the cache when submitAll() fails. This allows transactions to be rolled back and resubmitted without losing data.
I read the above as the row that had been written would not be cleared from the cache because submitAll() failed, which would then be written to the database again when submitAll() is called. Do you know if that is what the quote means or if I've misunderstood it?
Thanks.
-
I understand it the same way as you. However, did you call transaction on the QSqlDatabase instance before doing that ?
-
A bit late, but anyway...
I found a solution to your problem.
In my experience, always use:
testModel.insertRow(testModel.rowCount());
instead of:
testModel.insertRow(0); (which brings various kinds of problems)
and you'll never have a phantom record.
