[sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
Using SQLite version 3.7.7.1 (Windows/VS2008). A table has an integer autoincrement primary key, the value of which is being used to feed an R*Tree index. I am attempting to insert data and retrieve the latest row_id() in a compound statement like this using the SQLite C API: "INSERT INTO table (columns) VALUES(?,?,?,?); SELECT last_insert_rowid() FROM table;" which works in the Sqlite3 console. However when I run this sqlite3_step() returns SQLITE_DONE. sqlite3_prepare(); sqlite3_bind_xxx(); sqlite3_step(); Can anyone point out what I am missing? Thx++. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
On Jan 18, 2012, at 3:35 PM, g...@novadsp.com wrote: > "INSERT INTO table (columns) VALUES(?,?,?,?); > SELECT last_insert_rowid() FROM table;" These are really two statements, not one. Execute them one after the other and you will get the desired effect. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
g...@novadsp.com wrote: > "INSERT INTO table (columns) VALUES(?,?,?,?); SELECT last_insert_rowid() > FROM table;" > > which works in the Sqlite3 console. However when I run this > sqlite3_step() returns SQLITE_DONE. > > sqlite3_prepare(); > sqlite3_bind_xxx(); > sqlite3_step(); > > Can anyone point out what I am missing? sqlite3_prepare only accepts one statement, and (optionally) gives you a pointer to the beginning of the next one. This way, if you have a string with multiple statements in it, you can prepare and execute them one by one - which is exactly what SQLite shell is doing. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
Thanks to all. On 18/01/2012 14:53, Petite Abeille wrote: Or just use last_insert_rowid() directly in the next insert statement. That will reuse the rowid of the previous insert for the new one. And both row will end up with the same id, keeping your source table and its R*Tree index in synch. *Splendid* idea, one for the FAQ surely. Presumably this can be used in a binding string as in this? "INSERT INTO gps_index (auto_id,minX, maxX,minY, maxY) VALUES(last_insert_rowid(), ?, ?, ?, ?)" Thank you :) Jerry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
On Jan 18, 2012, at 4:16 PM, g...@novadsp.com wrote: >> Or just use last_insert_rowid() directly in the next insert statement. That >> will reuse the rowid of the previous insert for the new one. And both row >> will end up with the same id, keeping your source table and its R*Tree index >> in synch. > > *Splendid* idea, one for the FAQ surely. Yes, this is a pretty common idiom. > Presumably this can be used in a binding string as in this? > > "INSERT INTO gps_index (auto_id,minX, maxX,minY, maxY) > VALUES(last_insert_rowid(), ?, ?, ?, ?)" Yes, exactly. > Thank you :) You are welcome. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
