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?
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.