Problems with QSqlTableModel

django.Reinhard

Hi,

I'm working on some master-detail editor with Sqlite backend and usage of QSqlTableModel works fine for displaying table data. Insert of new record works as well.

My problems exists with updating data.
My code looks like:

QSqlRecord r = model.record(row2Edit);

r.setValue("fieldName", value);
...

if (!model.submitAll()) raiseError(model.lastError);

When I debug code, everything looks as expected and submitAll returns success - so no error message.
But changes don't make it into database.

What am I missing?

P.S.: My wip

JonB

@django-Reinhard
Since we know this works please recheck your "But changes don't make it into database." Close your connections, exit the Qt application, verify which file/database you are really writing to, verify what value you are putting into which row of which table.

django.Reinhard

Hi,

@JonB said in Problems with QSqlTableModel:

Since we know this works

I'm quite sure, you got me wrong.

I did not blame anybody else but me. Therefore I asked about what am I missing.

The point is, I already checked all mentioned points multiple times before posting - and I run out of ideas, where to search for.
I dropped database, created it new but application fail.

Another problem: whatever I do wrong, I never got an error message in lastError()

So possibly I have to install something for Sqlite? Don't know.
Its not the first time, I work with databases.
Using java I coded multiple database apps, which all work fine.
Its just the first time, I use Qt for that.

JonB

@django-Reinhard
OK, I've got it.

You are assuming that model.record() returns you a reference to the actual record, so that you can go QSqlRecord::setValue() to make a change on it to be sent back to the database. That's not how it works. QSqlRecord::record() returns you an in-memory copy of your record. After you have updated it with setValue() you need to call QSqlTableModel::setRecord(int row, const QSqlRecord &values) with your changed QSqlRecord to push it back to the model:

Applies values to the row in the model

Do that and then submitAll().

django.Reinhard

Hi,

thank you for your support.

