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::OnManualSubm it);
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();
}


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

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

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"

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


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

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;

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

[wysota]

What's the result of:

qDebug() << currentIndex << r;

[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;
}