Updating a relational table view

Dan3460

I made this simple dialog to show the problem that i'm having, the dialog is just a QTableView and QSqlRelationalTableModel. The problem is that i cannot save the view, even when the data changed was not any of the related tables. But if i comment out the relations, as in the code below, i can update with no problem. Is there an extra step to save information when there are foreign keys in play?

PS. Just in case is important I'm running a MySql server across the network.

#include "xtest.h"
#include "ui_xtest.h"

XTest::XTest(QWidget *parent) : QDialog(parent), ui(new Ui::XTest)
{
  ui->setupUi(this);
  modelSuppliers=new QSqlRelationalTableModel(this);
  modelSuppliers->setTable("supplier");
  modelSuppliers->setEditStrategy(QSqlTableModel::OnManualSubmit);
  modelSuppliers->setHeaderData(0,Qt::Horizontal,"ID");
  modelSuppliers->setHeaderData(1,Qt::Horizontal,"Name");
  modelSuppliers->setHeaderData(2,Qt::Horizontal,"Address");
  modelSuppliers->setHeaderData(3,Qt::Horizontal,"Address1");
  modelSuppliers->setHeaderData(4,Qt::Horizontal,"City");
  modelSuppliers->setHeaderData(5,Qt::Horizontal,"User");
  modelSuppliers->setHeaderData(6,Qt::Horizontal,"Country");
  modelSuppliers->setHeaderData(7,Qt::Horizontal,"Sta/Reg");
  modelSuppliers->setHeaderData(8,Qt::Horizontal,"Active");
//  modelSuppliers->setRelation(5,QSqlRelation("user","iduser","email"));
//  modelSuppliers->setRelation(6,QSqlRelation("country","idcountry","name"));
//  modelSuppliers->setRelation(7,QSqlRelation("sta_reg","idsta_reg","name"));
  modelSuppliers->select();

  ui->suppliers->setModel(modelSuppliers);
  ui->suppliers->resizeColumnsToContents();
  ui->suppliers->resizeRowsToContents();
  ui->suppliers->setItemDelegate(new QSqlRelationalDelegate(ui->suppliers));
  ui->suppliers->show();
}

XTest::~XTest()
{
  delete ui;
}

void XTest::on_save_clicked()
{
  modelSuppliers->submitAll();
}

I've been playing with this for a while and i have discovered something that may be related. I connected the signal beforeUpdate of the model to a function that displays the row and QSqlRecord. I made two runs one with the relation "user" active and another with non of them active. On line #5 it seems to me that the model is trying to update the record with the description of the related table and not with the key. The second run, the one that updates, has the key on that position. Is that a bug on QT?

2
QSqlRecord(9)
 0: QSqlField("idsupplier", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: true, readOnly: false) "4" 
 1: QSqlField("name", QString, length: 180, precision: 0, required: no, generated: no, typeID: 253, autoValue: false, readOnly: false) "Other" 
 2: QSqlField("address", QString, length: 180, precision: 0, required: no, generated: no, typeID: 253, autoValue: false, readOnly: false) "Direccion" 
 3: QSqlField("address1", QString, length: 180, precision: 0, required: no, generated: yes, typeID: 253, autoValue: false, readOnly: false) "dddd" 
 4: QSqlField("city", QString, length: 180, precision: 0, required: no, generated: no, typeID: 253, autoValue: false, readOnly: false) "Santiago" 
 5: QSqlField("iduser", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: false, readOnly: false) "N/A" 
 6: QSqlField("idcountry", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: false, readOnly: false) "2" 
 7: QSqlField("idsta_reg", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: false, readOnly: false) "5" 
 8: QSqlField("active", char, length: 4, precision: 0, required: no, generated: no, typeID: 1, autoValue: false, readOnly: false) "1"
""

.
    2
    QSqlRecord(9)
     0: QSqlField("idsupplier", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: true, readOnly: false) "4" 
     1: QSqlField("name", QString, length: 180, precision: 0, required: no, generated: no, typeID: 253, autoValue: false, readOnly: false) "Other" 
     2: QSqlField("address", QString, length: 180, precision: 0, required: no, generated: no, typeID: 253, autoValue: false, readOnly: false) "Direccion" 
     3: QSqlField("address1", QString, length: 180, precision: 0, required: no, generated: yes, typeID: 253, autoValue: false, readOnly: false) "dddd" 
     4: QSqlField("city", QString, length: 180, precision: 0, required: no, generated: no, typeID: 253, autoValue: false, readOnly: false) "Santiago" 
     5: QSqlField("iduser", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: false, readOnly: false) "1" 
     6: QSqlField("idcountry", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: false, readOnly: false) "2" 
     7: QSqlField("idsta_reg", int, length: 11, precision: 0, required: yes, generated: no, typeID: 3, autoValue: false, readOnly: false) "5" 
     8: QSqlField("active", char, length: 4, precision: 0, required: no, generated: no, typeID: 1, autoValue: false, readOnly: false) "1"

SGaist

Hi,

Can you provide a minimal compilable example that shows the behaviour you describe ?

VRonin

@Dan3460 said in Updating a relational table view:

On line #5 it seems to me that the model is trying to update the record with the description of the related table and not with the key. The second run, the one that updates, has the key on that position. Is that a bug on QT?

not a bug it is the correct behaviour. if you see here you notice the first run is just to update how the view displays it while the second is what gets uploaded to the database.

Can you change

void XTest::on_save_clicked()
{
  modelSuppliers->submitAll();
}

into

void XTest::on_save_clicked()
{
  if(!modelSuppliers->submitAll())
qDebug() << modelSuppliers->lastError()->text();
}

and tell us what the output is?

Dan3460

Thanks every one for the replays, i have decided to use regular SQL statements to manipulate the database, i have more control and i think is simpler to use. I'm using QSqlRelationalTableModel to display but updates, inserts and deletes are done in SQL.