Databases
StackOverflow2013
Postsdbo
POHow to get the ID of a previously inserted row within a transaction using QSqlQuery
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to get the ID of a previously inserted row within a transaction using QSqlQuery
    text
    copied!<p>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.</p> <p>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.</p> <p>Below is what I'm trying to do with production code, albeit slightly edited and narrowed down to one function for clarity. <code>const int lastInsertId</code> is always 0, which in this context should mean no value was found (technically that <code>toInt()</code> function failed). I tried manually inserting a valid non-foreign-key row, and then calling <code>LASTVAL()</code> which produced the expected result - the ID of the inserted row.</p> <p>So, what am I doing wrong? What am I missing or misunderstanding here? </p> <pre><code>void createEntityWithoutForiegnKeyConstraint(const QString &amp;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 &lt;= 0) // 0 is not a valid ID throw exception("Oh noes."); createEntityWithForeignKeyConstraint(lastInsertId, someData); if (!db_.commit()) db_.rollback(); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload