PDA

View Full Version : QSqlRelationalTableModel and OnManualSubmit oddness



Lykurg
13th July 2010, 11:15
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
13th July 2010, 11:25
What if you set a primary key for the second table?

Lykurg
13th July 2010, 11:27
Still the same. I changed the creation to
query.exec("create table city(id int primary key, name varchar(20))");:(

wysota
13th July 2010, 11:34
How does the contents of the model look like before you submit them to the database?

Lykurg
13th July 2010, 11:44
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
13th July 2010, 15:13
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
3rd December 2010, 13:27
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
3rd December 2010, 13:35
Are you sure you are using exactly the same code and exactly the same data in both situations?

giusepped
3rd December 2010, 13:49
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
3rd December 2010, 13:51
Does the data come from the same or similar file? Was the database opened properly?

giusepped
3rd December 2010, 14:02
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
3rd December 2010, 14:04
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
3rd December 2010, 14:10
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
3rd December 2010, 14:12
And what happens if you get rid of the MyFilterProxyModel instance?

giusepped
3rd December 2010, 14:26
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
3rd December 2010, 16:00
What's the result of:

qDebug() << currentIndex << r;

giusepped
5th December 2010, 11:24
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
10th December 2010, 14:35
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;
}