QSqlRelationalTableModel and OnManualSubmit oddness

[Lykurg]

Hi,

I have following simple QSqlRelationalTableModel example which works for OnRowChange but not for OnManualSubmit. Any clue why?

#include <QtGui>
#include <QtSql>
 
int main(int argc, char* argv[])
{
  QApplication a(argc, argv);
 
  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName(":memory:");
  if (!db.open())
  {
    qDebug() << db.lastError();
    return 0;
  }
 
  QSqlQuery query;
  query.exec("create table employee(id int primary key, namex varchar(20), city int)");
  query.exec("insert into employee values(1, 'Espen', 5000)");
  query.exec("insert into employee values(2, 'Harald', 80000)");
  query.exec("insert into employee values(3, 'Sam', 100)");
 
  query.exec("create table city(id int, name varchar(20))");
  query.exec("insert into city values(100, 'San Jose')");
  query.exec("insert into city values(5000, 'Oslo')");
  query.exec("insert into city values(80000, 'Munich')");
 
  QSqlRelationalTableModel model;
  model.setTable("employee");
  model.setRelation(2, QSqlRelation("city", "id", "name"));
  //  model.setEditStrategy(QSqlTableModel::OnManualSubmit);
  model.setEditStrategy(QSqlTableModel::OnRowChange);
  model.select();
 
  // Now add a new record
  QSqlRecord r;
  QSqlField f;
 
  f.setName("id");
  f.setType(QVariant::Int);
  f.setValue(4);
  r.append(f);
 
  f.setName("namex");
  f.setType(QVariant::String);
  f.setValue("Lykurg");
  r.append(f);
 
  f.setName("city");
  f.setType(QVariant::Int);
  f.setValue(80000);
  r.append(f);
 
  qDebug() << "insertRecord" << model.insertRecord(-1, r);
  qDebug() << " -->" << model.lastError();
  qDebug() << "submitAll" << model.submitAll();
  qDebug() << " -->" << model.lastError();
 
  query.exec("SELECT id, namex, city FROM employee WHERE id = 4");
  query.next();
  qDebug() << "id:" << query.value(0).toString();
  qDebug() << "name:" << query.value(1).toString();
  qDebug() << "city:" << query.value(2).toString();
 
  QTableView v;
  v.setModel(&model);
  v.show();
 
  return a.exec();
}

#include <QtGui>
#include <QtSql>

int main(int argc, char* argv[])
{
  QApplication a(argc, argv);

  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName(":memory:");
  if (!db.open())
  {
    qDebug() << db.lastError();
    return 0;
  }

  QSqlQuery query;
  query.exec("create table employee(id int primary key, namex varchar(20), city int)");
  query.exec("insert into employee values(1, 'Espen', 5000)");
  query.exec("insert into employee values(2, 'Harald', 80000)");
  query.exec("insert into employee values(3, 'Sam', 100)");

  query.exec("create table city(id int, name varchar(20))");
  query.exec("insert into city values(100, 'San Jose')");
  query.exec("insert into city values(5000, 'Oslo')");
  query.exec("insert into city values(80000, 'Munich')");

  QSqlRelationalTableModel model;
  model.setTable("employee");
  model.setRelation(2, QSqlRelation("city", "id", "name"));
  //  model.setEditStrategy(QSqlTableModel::OnManualSubmit);
  model.setEditStrategy(QSqlTableModel::OnRowChange);
  model.select();

  // Now add a new record
  QSqlRecord r;
  QSqlField f;

  f.setName("id");
  f.setType(QVariant::Int);
  f.setValue(4);
  r.append(f);

  f.setName("namex");
  f.setType(QVariant::String);
  f.setValue("Lykurg");
  r.append(f);

  f.setName("city");
  f.setType(QVariant::Int);
  f.setValue(80000);
  r.append(f);

  qDebug() << "insertRecord" << model.insertRecord(-1, r);
  qDebug() << " -->" << model.lastError();
  qDebug() << "submitAll" << model.submitAll();
  qDebug() << " -->" << model.lastError();

  query.exec("SELECT id, namex, city FROM employee WHERE id = 4");
  query.next();
  qDebug() << "id:" << query.value(0).toString();
  qDebug() << "name:" << query.value(1).toString();
  qDebug() << "city:" << query.value(2).toString();

  QTableView v;
  v.setModel(&model);
  v.show();

  return a.exec();
}

