PDA

View Full Version : EditStrategy, how to save data



JeanC
5th January 2008, 17:14
I'm beginning very slowly to catch some grip. Sometimes I even code more than search for things.

I can't seem to save changes in a QSqlTableModel, not if I set it to OnFieldChange and not if I set it to OnManualSubmit.
What am I overlooking?
Thanks

code:


QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("192.168.1.10");
db.setDatabaseName("iris");
db.setUserName("jean");
db.setPassword("");
if (!db.open())
setWindowTitle(db.lastError().text());

model = new QSqlTableModel;
model->setTable("pix");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();
table->setModel(model);

jacek
5th January 2008, 17:22
What do submitAll() and lastError() return?

JeanC
5th January 2008, 17:39
Thanks jacek,

it's empty:


model->submitAll();
QSqlDatabase db = QSqlDatabase::database();
qDebug() << "commit" << db.lastError().text();

Message:


commit " "

jacek
5th January 2008, 17:41
Is the output the same after you change anything in the table?

JeanC
5th January 2008, 17:41
Ow I do this in an onclose event, the docs said it was nessecary:


QSqlDatabase db = QSqlDatabase::database();
db.close();
db.removeDatabase("iris");

JeanC
5th January 2008, 17:45
Yes,

First I change a cell, clik on another, changes made are still visible, then click on the menuentry for the submit call.
I noticed that when choosing OnFieldChange, if I edit a cell, then click on another, that editing is lost, so it's just not writing to the database.



Is the output the same after you change anything in the table?

jacek
5th January 2008, 17:47
the docs said it was nessecary:
Could you post a link? Qt should close all database connections on exit.


QSqlDatabase db = QSqlDatabase::database();
db.close();
db.removeDatabase("iris");
After you do that, you won't be able to change anything in the database.

jacek
5th January 2008, 17:50
First I change a cell, clik on another, changes made are still visible, then click on the menuentry for the submit call.
What happens if you press the return key before clicking anywhere?

JeanC
5th January 2008, 18:16
hm, it does not say it's nessecary no.

To remove a database connection, first close the database using QSqlDatabase::close(), then remove it using the static method QSqlDatabase::removeDatabase().

I guess I was begin too carefull.

JeanC
5th January 2008, 18:17
The field resets to what it was before.

This is with OnFieldChange


What happens if you press the return key before clicking anywhere?

jacek
5th January 2008, 18:21
Can you edit your database with sqlbrowser demo that comes with Qt?

JeanC
5th January 2008, 18:34
Well that has no QSQL driver.
But I can edit it with all other tools, query browser, phpmyadmin, own php.

jacek
5th January 2008, 18:57
Well that has no QSQL driver.
It should have access to the same drivers as your application.

Have you tried to change the database with QSqlQuery?

JeanC
5th January 2008, 19:06
It should have access to the same drivers as your application.

I had a quick look at that code, it indeed queries for drives. Only ones it comes up with are QSQLITE, QSQLITE2 and QPSQL


Have you tried to change the database with QSqlQuery?

Gonna try that tomorrow and let you know.
Thanks for your time so far, appreciated.

JeanC
6th January 2008, 14:18
Yes, the same result, no error message, but no query executed either:

QString moviename = model->record(currentrow).value("name").toString();
QString querystr = "DELETE FROM pix WHERE name=";
querystr.append(moviename);
qDebug() << querystr;
QSqlQuery query(querystr);
query.exec();
qDebug() << "query" << db.lastError().text();

The output is:

"DELETE FROM pix WHERE name=ana.avi"
query " "


Have you tried to change the database with QSqlQuery?

jacek
6th January 2008, 14:22
QSqlQuery query(querystr);
query.exec();
This actually executes the query twice.


qDebug() << "query" << db.lastError().text();
What does qDebug() << query.lastError() output? What does db.isOpen() and db.isValid() return? Do you get any errors if you issue an invalid query? Do you start any transactions?

JeanC
6th January 2008, 14:51
This actually executes the query twice.

Ah thanks.


What does qDebug() << query.lastError() output? What does db.isOpen() and db.isValid() return? Do you get any errors if you issue an invalid query? Do you start any transactions?

I'm gonna investigate.
Oddity I noticed is that I can edit data with phpmyadmin, but the Mysql Query Browser has al edit options greyed out if I right click on a cell.

JeanC
6th January 2008, 14:54
What does qDebug() << query.lastError() output? What does db.isOpen() and db.isValid() return? Do you get any errors if you issue an invalid query? Do you start any transactions?

qDebug() << "error" << db.lastError();
qDebug() << "open" << db.isOpen();
qDebug() << "valid" << db.isValid();

Output:

error QSqlError(-1, "", "")
open true
valid true

jacek
6th January 2008, 14:54
but the Mysql Query Browser has al edit options greyed out if I right click on a cell.
Then maybe you don't have rights to modify the database?

jacek
6th January 2008, 14:56
error QSqlError(-1, "", "")
open true
valid true
And what about "query.lastError()"?

JeanC
6th January 2008, 15:15
Well, there was an error in my query, I had to surround the fieldname with ' '
Now it executes.

So a query executes...

jacek
6th January 2008, 16:18
How do you set up the model?

JeanC
6th January 2008, 16:49
This code is in mainwindowimpl's constructor
db and model are declared in the .h

db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("192.168.1.10");
db.setDatabaseName("iris");

db.setUserName("jean");
db.setPassword("");
if (!db.open())
setWindowTitle(db.lastError().text());

