Unable to perform setValue on QSqlRecord when model has relations

[stevebakh]

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]

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]

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);

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);

To copy to clipboard, switch view to plain text mode 

[graciano]

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]

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]

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]

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");

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

To copy to clipboard, switch view to plain text mode 

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

[wysota]

What does this print?

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

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

To copy to clipboard, switch view to plain text mode 

[stevebakh]

What does this print?

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

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

To copy to clipboard, switch view to plain text mode 

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.

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.

To copy to clipboard, switch view to plain text mode 

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

[graciano]

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]

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]

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]

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.