To copy to clipboard, switch view to plain text mode 

Debug for OnRowChange:

insertRecord true
--> QSqlError(-1, "", "")
submitAll false
--> QSqlError(-1, "No Fields to update", "")
id: "4"
name: "Lykurg"
city: "80000"

And for OnManualSubmit:

insertRecord false
--> QSqlError(-1, "", "")
submitAll true
--> QSqlError(-1, "", "")
id: "4"
name: "Lykurg"
city: ""

As you see city is empty and so it failed...



Thanks,

Lykurg

[wysota]

What if you set a primary key for the second table?

[Lykurg]

Still the same. I changed the creation to

query.exec("create table city(id int primary key, name varchar(20))");

query.exec("create table city(id int primary key, name varchar(20))");

To copy to clipboard, switch view to plain text mode 

[wysota]

How does the contents of the model look like before you submit them to the database?

[Lykurg]

Crazy! With OnManualSubmit my model record looks like this:

QSqlRecord( 3 )
" 0:" QSqlField("id", int, generated: yes, typeID: 1) "4"
" 1:" QSqlField("namex", QString, generated: yes, typeID: 3) "Lykurg"
" 2:" QSqlField("name", QString, generated: yes, typeID: 3) ""

Which cant work, but my inserted record is:

QSqlRecord( 3 )
" 0:" QSqlField("id", int, generated: yes) "4"
" 1:" QSqlField("namex", QString, generated: yes) "Lykurg"
" 2:" QSqlField("city", int, generated: yes) "80000"

AND HERE WE GO! I have to take the column name of the FK-Table! With a record like that:

QSqlRecord( 3 )
" 0:" QSqlField("id", int, generated: yes) "4"
" 1:" QSqlField("namex", QString, generated: yes) "Lykurg"
" 2:" QSqlField("name", int, generated: yes) "80000"

it works. But it seems to me, that that can't be normal? Or why the "wrong" syntax works with OnRowChange?


Thanks for pushing me in the right direction!

Lykurg

[wysota]

Actually what I was surprised was that it worked with OnRowChange. I knew (by own experience) about having to use the related column name but since it worked for OnRowChange I thought it didn't apply here.

[giusepped]

I have a similar problem which arises only in Windows XP.
I use a proxy attached to a model for filtering input search.

model->setEditStrategy(QSqlTableModel::OnRowChange);
 