model = new QSqlTableModel;
model->setTable("pix");
model->setEditStrategy(QSqlTableModel::OnFieldChange);
model->setHeaderData(0, Qt::Horizontal, QObject::tr("Naam"));
model->select();
table->setModel(model);
selection = new QItemSelectionModel(model);
table->setSelectionModel(selection);

I am also having a hard time deleting a row in the tableview. But first things first.

jacek
6th January 2008, 16:58
This code is in mainwindowimpl's constructor
db and model are declared in the .h
Is that the only place where you invoke addDatabase()?


selection = new QItemSelectionModel(model);
table->setSelectionModel(selection);
These two lines aren't necessary. setModel() does this for you.

JeanC
6th January 2008, 18:37
Is that the only place where you invoke addDatabase()?

Yes it is.

In my searches I came across a tutorial which I'm gonna read: http://www.pro-linux.de/work/qt4/qt4-teil1.html
Good tutorials for qt 4 are not so easy to find as most is still about qt3, this men is called Jacek.
Coincidence?

JeanC
6th January 2008, 18:57
I have put my complete mainwindowimpl.cpp here:
http://jjmc.mine.nu/mainwindowimpl.cpp if you care to look.

I have a collection of movies on a harddisk and this is meant to catalog them and maintain the collection, a movie has some jpg's with it, gets a rate, has notes etc, a first linux cpp project to get me started.

JeanC
7th January 2008, 14:05
I tried to change that users mysql rights to all hosts in stead of just 192.168.1.11, this machine's local adress but still cannot change data. If I delete the contents of a cell, then click another, that previous cell resets it's content.

I'm out of options now.

jacek
8th January 2008, 00:02
I tried to change that users mysql rights to all hosts in stead of just 192.168.1.11, this machine's local adress but still cannot change data.
Do UPDATE queries work if you issue them through QSqlQuery? Do you have any constraints set on that table or its fields?

Can you edit the database with this code?

#include <QtGui>
#include <QtSql>

int main( int argc, char ** argv )
{
QApplication app( argc, argv );

QSqlDatabase db = QSqlDatabase::addDatabase( "QMYSQL" );
db.setHostName( "192.168.1.10" );
db.setDatabaseName( "iris" );
db.setUserName( "jean" );
db.setPassword( "" );

if( !db.open() ) {
return 0;
}

QTableView v;

QSqlTableModel m;
m.setTable( "pix" );
m.setEditStrategy( QSqlTableModel::OnFieldChange );
m.select();

v.setModel( &m );
v.show();

return app.exec();
}

JeanC
8th January 2008, 11:03
Thanks jacek for your efforts, I tried that code it but if I change a cell and then click another, the cell resets again so it's not writing.

An UPDATE with a QSqlQuery succeeds however. I can't see the change right away, I still have to figure out that table feedback part, but when the app is run again, changes are visible.

Maybe it's related but this deletes that row from the database but not from the tableview:


void MainWindowImpl::deleteClicked()
{
QString moviename = model->record(currentrow).value("name").toString();
QString querystr = "DELETE FROM pix WHERE name='";
querystr.append(moviename);
querystr.append("'");
qDebug() << querystr;
QSqlQuery query(querystr);
model->removeRows(currentrow, 1, QModelIndex());
}

jacek
8th January 2008, 14:00
Thanks jacek for your efforts, I tried that code it but if I change a cell and then click another, the cell resets again so it's not writing.
That's really weird, because it works on my system. Which Qt version do you use?



Maybe it's related but this deletes that row from the database but not from the tableview:

When you change your database behind QSqlTableModel's back, you have to invoke select() to see the changes.

JeanC
8th January 2008, 15:42
That's really weird, because it works on my system. Which Qt version do you use?

:(

I'm using 4.3.3. on ubuntu 7.10, with kubuntu on top and I'm using qdevelop 0.25.

Hola, if I ask qdevelop what version qt is, it says 4.3.2
But what's installed is /usr/local/Trolltech/Qt-4.3.3/
Strange...

Looks like I have to test this outside of qdevelop..


When you change your database behind QSqlTableModel's back, you have to invoke select() to see the changes.

This works. :)

I have been thinking of just using an ordinary table in stead of mysql and filling it myself with the results of queries. In that way I even have more freedom to let things be editable and such.

[edit] I'm having a look at QSqlQueryModel, but I'm still suspicious about things not working as they should, suggestions welcome.

JeanC
9th January 2008, 13:32
If was thinking, maybe it cannot write because that database had no primairy key, but if I add one, the table stays empty, although the database is not.

Another mystery or something that helps diagnose?

jacek
9th January 2008, 16:33
If was thinking, maybe it cannot write because that database had no primairy key
The docs don't say that primary key is required.


but if I add one, the table stays empty, although the database is not
Well... it shouldn't. What did you change exactly?


Another mystery or something that helps diagnose?
Do you use qmake? If yes, do you have QT += sql in the .pro file?

JeanC
9th January 2008, 17:31
Hmm, just tried again and now it shows my table with the primary key.

I use qdevelop which has a checkbox which puts += SQL in the .pro file. If that wasn't the case it would not work at all I guess. And it uses qmake yes.

I'm going to figure out how to build without qdevelop, shouldn't be too hard, and then I'll get back.

[edit] Since that primary key suddenly got working, I tried editing a cell, and the magic wand sparkles and suddenly the edits stick. It just works now. pff.