QT QPSQL table insert return column

Question

Using Qt Creator 4.1.0 based on Qt 5.6.

I have a postgresql table that looks like this:

CREATE TABLE shelf (
 bookid sequence,
 title character varying(200),
 author character varying(200),
 publisher character varying(200),
 isbn character varying(200),
 genre character varying(200)
);

I have just done a successful insert

QSqlQuery que;
que.exec("insert into shelf(title) values('blah') returning bookid;");

The insert worked fine, how to I get returning bookid?

score 0 · Answer 1 · answered Jan 19 '18 at 13:46

0

try:

 while (que.next()) {
        QString bookid = que.value(0).toString();
         qDebug() << bookid;
    }

?..

answered Jan 19 '18 at 13:46

Vao Tsun

47,234
13
100
132

Mike · Answer 2 · 2018-01-19T20:52:40.613

You have two ways to get the last insert id when using Qt and PostgreSQL:

Using lastInsertId(): This has the advantage of being portable (i.e. your code keeps working if you decide to change to another DBMS later on), but it might be unsafe if you had triggers in your database that fire upon insertion in your table (e.g. shelf) and insert other values in other tables (note that it is implemented by issuing the query SELECT lastval(); on PostgreSQL). When using this approach, your code should look something like this:
```
QSqlQuery query;
query.prepare("INSERT INTO some_table(col_name) VALUES(?);");
query.addBindValue("sth");
if(!query.exec()) //insert statement failed
    qWarning() << "insert statement failed with error: "
               << query.lastError().databaseText();
int insertId = query.lastInsertId().toInt();
//do anything you want with the id
qDebug() << "id: " << insertId;
```

Using the RETURNING clause: This has the advantage of being safer in case you had the weird triggers mentioned above, but it is not portable (i.e. the insert statement won't execute if you ever decide to change to another DBMS because it is not standard SQL anymore). This way, your code should look something like this:

QSqlQuery query;
query.prepare("INSERT INTO some_table(col_name) VALUES(?) RETURNING id_col_name;");
query.addBindValue("sth");
if(!query.exec()) //insert statement failed
    qWarning() << "insert statement failed with error: "
               << query.lastError().databaseText();
if(!query.next()) //returning clause failed
    qWarning() << "returning clause did not return any data";
int insertId = query.value(0).toInt();
//do anything you want with the id
qDebug() << "id: " << insertId;

I tried the first, it worked fine. I don't understand the bind value, I looked it up but didn't make any sense to me. At the moment I have not triggers. Is it legal to post links ? here goes.... My complete example is on github at https://github.com/vince-br-549/PubStor — Vince, Jan 20 '18 at 18:14
Seems to work both with and without the addBindValue("sth"); I don't understand, the doc for it, didn't help me :) — Vince, Jan 20 '18 at 18:32
@Vince `prepare()`/`addBindValue()` are about using [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) which are mostly utilized to mitigate [SQL injection attacks](https://en.wikipedia.org/wiki/SQL_injection). Your code certainly works both ways, but you should always prefer to write safe code... — Mike, Jan 20 '18 at 22:38
Thanks Mike, I had seen use of prepare stmts before but diidn't realize how they would work to prevent injection attacks, The light came on here :) — Vince, Jan 22 '18 at 19:03

QT QPSQL table insert return column

2 Answers2