...
 currentIndex = proxyModel->mapToSource(tViewDatiPannello->currentIndex());
	if (currentIndex.isValid()) {
 
 
	QSqlRecord r = model->record(currentIndex.row());
        qDebug()<<"DATA"<<currentIndex<<r.field("id")<<r;

model->setEditStrategy(QSqlTableModel::OnRowChange);

...
 currentIndex = proxyModel->mapToSource(tViewDatiPannello->currentIndex());
	if (currentIndex.isValid()) {


	QSqlRecord r = model->record(currentIndex.row());
        qDebug()<<"DATA"<<currentIndex<<r.field("id")<<r;

To copy to clipboard, switch view to plain text mode 

In Windows 7, no problem. The debug output is:

DATA QModelIndex(4,3,0x0,QSqlTableModel(0x5423da0) )  QSqlField("id", int, generated: yes, typeID: 1) QSqlRecord( 20 ) 
 " 0:" QSqlField("id", int, generated: yes, typeID: 1) "5"

DATA QModelIndex(4,3,0x0,QSqlTableModel(0x5423da0) )  QSqlField("id", int, generated: yes, typeID: 1) QSqlRecord( 20 ) 
 " 0:" QSqlField("id", int, generated: yes, typeID: 1) "5"

To copy to clipboard, switch view to plain text mode 

but in Windows XP, the first row is empty! I mean it seems that it cannot find the column "id"

[wysota]

Are you sure you are using exactly the same code and exactly the same data in both situations?

[giusepped]

I just copied the exe from one Os to the other (Windows XP is under a VirtualBox machine).
On both, I installed the gdb and printout the debug messages.

G

[wysota]

Does the data come from the same or similar file? Was the database opened properly?

[giusepped]

Yes, also the database is the same. I just copied it from on Os to the other.
The database is opened as usual

model       = new QSqlTableModel(this);
        listModel   = new QStringListModel(this);
        proxyListModel = new QSortFilterProxyModel(this);
        proxyModel  = new MyFilterProxyModel(this);

model       = new QSqlTableModel(this);
        listModel   = new QStringListModel(this);
        proxyListModel = new QSortFilterProxyModel(this);
        proxyModel  = new MyFilterProxyModel(this);

To copy to clipboard, switch view to plain text mode 

And there is a function to set the db name

void MyData::setDatabase(const QSqlDatabase &db,const QString &t)
{
	database = db;	
	table = t;
        if (model!=0){
		delete model;
		model = new QSqlTableModel(0,database);
	}
        if (listModel!=0) {
            delete listModel;
            listModel = new QStringListModel(this);
 
        }
 
	initializeModel();
	refreshView();
}

void MyData::setDatabase(const QSqlDatabase &db,const QString &t)
{
	database = db;	
	table = t;
        if (model!=0){
		delete model;
		model = new QSqlTableModel(0,database);
	}
        if (listModel!=0) {
            delete listModel;
            listModel = new QStringListModel(this);

        }

	initializeModel();
	refreshView();
}

To copy to clipboard, switch view to plain text mode 

The problem is that with Qt 4.5 I never such thing. I will try to install Qt 4.5 again and try to repeat this apparent "bug".

[wysota]

Yes, also the database is the same. I just copied it from on Os to the other.

So it's not the same, it is similar.

The database is opened as usual

Do you make an actual check that the database does get opened?

[giusepped]

Yes, because the debug output is the same except for the first row.
I mean, I have 10 cols, and the first one is the "id" field or the key of the db, and I qDebug the QSqlRecord which comes from the proxy. In W7 I can see all the data correctly, in XP I can see everything except the first col, the "id". Just the first one.
Regards

[wysota]

And what happens if you get rid of the MyFilterProxyModel instance?

[giusepped]

Nothing.
I just put

QSqlRecord r = model->record(0);
        qDebug()<<"DATA"<<currentIndex<<r.field("id")<<r;

QSqlRecord r = model->record(0);
        qDebug()<<"DATA"<<currentIndex<<r.field("id")<<r;

To copy to clipboard, switch view to plain text mode 

the result is the same (in XP). The first column is empty.

[wysota]

What's the result of:

qDebug() << currentIndex << r;

qDebug() << currentIndex << r;

To copy to clipboard, switch view to plain text mode 

[giusepped]

I attach a screenshot. You can see that only the first field is empty.
But if a try to query the database by using sqlite3 on the commandline, I can see that it is not!
Regards

[cc0e]

I think this is the reason why onManual submit won't work.
It seems single field and row/record operations are not intended to work with insertRecord() function.

/*!
    Inserts the \a record after \a row. If \a row is negative, the
    record will be appended to the end. Calls insertRows() and
    setRecord() internally.
 
    Returns true if the row could be inserted, otherwise false.
 
    \sa insertRows(), removeRows()
*/
bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
{
    Q_D(QSqlTableModel);
    if (row < 0)
        row = rowCount();
    if (!insertRow(row, QModelIndex()))
        return false;
    if (!setRecord(row, record))
        return false;
    if (d->strategy == OnFieldChange || d->strategy == OnRowChange)
        return submit();
    return true;
}

/*!
    Inserts the \a record after \a row. If \a row is negative, the
    record will be appended to the end. Calls insertRows() and
    setRecord() internally.

    Returns true if the row could be inserted, otherwise false.

    \sa insertRows(), removeRows()
*/
bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
{
    Q_D(QSqlTableModel);
    if (row < 0)
        row = rowCount();
    if (!insertRow(row, QModelIndex()))
        return false;
    if (!setRecord(row, record))
        return false;
    if (d->strategy == OnFieldChange || d->strategy == OnRowChange)
        return submit();
    return true;
}

To copy to clipboard, switch view to plain text mode