Results 1 to 5 of 5

Thread: Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

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

    Default Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

    I have a MySql table with a primary key that is an auto increment bigint.

    If I have a QSqlTableModel that is set to OnManualSubmit how do I add a new row and retrieve that auto increment value?

    Qt Code:
    1. MyClass::onAddNewItem()
    2. {
    3. QSqlTableModel *model = new QSqlTableModel(this);
    4. model->setTable("mytable");
    5. model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    6.  
    7. //This selects no records which is a lot faster, imho, then selecting without a filter.
    8. //Especially if mytable has a lot of rows.
    9. model->setFilter("myid is null"); //fyi, myid is column 0 in the model.
    10.  
    11. model->select();
    12.  
    13. //now I know I could use QSqlTableModel::insertRecord() ,
    14. //but I would like to use QAbstractItemModel methods only.
    15. //If using QSqlTableModel specific methods is the answer to my problem,
    16. //or even just a better way of doing it then please let me know.
    17.  
    18. QAbstractItemModel *abModel = model; //Yes I know this is unnecessary but it helps me adhere to my point above.
    19.  
    20. int newRow = abModel->rowCount();
    21. abModel->insertRow(newRow);
    22.  
    23. //Column 0 is the auto id, so I don't set it here.
    24. newIndex = abModel->index(newRow, 1);
    25. abModel->setData(newIndex, "myvalue");
    26. }
    27.  
    28. MyClass::onSaveItem()
    29. {
    30. //Calling abModel->submit() does nothing because of the OnManualSubmit strategy.
    31. //So I created a ModelHandler class that let's me call the correct submit method.
    32. //I'm using this ModelHandler to do other things as well and it looks odd in this example, i know.
    33.  
    34. myAbModelHandler->submit(); //The end result of this is the same as calling model->submitAll();
    35.  
    36. //This prints "FALSE".
    37. qDebug() << "New Index validity:" << newIndex.isValid();
    38.  
    39. //And because of that this statement doesn't work...
    40. qDebug() << "My new row's autoincrement value is: " << newIndex.sibling(newIndex.row(), 0).data().toString();
    41. }
    42.  
    43. MyClass::onCancelItem()
    44. {
    45. //same as the submit thing above except for revert.
    46. myAbModelHandler->revert(); //The end result of this is the same as calling model->revertAll();
    47. }
    To copy to clipboard, switch view to plain text mode 

    When saving the item my newIndex is invalid because submitAll() reselects the data and invalidates all indexes. Also because of my setFilter my model now has no values in it. Even if I wouldn't use the filter I can't assume it will be the last row in this newly selected dataset. I have no way of knowing which was the newly inserted row so that I can retrieve my auto incremented number from column 0.

    I guess an option would be to change the EditStrategy on the fly. But if I do that, when I call insertRow() it will automatically add that new row to the database. If I want to provide my user with a way of cancelling the new item I would have to delete the row instead of just calling revert(). This would also increase my auto increment number needlessly.

    Any wisdom would be very much appreciated!

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

    Default Re: Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

    When using auto increment fields you should pass NULL as the value of the auto increment field and the database will fill it with proper number.

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

    Default Re: Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

    submitAll() changes the underlying database and QSqlTableModel needs to requery for data correctness, submitAll() is used to submit changes , you could need to delete a record and submitAll() will save the changes on the database. Your problem is here, the second query breaks you plans. It is difficult to get the same table after submitAll() if you set a filter is even worst.
    If your intention is to display and insert a single row at once try this:
    after the submitAll() use QSqlDatabase::lastInsertId() to get the ID and call model->setFilter( myid is <QSqlDatabase::lastInsertId()> ) to get again the last inserted record.
    This should work only if you inserts one row at once, multiple rows will inserts multiple ids and you can get only the last or the first one I don't remember now.

    Daniele

  4. The following user says thank you to trueneo for this useful post:

    darkadept (10th April 2008)

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

    Default Re: Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

    I haven't tried it out yet but that looks like the answer I'm looking for.

    Wysota, I haven't completely confirmed this but passing NULL to an auto increment works great with MySql but I was having trouble with it when using ODBC and MSSQL on Windows.

    I'll be testing out that NULL problem soon and post the results here but I have been little tied up as my wife just gave birth to our first child.

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

    Default Re: Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

    I "accidentally" continued this post on a different thread. My apologies.

    Click here for that thread.

    @wysota passing NULL as the auto-increment value is the correct way of doing it in both MySQL and ODBC, thanks.

    @trueneo: what you suggested doesn't work for 2 reasons. First the QODBC driver does not support lastInsertId and secondly QSqlDatabase does not have a lastInsertId method.

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.