Because we didn't read QSqlRecord docs carefully enough (I didn't read it at all, just looked at it).Originally Posted by jacek
Because we didn't read QSqlRecord docs carefully enough (I didn't read it at all, just looked at it).Originally Posted by jacek
I have been watching this thread since its beginnings because I am stuck with the same problem. Look at this scenario:
Two database tables
CUSTOMERS
customer_id INTEGER AUTOINCREMENT NOT NULL
name VARCHAR
city INTEGER NOT NULL DEFAULT 0
CITIES
city_id INTEGER AUTOINCREMENT NOT NULL
city_name VARCHAR
A model:
Qt Code:
model->setTable("customers"); model->select();To copy to clipboard, switch view to plain text mode
"this" beeing the form on which there is a QTableView:
Qt Code:
ui.tableView->setModel(model);To copy to clipboard, switch view to plain text mode
A QPushButton insertButton and a custom slot:
Qt Code:
void MainWindow::on_insertButton_clicked(bool checked) { rec.setValue("name", "Test Customer"); rec.setValue("city_name", "London"); bool insert = model->insertRecord(-1, rec); bool submit = model->submit(); }To copy to clipboard, switch view to plain text mode
The select statement upon which the model is created is like this:
"SELECT customers.customer_id, customers.name, cities.city_name
FROM customers, cities
WHERE customers.city = cities.city_id"
This is the return of model->query().lastQuery().
The QSqlRecord rec from above have the same fields: customer_id, name, city_name, which is obvious.
BUT if you run the code like this both insertRecord() and submit() return FALSE and the last error reported by model->lastError().text() is:
"Unknown column 'city_name' in 'field list' QMYSQL3 : QMYSQLResult". Surprisingly in the tableView there is a new row which contain the values used by the setRecord() calls.
You get the same result if you use a numerical value with rec.setValue() in line 6:
Qt Code:
rec.setValue("city_name", 1);To copy to clipboard, switch view to plain text mode
QSqlTableModel::insertRecord() calls internally QSqlTableModel::insertRows() and QSqlTableModel::setRecord(). The new row is shown in the tableView and that means that only insertRows() worked but not setRecord().
Now change this line (6) to be like this:
Both insertRecord() and submit() return TRUE, the record is added in the database (I have checked with MySQL Query Browser) but the field "city" is set to 0, which is the default value for the field and model->lastError().text() is empty. The record isn't shown in the tableView because there is no corresponding record in the related table "cities" to match the WHERE clause 'customers.city = cities.city_id'.
So the first way is not good (it generates SQL errors and no record added) the second one seems also not to be working (no SQL error, record added, but with a useless value in the field upon the relation is set). Until now I couldn't narrow it more than this. Which way??
The behaviour is (probably) because you can't insert into two tables with a single insert statement. Using this model you can only insert into the table which you set to be the model table ("customers" in your case). That's why you have to reference "city" and not "city_name".
Now we come to the second part -- the Problem(R).
I would try two things:
1.2.Qt Code:
rec.setValue("city", "London");To copy to clipboard, switch view to plain text modeQt Code:
rec.setGenerated("city", true);To copy to clipboard, switch view to plain text mode
A third thing to do could be to check what is the type of QVariant associated with "city" field (using rec.field().type()).
Is it not written to the database or not displayed in the tableview?Originally Posted by MarkoSan
Oh, and guys, try not to use UPPERCASE column names in SQL tables, it's a risk while porting between platforms.
Thanks.
1. I have triedand with the same result -- record added to database but 'city' field value == 0.Qt Code:
rec.setValue("city", "London");To copy to clipboard, switch view to plain text mode
2 - 3. I'll go for them later, I do not have Qt4 right now.
Is my problem similar to MarkoSan's or am I wrong?
I have done some more "research" and this is what I have:
rec.field("city").type() returns QVariant::Invalid - that's because rec does not contain a field named "city".
rec.field("city_name").type() returns QVariant::String which is correct as long the database field city_name is a VARCHAR.
Using rec.setValue("city", "London") apparently works because QSqlRecord::setValue(const QString & name, const QVariant & val) does nothing if the field ("city" in this case) does not exist. So the record is inserted in the database using the default value (0) for the field "city_name" which is contained by the record rec. In this case why rec.setValue("city_name", "London") doesn't work and model->insertRecord(-1, rec) returns FALSE?
I have also found something that looks strange, at least for me. I run in MySQL QueryBrowser the query upon which the QSqlRelationalTableModel model is filled with data and a saw that I can't insert or update records in the resultset returned.
I think I have to dig deeper into this model/view architecture. For now I will try a different approach and use a QSqlQuery("INSERT INTO ...") to make things going on, but the topic still remains opened.
You might want to ask Trolltech about it, it might be some kind of bug or lack of documentation.
I know why submitAll is not working for me. I've set up foreign keys, but I had not done the JOIN statement, I'll try it now and report the result.
Qt 5.3 Opensource & Creator 3.1.2
I do something like what is shown below, but if you have an updatable view (using multiple tables) on your database then something similar to this snippet should work.
BTW, this code works on Oracle and on PostgreSQL before 8.1.0. On PostgreSQL after 8.0 they've added 3 millisecond positions to the QDateTime return.Qt Code:
void homestead::UpdateProperty() { propQryPrep.append(this->dbYear); propQryPrep.append(" SET \ county = :county, \ cntyname = :cntyname, \ txdistrict = :txdistrict, \ legal = :legal, \ parcel_id = :parcel_id, \ pvalue = :pvalue, \ entry_id = :entry_id, \ entry_date = :entry_date, \ notes = :notes \ WHERE proprty_id = :proprty_id"); propQry.prepare(propQryPrep); propQry.bindValue(":county",ui.leCountyNumber->text().toInt()); propQry.bindValue(":cntyname",ui.cboCountyName->currentText()); propQry.bindValue(":txdistrict",ui.leTaxDistrict->text()); propQry.bindValue(":legal",ui.txtLegal->toPlainText()); propQry.bindValue(":parcel_id",ui.leParcelID->text()); propQry.bindValue(":pvalue",ui.leHomeValue->text().toInt()); propQry.bindValue(":entry_id",homestead::RevID); propQry.bindValue(":entry_date",dtNow); propQry.bindValue(":notes",ui.teNotes->toPlainText()); propQry.bindValue(":proprty_id",ui.leProprtyID->text().toInt()); if (propQry.exec()) { ui.leStatus->setText("Property record: "+ui.leProprtyID->text()+" updated!"); } else { ui.leStatus->setText("Cannot updated property record: "+ui.leProprtyID->text()); } }To copy to clipboard, switch view to plain text mode
I have found a solution to my problem. I have appended a QSqlField to the record retrieved with model->record()
'city' is the name of a field in the 'customers' database table but which is not part of the query that filled the model.Qt Code:
rec.append(cityField); rec.setValue("name", "Test Customer"); rec.setValue("city", 1); // or whatever value exists in the related table 'cities' model->insertRecord(-1, rec); model->submit();To copy to clipboard, switch view to plain text mode
'city_name' is the name of a field in the related table 'cities' and is contained by rec.
I run this code and I got the same "error" - record inserted in the table 'customers' but the value of 'city' field is set to the default == 0. Then I have installed Qt 4.1.0 and magically it worked - record inserted with proper value for field 'city' == 1 (in this case).
So, as wysota said, it is probably a bug in Qt 4.0.0.
Thanks for all suggestions.
Whatever I do, I cannot get data into database!!!! I am going mad!!!
Qt 5.3 Opensource & Creator 3.1.2
Please provide a minimal compilable example which reproduces the problem. Your code is too complex (and incomplete) for us to test on our own machines.Originally Posted by MarkoSan
I tried, but zip file is too big and i do not know how to distribure sql databse.Originally Posted by wysota
Qt 5.3 Opensource & Creator 3.1.2
Is the situation maybe connected with http://www.trolltech.com/developer/t...entry&id=86645, what do you think?
Qt 5.3 Opensource & Creator 3.1.2
So make it smaller. The example is to be minimal. We don't need your database, just a table schema and some sample data.Originally Posted by MarkoSan
Here is minimal project file and sources (VC++ 6.0 Service Pack 6, Qt 4.0.1). After unziping in .sql file there are table declarations and sample data.
Last edited by MarkoSan; 17th June 2006 at 03:11.
Qt 5.3 Opensource & Creator 3.1.2
I have managed to make it work but I use Qt 4.1.0 on WinXp and MySQL 4.1.11. Similar code didn't work on Qt 4.0.0, but I don't know about 4.0.1. My modifications are commented with 'modified by fane' (hope I didn't miss anyone), and of course your MySQL root password has to be changed.
I have appended two QSqlFields to the record retrieved with model->record() named "ULICA" and "POSTA_STEVILKA", set their value with QSqlRecord::setValue(). I have modified the edit strategy to be OnRowChange and removed the setValue() call for the "SIFRA" field which is autoincremented by MySQL.
I guess you have to move to Qt 4.1.0 according to this
Last edited by fane; 18th January 2006 at 19:02.
Bookmarks