How to get the ID of a previously inserted row within a transaction using QSqlQuery

Question

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();
}

Closely related: http://stackoverflow.com/questions/13393604/postgresql-next-value-of-the-sequences — Erwin Brandstetter, Dec 10 '12 at 14:14
Agreed; `INSERT ... RETURNING` is ideal here. See the [`INSERT` documentation](http://www.postgresql.org/docs/current/static/sql-insert.html) for specifics. — willglynn, Dec 10 '12 at 14:26
Confirmed that RETURNING works from pgAdmin for a test insert, like so `INSERT INTO nonforeignkeyentity ("id", "name") VALUES (DEFAULT, 'teste') RETURNING "id"`. But this does not work via `QSqlQuery`, so I am still in need of guidance here. How to retrieve that value from the query object. Must I bind a variable for the return value or something? — average joe, Dec 10 '12 at 14:30
`insertQuery.record().indexOf("id");` returns 0, which is a valid index according to the Qt documentation. I am really flustered. If the field exists, why is there no value there? — average joe, Dec 10 '12 at 14:46
You probably have to call insertQuery.next() before fetching the value . — andrewffff, Dec 10 '12 at 15:33
Doh! You're absolutely right. Don't know what should be judged answer here, since there are no answers. :) — average joe, Dec 10 '12 at 16:11

score 16 · Accepted Answer · answered Jan 14 '18 at 20:37

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();

score 2 · Answer 2 · answered Sep 15 '18 at 09:44

@Kasheen is perfectly right but I want to set the focus on one important aspect you should keep in mind: Don't forget to encapsulate everything in one single transaction.

Why? It saves time and avoids database corruption if the second insert using the generated primary key (you got with q.lastInsertId()) fails.

So your insert should look like this (this is basically @Kasheen's answer with some additions):

db_.transaction(); // Starts a transaction

QSqlQuery q;

// first insert
q.prepare("INSERT INTO table_name VALUES(:some_column_name)");
q.bindValue(":some_column_name", "FooBar");
q.exec();

auto pk == q.lastInsertId().toInt;

// second insert
q.prepare("INSERT INTO other_table VALUES(:other_column_name, :fk)");
q.bindValue(":other_column_name", "OtherText");
q.bindValue(":fk", pk);
q.exec();

db_.commit(); // Commits transaction

@average joe does the transaction handling correctly but you might forget it if you look on the solution only.

How to get the ID of a previously inserted row within a transaction using QSqlQuery

2 Answers2

Linked