PDA

View Full Version : Unable to perform setValue on QSqlRecord when model has relations



stevebakh
20th March 2010, 20:45
Hi,

I have a QSqlRelationalTableModel model and I'm trying to create a record. So I do model->record() to get a blank record, however, if I attempt to do record.setValue("field", "value") on a column that has a relation, it fails (or rather, no value is set). If I remove the relation from the table model, it works fine. Any ideas?

Thanks!

graciano
20th March 2010, 21:43
Did you check if the "value" used exists in the referenced table, where i suppose "field" is the PK?
I experienced similar problems because of this detail.

stevebakh
21st March 2010, 00:13
Hi Graciano,

I did check that the value existed in the related table before trying to set it. I didn't think it would matter anyway (not until attempting to commit the data to the database at least), but still, no joy.

Here's an example of what I'm doing:



model->setTable("foo");
model->setRelation(model->fieldIndex("bar_id"),
QSqlRelation("bar", "id", "bazcolumn"));

// fetch a new, empty record
QSqlRecord row = model->record();

// The following fails to set 123 for the bar_id column. If I comment
// out the call to setRelation() above, this works fine. Assume that
// 123 is a valid id that exists in the bar table.
row.setValue("bar_id", 123);

graciano
21st March 2010, 10:31
Well ... it's hard to say without looking at the whole thing.
Here is a similar example i'm working on ( http://www.box.net/shared/f511adk6y3 )
You find your line of code in line 54 of the cidadaodialog.cpp file.
Important note: I'm using MySql Workbench to manage the database (mva1.mwb)

wysota
21st March 2010, 10:35
As far as I remember for relation fields you have to use the foreign key (id) instead of values from the related table when changing the record.

stevebakh
21st March 2010, 13:02
As far as I remember for relation fields you have to use the foreign key (id) instead of values from the related table when changing the record.

Hi, could you elaborate on this point please?

I've tried to set a value that matches the foreign key value, but I'm guessing that's not what you meant? Is there some other way of doing this?

Also, Graciano, thanks for sharing some code, I'll take a look at it now! :)

stevebakh
21st March 2010, 13:29
I've managed to narrow the problem down some more...

The reason no data is set is because the column seemingly doesn't exist in the QSqlRecord when there's a relation applied to it.



// when the relation isn't set, the following prints 4
// if a relation IS set, it prints -1
qDebug() << record.indexOf("bar_id");


It's simply not added to the list of fields, for whatever reason. :(

wysota
21st March 2010, 15:19
What does this print?


for(int i=0;i<record.count();++i){
qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

stevebakh
21st March 2010, 17:50
What does this print?


for(int i=0;i<record.count();++i){
qDebug() << "Field " << i << "is called" << record.fieldName(i);
}

Oooh, this gives interesting results. It turns out that the field name changes when adding the relation, it's appended with "_2", so "foo_id_2".

Here's some code as an example:



QSqlRelationalTableModel *model = new QSqlRelationalTableModel(this);
model->setTable("foo");
model->setRelation(model->fieldIndex("bar_id"), QSqlRelation("bar", "id", "somecolumn"));
model->select();

QSqlRecord row = model->record();
// the following fails to set the value
row.setValue("bar_id", 123);

// because the following returns -1
qDebug() << row.indexOf("bar_id");

for (int i = 0; i < row.count(); i++) {
qDebug() << "field: " << i << " is called: " << row.fieldName(i);
}

// the above outputs the following:
// field: 0 is called: "blah"
// field: 1 is called: "blah1"
// field: 2 is called: "blah2"
// field: 3 is called: "bar_id_2" <<<< The _2 is appended only when a setRelation() is called on the model.


So, doesn't this happen if you try my example code? Is this standard / normal behaviour?

graciano
21st March 2010, 19:22
for(int i=0;i<record.count();++i){
qDebug() << "Field " << i << "is called" << record.fieldName(i);
}


I tested this code and, for the foreign key field, i get: REFERENCEDTABLENAME_REFERENCEDFIELDNAME.
Nothing strange here!?

In you exemple the filed name show be "bar_somecolumn".

wysota
21st March 2010, 22:08
Anyway if you referenced the column by its index and not by its name, you wouldn't have even noticed there was a name change ;)

stevebakh
22nd March 2010, 01:43
Anyway if you referenced the column by its index and not by its name, you wouldn't have even noticed there was a name change ;)

That's very true, however, I figured it would be bad practice to just use magic numbers to refer to arbitrary columns. What if the table structure changes?

I could define some enum values that represent each column and use those, but it's the same problem. If the table structure changes, the app breaks and needs recompiling. Hmmm, I just thought about it, I could/should probably use the model instead of the record to find the column number. record.setValue(model.fieldIndex("foo_id"), 123); Le sigh...

Thanks guys! Lesson learnt, knowledge earnt.

wysota
22nd March 2010, 09:59
That's very true, however, I figured it would be bad practice to just use magic numbers to refer to arbitrary columns. What if the table structure changes?
I don't think it would change behind your back. If it did, the change could as well be that a column is removed at all. So we can assume that any change like that would only require you to renumber some enum and rebuild the app.