How to get the ID of a previously inserted row within a transaction using QSqlQuery
qt, qtsql, transactions
Solution
I realise this is an old question but in Qt 5.10 (and likely earlier) there is a function `QSqlQuery::lastInsertId()` which can be called after `QSqlQuery::exec()`.
It's quite useful if you are using a database such as SQLite which doesn't support the `RETURNING` clause on an `INSERT` statement.
QSqlQuery::lastInsertId() documentation.
Usage is something along the lines of the following:
QSqlQuery q;
q.prepare("INSERT INTO table_name VALUES(:some_column_name)");
q.bindValue(":some_column_name", "FooBar");
q.exec();
qDebug() << "Last ID was:" << q.lastInsertId();
Problem
I am trying to get the primary key of an inserted row within a transaction scope, because I do not want to leave the db in a logically inconsistent state. My problem is I cannot find a way to retrieve the ID value of a previously executed query, which I want to use for the next insert query. Querying the PostgreSQL database while the transaction is in effect shows no results in the non-foreign-key table(the row is not yet committed?). I believe this is due to the transaction's isolation level. Below is what I'm trying to do with production code, albeit slightly edited and narrowed down to one function for clarity. `const int lastInsertId` is always 0, which in this context should mean no value was found (technically that `toInt()` function failed). I tried manually inserting a valid non-foreign-key row, and then calling `LASTVAL()` which produced the expected result - the ID of the inserted row. So, what am I doing wrong? What am I missing or misunderstanding here? ``` void createEntityWithoutForiegnKeyConstraint(const QString &nameOfEntity) { db_.transaction(); QSqlQuery insertQuery(db_); insertQuery.prepare("INSERT INTO \"EntityWithoutForeignKey\" (\"name\") VALUES (:name);"); insertQuery.bindValue(":name", nameOfEntity); execQuery(__LINE__, insertQuery); QSqlQuery lastIdQuery("SELECT LASTVAL();", db_); // auto executes const int lastInsertId = lastIdQuery.value(0).toInt(); if (lastInsertId <= 0) // 0 is not a valid ID throw exception("Oh noes."); createEntityWithForeignKeyConstraint(lastInsertId, someData); if (!db_.commit()) db_.rollback(); } ```