0

Imagine I have the following SQLite table definition:

create table test (id integer primary key, info integer);

and the following entries:

id  | info
----------
1   | 10
2   | 20
3   | 30

I want to use Qt's QSqlQuery class in order to prepare() a query and use the bindValue() function.

What I'm trying to achieve is something along the lines of 

insert into test values (
    ( select id from test where ROWID = last_insert_rowid() )+100,
    666
);

in order to get:

id  | info
----------
1   | 10
2   | 20
3   | 30
103 | 666

While that works directly exec()ing the statement via a QSqlQuery qry object, this

//qry is set up correctly.
qry.prepare("insert into test values (?,?);");
qry.bindValue(0, "select id from test where ROWID = last_insert_rowid() )+100");
qry.bindValue(1,666);
qry.exec();

doesn't work (datatype mismatch).

1) How can I get this to work by using bindValue()?

2) What is the neatest way to achieve the same behavior withouth using last_insert_rowid()?

3) What would value would be returned by the code above for id if the table had no rows so far? Zero?

LCsa
  • 607
  • 9
  • 30

1 Answers1

3

1) You cannot bind a SQL expression to "?",that is a binding purpose. Just forget about first "?" and bind only one value:

qry.prepare("insert into test values ( (select id from test where ROWID = last_insert_rowid() )+?,?);");
qry.bindValue(0,100);
qry.bindValue(0,666);
qry.exec();

2) If you have integer primary key column, sqlite last_insert_rowid() will return the value of that column, so you can simply write:

qry.prepare("insert into test values (last_insert_rowid()+?,?);");
qry.bindValue(0,100);
qry.bindValue(0,666);
qry.exec();

Thinking of your intended behavior, this will not behave like auto increment, because someone can insert a value at index that cause collision for your next insert. More bulletproof approach is to increment the maximal value:

qry.prepare("insert into test values ( (select id from test order by id desc limit 1)+?,?);");
qry.bindValue(0,100);
qry.bindValue(0,666);
qry.exec();

3) If the table are empty this select will return null, and null+100 is still null, and this will trigger the auto increment so 1 is inserted.

Arpegius
  • 5,817
  • 38
  • 53
  • Ok, I thought so... And any idea about part 2)? – LCsa Nov 17 '15 at 09:12
  • Okay, if I just want to address the last inserted row, `ROWID` and `last_insert_rowid()` is the way to go? – LCsa Nov 18 '15 at 18:04
  • @LCsa I think i edited after you accept my question, you are so impatient. – Arpegius Nov 18 '15 at 19:47
  • I'm not impatient, I just felt that it was too much in the light of the initial question and decided to take it away again, before you're bothered by it. This attempt apparently failed, however, definitely no harm meant! :-) Thank you for updating, I'll undo the change in the question! – LCsa Nov 18 '15 at 19:53