Obviously I have a big misunderstanding. I read that QSqlRecords are internally shared (can't find this docs actually). Therefore I thought to use the editor as a kind of double buffer.

And yes, I thought QSqlRecord is a link to database record.

I'll try your advice

django.Reinhard

Hi,

your recommendation works as expected.

May be you can help me improve my understanding about QSqlTableModel ...

When I realized, that QSqlTableModel is a subclass of QAbstractItemModel, which is a baseclass of TreeModels too, I thought I could derive my treemodels from QSqlTableModel and so need only one container class and one item class.

But that does not work. QTreeView does not show any tree, when the model is a subclass of QSqlTableModel. When I change ancestor to QAbstractItemModel, QTreeView works as expected.

What is the trigger for QTreeView to render a Tree? Is it possible to overwrite that trigger in a subclass of QSqlTableModel?

JonB

@django-Reinhard
I am going out so I don't have time to respond! Yes, it is perfectly possible to make a QSqlTableModel the model for a QTreeView. What you need for the latter is a model which exposes parentage, so that it knows what are nodes and what are leaves. Google for QTreeView QSqlTableModel and look at the discussions/examples.

django.Reinhard

@JonB said in Problems with QSqlTableModel:

I am going out so I don't have time to respond!

Never mind! I'm not in a hurry.
Thank you for your help so far!

@JonB said in Problems with QSqlTableModel:

Yes, it is perfectly possible to make a QSqlTableModel the model for a QTreeView. What you need for the latter is a model which exposes parentage,

Don't know whether I understand you right. Of cause the parentage is exposed. I started with editableTreeModel-sample as template. I implemented the tree as subclass of QAbstractItemModel which worked ootb.
Later on I changed the treemodel to a subclass of QSqlTableModel. Then only a list was displayed (with the same data in my childclass).
Then I changed the code, so that I could switch between QAbstractItemModel and QSqlTableModel as parent.
Treeview showed a tree only with QAbstractItemModel as parent.

Actually I have another problem with QSqlTableModel:
For new records, I have this workflow:

if (model->insertRows(model->rowCount(), 1) {
   QSqlRecord r = model->record(model->rowCount() - 1);
   r.setValue("id", model->rowCount() - 1);
   r.setValue("roField", someValue);
 
   // do editing
   }

then on trigger after editing as finished:

QSqlRecord r = model->record(model->rowCount() - 1);

editor->getChanges(r);
model->setRecord(model->rowCount() - 1, r);
if (!model->submitAll()) raiseError model->lastError().text();

That second part is the same stuff for changed and new records. Whereas it works for changed records, it fails with new records. This time I do have an error message:
"No Fields to update"

... and I have no idea, what the database will tell me.
When I debug the code, all entered fields are in the record and all mandatory fields are filled ...

How can I find out, what goes wrong?

JonB

@django-Reinhard
So for the QSqlTableModel for QTreeView you created a QAbstractProxyModel to interpose, did you? As I said Googling seems to show some examples to start from. I don't know, but https://www.qtcentre.org/threads/69098-Viewing-QSqlTableModel-in-QTreeView-through-a-proxy-model seemed to show a working example?

Whereas it works for changed records, it fails with new records.

I'm not sure what you mean here. New records will need to be added via QSqlTableModel::insertRecord(), you did that, did you? And I imagine they cannot be updated till after they have been submitted/committed to do the SQL INSERT statement because till then they cannot have an UPDATE statement; I would guess that if you update the in-memory record after inserting but before committing then, assuming it works, it won't do an UPDATE, rather it will include the changed fields in the INSERT statement when that gets submitted?

django.Reinhard

@JonB said in Problems with QSqlTableModel:

So for the QSqlTableModel for QTreeView you created a QAbstractProxyModel to interpose, did you?

No! Not at all.

Sorry, but looks like you didn't read my post.
Just guessing what I did is so uncool ... :(

I told you, that I followed the QT-example editableTreeModel, so my model looks like:


class CategoryTreeModel : public QAbstractItemModel // QSqlTableModel
{
  Q_OBJECT

public:
  ...
};

... and for testing I switched between QAbstractItemModel as parent and QSqlTableModel as parent.
Lateron I add a QSqlSortedFilterProxy to enable Treeview sorting.

@JonB said in Problems with QSqlTableModel:

As I said Googling seems to show some examples to start from.

Well, I found that link too, but to me it looked like somebody is asking for help, but noone did help. So I didn't think, that code was a good starting point.

Did you try his code? Does it work?

@JonB said in Problems with QSqlTableModel:

you did that, did you?
I posted my code, so no, I did not use insertRecord, but insertRows ...

I tried to add a submitAll directly after insertRows, but I get the same errormessage: "No Fields to update".

Well, I think, as Sqlite does not provide any helpful errormessage, I have to use a real database.

Sadly Qt docs don't write anything about all the gotchas around database layer usage.

JonB

@django-Reinhard said in Problems with QSqlTableModel:

Sorry, but looks like you didn't read my post.

I did read your post. I had previously referred you to web posts for QSqlTableModel QTreeView, and in particular a particular thread showing a working example using a proxy model, so my impression is that is the way to go and I thought you would agree. Anyway, I don't know, up to you, you seem to know what you're doing better than I.

Well, I think, as Sqlite does not provide any helpful errormessage, I have to use a real database.

Lots of people us SQLite without problem.

Maybe you need someone other than me to answer.

django.Reinhard

Hi,

@JonB said in Problems with QSqlTableModel:

in particular a particular thread showing a working example using a proxy model ...

I tried that code and it turned out, that it is the same as the model I already have (subclass of QAbstractItemModel). QAbstractProxyModel is a subclass of QAbstractItemModel, so ...

My question was, whether it is possible to create a model suitable for treeview as a child of QSqlTableModel. Apparently it is not possible.
Proxy uses a QSqlTableModel - as I do with my model - so nothing new in that linked thread.

I changed my editor to use insertRecord and setRecord only. No things seem to work. For me, this usecase was not obvious from documentation.

@JonB said in Problems with QSqlTableModel:

Lots of people us SQLite without problem.

Apparently I'm not cute enuf.

... any way: thank you for your attention and support!