Getting an Auto Increment value from QSqlTableModel with OnManualSubmit

[darkadept]

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?



MyClass::onAddNewItem()
{
QSqlTableModel *model = new QSqlTableModel(this);
model->setTable("mytable");
model->setEditStrategy(QSqlTableModel::OnManualSubmit);

//This selects no records which is a lot faster, imho, then selecting without a filter.
//Especially if mytable has a lot of rows.
model->setFilter("myid is null"); //fyi, myid is column 0 in the model.

model->select();

//now I know I could use QSqlTableModel::insertRecord() ,
//but I would like to use QAbstractItemModel methods only.
//If using QSqlTableModel specific methods is the answer to my problem,
//or even just a better way of doing it then please let me know.

QAbstractItemModel *abModel = model; //Yes I know this is unnecessary but it helps me adhere to my point above.

int newRow = abModel->rowCount();
abModel->insertRow(newRow);

//Column 0 is the auto id, so I don't set it here.
newIndex = abModel->index(newRow, 1);
abModel->setData(newIndex, "myvalue");
}

MyClass::onSaveItem()
{
//Calling abModel->submit() does nothing because of the OnManualSubmit strategy.
//So I created a ModelHandler class that let's me call the correct submit method.
//I'm using this ModelHandler to do other things as well and it looks odd in this example, i know.

myAbModelHandler->submit(); //The end result of this is the same as calling model->submitAll();

//This prints "FALSE".
qDebug() << "New Index validity:" << newIndex.isValid();

//And because of that this statement doesn't work...
qDebug() << "My new row's autoincrement value is: " << newIndex.sibling(newIndex.row(), 0).data().toString();
}

MyClass::onCancelItem()
{
//same as the submit thing above except for revert.
myAbModelHandler->revert(); //The end result of this is the same as calling model->revertAll();
}


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!

[wysota]

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.

[trueneo]

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

[darkadept]

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. :D

[darkadept]

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

Click here for that thread. (http://www.qtcentre.org/forum/f-qt-programming-2/t-qodbc-qsqltablemodel-and-submit-problems-13487.html)

@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.