Results 1 to 4 of 4

Thread: QODBC, QSqlTableModel, and submit problems

  1. #1
    Join Date
    May 2006
    Posts
    70
    Thanks
    12
    Thanked 4 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default QODBC, QSqlTableModel, and submit problems

    I'm pretty much fed up with ODBC and Qt but I thought I'd make comment on this since no one else has anywhere.

    The "names" table has an autoid "id" column and a "name" text column.

    Here is code I use when wanting to insert a new item into my database.

    Qt Code:
    1. //Create a simple model
    2. model.setEditStrategy(QSqlTableModel::OnManualSubmit);
    3. model.setTable("names");
    4. model.setFilter("id is null"); //I do this so that I don't have to return every single row, or in this case any rows at all.
    5. model.select();
    6.  
    7. //Make a mapper to hook up my widgets to my data
    8. mapper.setModel(&model);
    9. mapper.addMapping(myIdLineEdit, 0);
    10. mapper.addMapping(myNameLineEdit, 1);
    11.  
    12. //Since the model has no rows currently (because of the 'is null' filter), insert a default one
    13. QSqlRecord rec = model.record();
    14. rec.setValue(1, "type name here");
    15. model.insertRecord(-1, rec);
    16. mapper.toFirst();
    To copy to clipboard, switch view to plain text mode 

    At this point if the user wants to cancel editing I can call model.revertAll() and insert a new default record.

    But if the user wants to save i do this:
    Qt Code:
    1. qDebug() << model.index(0,0).data().toString(); //Try to display the id, this will be blank because the record only exists in Qt's cache and not in the database yet.
    2. qDebug() << model.rowCount(); //displays "1"
    3. qDebug() << mapper.currentIndex(); //displays "0"
    4. model.submitAll();
    5. qDebug() << mapper.currentIndex(); //displays "-1" because submitAll() calls select() and invalidates all model indexes.
    6. qDebug() << model.rowCount(); // *** SEE BELOW ***
    7. qDebug() << model.index(0,0).data().toString(); //*** SEE BELOW ***
    To copy to clipboard, switch view to plain text mode 

    Ok so if we run the above using a MySql database we now have a model.rowCount() of 1, and the autoid get's passed back from the database and displays correctly.
    (using mysql 5, qmysql driver, and qt 4.4.0)

    On a MS-SQL server via ODBC (ms-sql server 2005, qodbc driver, qt 4.4.0 compiled with msvc c++ 2008) the model.rowCount() shows 0 and the the autoid line doesn't work because there are no rows in the model.

    I kinda get what it's doing behind the scenes and it does kinda make sense to me but it's highly annoying when trying to use QSqlTableModel, QDataWidgetMapper, and the ability to keep data cached locally until the user wants it saved.

    • Am I misunderstanding Trolltech's way of doing things?
    • Should I turn back to using QSqlQuery and doing everything manually?
    • Would using the commercial TDS driver fix these problems? (I'm using the opensource version of Qt right now)


    I guess it all comes down to what QSqlDriver->hasFeature() returns, right?. I peeked at the 4.4.0 source for each driver (qodbc, qmysql, qsqlite) and can see how limited qodbc is compared to the others. What I can't do and don't know is what the TDS driver's hasFeature returns. Can anyone tell me what features it supports?

    If this is the only way to get MS-SQL to play nicely with Qt apps I am willing to shell out the money for the TDS driver... but I was really hoping for an open source solution.

    If anyone can help out with this I would be so very very grateful. Thanks

  2. #2
    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: QODBC, QSqlTableModel, and submit problems

    I don't really see the problem here... You set the filter to display rows with invalid id, then you insert a row with valid id and expect the model to return rowCount() of 1 regardless of the fact it still contains only rows with invalid id? Try resetting the filter and see if the problem persists.

  3. #3
    Join Date
    May 2006
    Posts
    70
    Thanks
    12
    Thanked 4 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QODBC, QSqlTableModel, and submit problems

    I totally agree with you. I don't expect there to be a rowCount of 1 afterwards. In fact the ODBC result seems correct to me. I was a shocked to see the MySQL method work as it does.

    But here is the problem: When inserting a new row into a model and then submitting it how do you retrieve that same row again?

    Example (using the same table as previously posted):
    Qt Code:
    1. QSqlRecord rec = model.record(); //create blank record
    2. rec.setValue(1, "some name"); //set only the name, leaving the auto id field null
    3. model.insertRecord(-1, rec); //append the record to the end of the model
    4. mapper.toLast(); //set the mapper to the last row so we are seeing this new record in our widgets
    5. //** let the user edit data on the widgets here
    6. model.submitAll(); //save our cached model to the database
    7. mapper.setCurrentIndex( ????? );
    To copy to clipboard, switch view to plain text mode 

    Here are some ideas I have come up with but they pose their own problems:

    Solution #1
    Do not filter the model so that select returns all rows, then assume the last row is our newly inserted row.
    Qt Code:
    1. model.setFilter(QString()); //no filter
    2. model.select();
    3. QSqlRecord rec = model.record(); //create blank record
    4. rec.setValue(1, "some name"); //set only the name, leaving the auto id field null
    5. model.insertRecord(-1, rec); //append the record to the end of the model
    6. mapper.toLast(); //set the mapper to the last row so we are seeing this new record in our widgets
    7. //** let the user edit data on the widgets here
    8. model.submitAll(); //save our cached model to the database
    9. mapper.setCurrentIndex(model.rowCount() - 1);
    To copy to clipboard, switch view to plain text mode 

    Possible Problems:
    • What if other users insert or delete rows in the table while the user is editing data. When the submitAll() get's called it will reselect the table and our row will be in a different location.
    • When selecting a table with no filter and the table has many many rows we are wasting memory and slowing things down. (Am I wrong with this assumption?)
    • What if we insert more than just one new row...?


    Solution #2
    Save the values of the current row (except for the id) just before we submit. Then right after the submit change the model's filter to select the record that matches all those values. Then call select and we should have only one row returned.

    Possible Problems:
    • If two or more records exist that have identical data but different id's then our filter won't know which one was the new one. I guess we could sort on id and take the highest one but that's assuming the database always increments the id.


    Solution #3
    Don't use the database auto id's and use UUID's.

    Possible Problems:
    • The primary key for each table will be a string instead of a BIGINT. Is this a performance problem?


    Solution #4
    Subclass QSqlTableModel and re-implement QSqlTableModel::insertRowsIntoTable(const QSqlRecord &values) so that you can retrieve the last inserted id. With MySQL and SQLITE you can use QSqlQuery::lastInsertId() and with ODBC (or at least MSSQL+ODBC) you can run the query "SELECT id = @@Identity"

    Possible Problems:
    • Still have the same problem as solution #1 in that you have to select all rows.



    I realize now that this has boiled down to the same problem that I posted in this thread. (Sorry for duplicating posts! )
    In regards to that thread, yes wysota passing NULL as the auto-increment value is the correct way of doing it in both MySQL and ODBC, thanks.
    What trueneo suggested doesn't work for 2 reasons. First the QODBC driver does not support lastInsertId and secondly QSqlDatabase does not have a lastInsertId method.

    I'm leaning towards using UUID's instead of database-generated id's just to make this work properly. I look forward to hearing what your opinions are on any of the above solutions or if you have different ways of doing things let me know! Thanks for reading such a long post heh.

  4. #4
    Join Date
    Aug 2007
    Posts
    5
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: QODBC, QSqlTableModel, and submit problems

    Ops, sorry, lastInsertId() is a QSqlQuery::method, you can get the query using QSqlTableModel::query() then the lastInsertId(). The suggestion is about mysql, I do not know ODBC.

    May I suggest you to change your approach?
    1 - QSqlTableModel is intended for simple tasks, it will not locks the table you want to modify, so others could even truncate it.
    If you wanto to insert a record at once write the insert code yourself avoiding QSqlTableModel methods. A good prepared statement with a QSql::InOut bound placeholder could resolve the issue. QSqlTableModel is good if you want to change existing data but a filter on the changed field could cause the loosing of the selected row after the submitAll(). Remember, every submitAll() will cause a select query invalidating indexes and you will need a lookup to find the row inserted/modified.
    2 - QtSql does not guarantee the database independence, you cannot pass from a database to another using only one code base, Trolltech guarantees only a layer for basic operations.

    Daniele

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.