Page 3 of 3 FirstFirst 123
Results 41 to 57 of 57

Thread: Record update windowd entered data saving

  1. #41
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Record update windowd entered data saving

    Quote Originally Posted by jacek
    I just wonder why didn't we thought about this earlier
    Because we didn't read QSqlRecord docs carefully enough (I didn't read it at all, just looked at it).

  2. #42
    Join Date
    Jan 2006
    Location
    Iasi, Romania
    Posts
    10
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Record update windowd entered data saving

    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:
    1. model = new QSqlRelationalTableModel(this);
    2. model->setTable("customers");
    3. model->setRelation(2, QSqlRelation("cities", "city_id", "city_name"));
    4. model->select();
    To copy to clipboard, switch view to plain text mode 

    "this" beeing the form on which there is a QTableView:

    Qt Code:
    1. ui.tableView->setModel(model);
    To copy to clipboard, switch view to plain text mode 

    A QPushButton insertButton and a custom slot:

    Qt Code:
    1. void MainWindow::on_insertButton_clicked(bool checked)
    2. {
    3. QSqlRecord rec = model->record();
    4.  
    5. rec.setValue("name", "Test Customer");
    6. rec.setValue("city_name", "London");
    7.  
    8. bool insert = model->insertRecord(-1, rec);
    9. bool submit = model->submit();
    10. }
    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:
    1. 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:
    Qt Code:
    1. rec.setValue("city", 1);
    To copy to clipboard, switch view to plain text mode 
    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??

  3. #43
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Record update windowd entered data saving

    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.
    Qt Code:
    1. rec.setValue("city", "London");
    To copy to clipboard, switch view to plain text mode 
    2.
    Qt Code:
    1. 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()).

  4. #44
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Record update windowd entered data saving

    Quote Originally Posted by MarkoSan
    Ok, I have another question: Why do I get now table filled with new data correctly, but If i reopen windows, the data dissapears, that means, record is still not being written to database?
    Is it not written to the database or not displayed in the tableview?


    Oh, and guys, try not to use UPPERCASE column names in SQL tables, it's a risk while porting between platforms.

  5. #45
    Join Date
    Jan 2006
    Location
    Iasi, Romania
    Posts
    10
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Record update windowd entered data saving

    Thanks.
    1. I have tried
    Qt Code:
    1. rec.setValue("city", "London");
    To copy to clipboard, switch view to plain text mode 
    and
    Qt Code:
    1. rec.setValue("city", 1) ;
    To copy to clipboard, switch view to plain text mode 
    with the same result -- record added to database but 'city' field value == 0.

    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?

  6. #46
    Join Date
    Jan 2006
    Location
    Iasi, Romania
    Posts
    10
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Record update windowd entered data saving

    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.

  7. #47
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Record update windowd entered data saving

    You might want to ask Trolltech about it, it might be some kind of bug or lack of documentation.

  8. #48
    Join Date
    Jan 2006
    Location
    Ljubljana
    Posts
    687
    Thanks
    111
    Thanked 4 Times in 4 Posts
    Qt products
    Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: Record update windowd entered data saving

    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

  9. #49
    Join Date
    Jan 2006
    Location
    Lincoln, NE USA
    Posts
    177
    Thanks
    3
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Record update windowd entered data saving

    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.
    Qt Code:
    1. void homestead::UpdateProperty() {
    2. QDateTime dtNow = QDateTime::currentDateTime();
    3. QString propQryPrep = "UPDATE property_";
    4. propQryPrep.append(this->dbYear);
    5. propQryPrep.append(" SET \
    6. county = :county, \
    7. cntyname = :cntyname, \
    8. txdistrict = :txdistrict, \
    9. legal = :legal, \
    10. parcel_id = :parcel_id, \
    11. pvalue = :pvalue, \
    12. entry_id = :entry_id, \
    13. entry_date = :entry_date, \
    14. notes = :notes \
    15. WHERE proprty_id = :proprty_id");
    16.  
    17. propQry.prepare(propQryPrep);
    18. propQry.bindValue(":county",ui.leCountyNumber->text().toInt());
    19. propQry.bindValue(":cntyname",ui.cboCountyName->currentText());
    20. propQry.bindValue(":txdistrict",ui.leTaxDistrict->text());
    21. propQry.bindValue(":legal",ui.txtLegal->toPlainText());
    22. propQry.bindValue(":parcel_id",ui.leParcelID->text());
    23. propQry.bindValue(":pvalue",ui.leHomeValue->text().toInt());
    24. propQry.bindValue(":entry_id",homestead::RevID);
    25. propQry.bindValue(":entry_date",dtNow);
    26. propQry.bindValue(":notes",ui.teNotes->toPlainText());
    27. propQry.bindValue(":proprty_id",ui.leProprtyID->text().toInt());
    28. if (propQry.exec()) {
    29. ui.leStatus->setText("Property record: "+ui.leProprtyID->text()+" updated!");
    30. } else {
    31. ui.leStatus->setText("Cannot updated property record: "+ui.leProprtyID->text());
    32. }
    33. }
    To copy to clipboard, switch view to plain text mode 
    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.

  10. #50
    Join Date
    Jan 2006
    Location
    Iasi, Romania
    Posts
    10
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Record update windowd entered data saving

    I have found a solution to my problem. I have appended a QSqlField to the record retrieved with model->record()
    Qt Code:
    1. QSqlRecord rec = model->record();
    2. QSqlField cityField("city", QVariant::Int);
    3. rec.append(cityField);
    4. rec.setValue("name", "Test Customer");
    5. rec.setValue("city", 1); // or whatever value exists in the related table 'cities'
    6.  
    7. model->insertRecord(-1, rec);
    8. model->submit();
    To copy to clipboard, switch view to plain text mode 
    'city' is the name of a field in the 'customers' database table but which is not part of the query that filled the model.
    '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.

  11. #51
    Join Date
    Jan 2006
    Location
    Ljubljana
    Posts
    687
    Thanks
    111
    Thanked 4 Times in 4 Posts
    Qt products
    Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: Record update windowd entered data saving

    Whatever I do, I cannot get data into database!!!! I am going mad!!!
    Qt 5.3 Opensource & Creator 3.1.2

  12. #52
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Record update windowd entered data saving

    Quote Originally Posted by MarkoSan
    Whatever I do, I cannot get data into database!!!! I am going mad!!!
    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.

  13. #53
    Join Date
    Jan 2006
    Location
    Ljubljana
    Posts
    687
    Thanks
    111
    Thanked 4 Times in 4 Posts
    Qt products
    Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: Record update windowd entered data saving

    Quote Originally Posted by wysota
    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.
    I tried, but zip file is too big and i do not know how to distribure sql databse.
    Qt 5.3 Opensource & Creator 3.1.2

  14. #54
    Join Date
    Jan 2006
    Location
    Ljubljana
    Posts
    687
    Thanks
    111
    Thanked 4 Times in 4 Posts
    Qt products
    Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: Record update windowd entered data saving

    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

  15. #55
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Record update windowd entered data saving

    Quote Originally Posted by MarkoSan
    I tried, but zip file is too big and i do not know how to distribure sql databse.
    So make it smaller. The example is to be minimal. We don't need your database, just a table schema and some sample data.

  16. #56
    Join Date
    Jan 2006
    Location
    Ljubljana
    Posts
    687
    Thanks
    111
    Thanked 4 Times in 4 Posts
    Qt products
    Qt5 Qt/Embedded
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: Record update windowd entered data saving

    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 04:11.
    Qt 5.3 Opensource & Creator 3.1.2

  17. #57
    Join Date
    Jan 2006
    Location
    Iasi, Romania
    Posts
    10
    Qt products
    Qt3 Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Record update windowd entered data saving

    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
    Attached Files Attached Files
    Last edited by fane; 18th January 2006 at 20:02.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.