Hi Günter, Great, yes that makes sense!
So that solves it. I was thinking about this and thought there must be some way to do this already; thank you so much for pointing this out. This would also open up for picking up several inserted id:s from one query which is nice. Thanks, Mikael 2013/5/7 Hick Gunter <h...@scigames.at> > How about calling sqlite3_update_hook()?**** > > ** ** > > You will be notified about all UPDATE, INSERT and DELETE operations as > they occur, with the database, table and rowid affected and in the thread > executing the statement.**** > > ** ** > > If you register a well-behaved function (i.e. that calls the previously > registered function) these hooks may even be layered…**** > > ** ** > > *Von:* Mikael [mailto:mikael.tr...@gmail.com] > *Gesendet:* Dienstag, 07. Mai 2013 14:15 > *An:* Richard Hipp; Simon Slavin; Hick Gunter; James K. Lowden; General > Discussion of SQLite Database > *Betreff:* Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or > functionalequivalent?**** > > ** ** > > Hi,**** > > ** ** > > Thanks for your feedback, James.**** > > ** ** > > A generalized way of getting if an ID was inserted on the last performed > query and if so which, is of value for instance when inserting a row into a > table with an AUTOINCREMENT key e.g.**** > > ** ** > > CREATE TABLE t (**** > > id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,**** > > d TEXT**** > > );**** > > ** ** > > and then inserting into it, e.g.**** > > ** ** > > "INSERT INTO t (d) VALUES (\"a\");" => inserted id: 1**** > > "INSERT INTO t (d) VALUES (\"a\");" => inserted id: 2**** > > "INSERT INTO t (d) VALUES (\"b\");" => inserted id: 3**** > > "INSERT INTO t (d) VALUES (\"b\");" => inserted id: 4**** > > "INSERT INTO t (d) VALUES (\"c\");" => inserted id: 5**** > > ** ** > > This functionality makes a lot of sense in at least certain SQLite > bindings. In the places where it's not strictly required, it will still > provide clarity which is valuable in debug, and convenience from being > dynamic, please see the example use ~15 rows below for this -**** > > ** ** > > ** ** > > So what I'm looking for is a general mechanism for deriving the ID > inserted for a query. E.g.:**** > > ** ** > > "SELECT a FROM b" => none**** > > ** ** > > "UPDATE b SET a = c" => none**** > > ** ** > > "INSERT IGNORE INTO b (a) VALUES (c)" and the ID was already in there => > none**** > > ** ** > > "INSERT IGNORE INTO b (a) VALUES (c)" and the ID was not already in there > => the ID of the inserted row**** > > ** ** > > "INSERT INTO b (a) VALUES (c)" => the ID of the inserted row**** > > ** ** > > ** ** > > Thanks to this, a SQLite binding can be achieved which > dynamically/*automatically* detects not only if it was a readonly or > non-readonly query, but also within the non-readonly query if it was an > update or insert, and thus a generalized query mechanism can be devised > that behaves like:**** > > ** ** > > SELECT query => The query results**** > > UPDATE query => The number of rows updated**** > > INSERT query => The ID of the row inserted, or none (by null/false/0/etc.) > **** > > ** ** > > ** ** > > This is to create a general abstraction for this purpose. Indeed SQLite > performs queries, well, at the level of query and, the meaningful work you > do with SQLite is at the granularity of a query, so it appears reasonable > to me that there would be a mechanism using which you could retrieve the ID > of the row inserted by a particular query.**** > > ** ** > > A generalized "rows changed by this query" abstraction can be implemented > as of today, using sqlite3_changes which returns the number of changed rows > in the *last performed query*, the error reporting (as to check > sqlite3_changes was set on the last query) and possibly secondarily > by stmt_readonly .**** > > ** ** > > ** ** > > The problem with checking the ID of the last inserted row is that the > abstraction for checking it cannot reasonably know that a query is an > insert;**** > > ** ** > > Essentially doing a string match for "INSERT " in the query executed would > be a leaky abstraction.**** > > ** ** > > Getting a copy of SQLite's SQL parser's data could be something, but then > - this is a bit beyond my current SQLite skills - aren't there mechanisms > to make INSERT:s indirectly in SQLite, like through a database-stored > procedure or alike?**** > > ** ** > > That would make any abstraction like this leak, because it wouldn't know > if an INSERT was made during a query, and that knowledge is required as to > know if sqlite3_last_insert_rowid regards the last inserted ID or not.**** > > ** ** > > ** ** > > ** ** > > And to illustrate how sqlite3_changes and sqlite3_last_insert_rowid does > not deliver for automatic query ID retrieval:**** > > ** ** > > Using these two, let's define an attempt at a general mechanism for this, > in pseudocode:**** > > ** ** > > var last_rowId_on_last_query = 0;**** > > function query(query) {**** > > lock_mutex();**** > > sqlite_perform_query(query);**** > > var last_rowId = sqlite_last_rowid();**** > > var id_of_this_row = (last_rowId != last_rowId_on_last_query) ? > last_rowId : null; > last_rowId_on_last_query = last_rowId;**** > > unlock_mutex();**** > > return id_of_this_row;**** > > }**** > > ** ** > > Now, let's set up these two tables**** > > ** ** > > CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT ); > CREATE TABLE t2 ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, d TEXT ); > **** > > ** ** > > and now make some inserts with it! The use of query() above is implied > here.**** > > ** ** > > "INSERT INTO t1 (d) VALUES (\"a\");" => 1 - Correct!**** > > "INSERT INTO t1 (d) VALUES (\"a\");" => 2 - Correct!**** > > "INSERT INTO t2 (d) VALUES (\"a\");" => 1 - Correct!**** > > "INSERT INTO t2 (d) VALUES (\"a\");" => 2 - Correct!**** > > "INSERT INTO t2 (d) VALUES (\"a\");" => 3 - Correct!**** > > "INSERT INTO t1 (d) VALUES (\"a\");" => null - Error, abstraction leak, > ouch!**** > > ** ** > > ** ** > > All of this leakiness would be undone if before a query, the SQLite user > could reset the sqlite3_last_insert_rowid . By that, the user can just > check the sqlite3_last_insert_rowid right after the query, and by that > know that that was .**** > > ** ** > > It may be a bit too simplistic if a query inserted several ID:s as you > pointed out, though at least it's general unlike the present solution, and > can be made to fit any usecase at least I have in reach, as in SQLite any > INSERT operation can be performed one row per query with OK-to-great > performance.**** > > ** ** > > ** ** > > Introduction of a sqlite3_reset_last_insert_rowid would be complimenting > the current sqlite_int64 sqlite3_last_insert_rowid at sqlite3.c row > 114023-114028:**** > > ** ** > > /***** > > ** Return the ROWID of the most recent insert**** > > */**** > > SQLITE_API sqlite_int64 sqlite3_last_insert_rowid(sqlite3 *db){**** > > return db->lastRowid;**** > > }**** > > ** ** > > with the similar**** > > ** ** > > SQLITE_API void sqlite3_reset_last_insert_rowid(sqlite3 *db){**** > > db->lastRowid = 0;**** > > }**** > > ** ** > > and then making it export properly.**** > > ** ** > > ** ** > > The reason I address you on this list directly with this is because it > appears to be of use for SQLite bindings as far as I can see.**** > > ** ** > > I agree that keeping the API minimal is a top priority. Though, for > instance exporting this as a SQL procedure that could be invoked by > preparing "CALL reset_last_insert_rowid();" and then invoking it, wouldn't > that be a bit too costly to do on *every* query however small in a > particular binding?**** > > ** ** > > ** ** > > ** ** > > If you see any other way to solve this with good performance, please feel > free to share, I'm just looking for a general way of getting if the last > query inserted an ID and if so which, it can be implemented any way as long > as it performs so good it can be included in a general query mechanism in a > binding.**** > > ** ** > > Thanks,**** > > Mikael**** > > ** ** > > 2013/5/7 James K. Lowden <jklow...@schemamania.org>**** > > On Mon, 6 May 2013 20:55:01 +0300 > Mikael <mikael.tr...@gmail.com> wrote: > > > Currently in the absence of a sqlite3_reset_last_insert_rowid(), the > > closest you can do to figure out what ID was actually inserted by the > > most recent SQLite query is: > > > > * Lock mutex > > * Get sqlite*'s last insert row ID > > * Perform the query > > * Get number of changed rows > > * Get sqlite*'s last insert row ID > > * Unlock mutex**** > > I don't understand. The process doing the insert presumably knows > which data it inserted. Why not turn around and SELECT the ids for the > inserted values? No mutex and no guesswork. > > Your process seems to imply that the the inserted values don't > constitute a key, or that you want to compute the row IDs for > performance. Those don't strike me as sufficient reason to perturb > the API. > > --jkl**** > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users**** > > ** ** > > ------------------------------ > Gunter Hick > Software Engineer > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If > you have received it in error, you are on notice as to its status and > accordingly please notify us immediately by reply e-mail and then delete > this message from your system. Please do not copy it or use it for any > purposes, or disclose its contents to any person as to do so could be a > breach of confidence. Thank you for your cooperation. > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
