Dear Simon, Günter and list, The issue goes like this:
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 But this test leaks crazily much! If either of the following holds true, you're in Wild Bugs land! * This query was made as an INSERT to one table, and the last query was made as an INSERT to another table, and both yielded the same ID * This query was an UPDATE, so it gave a change count but did not actually generate a new ID Probably there's much more cases, though this is enough to show that the above method is absolutely undesirable and a perpetual source of bugs for use general method for retrieving a query's insert id. What do you say about a sqlite3_reset_last_insert_rowid() as to enable SQLite with this? It has a zero performance overhead on present code, and the binary addition should be in the range 50 bytes. Best regards 2013/5/6 Hick Gunter <[email protected]> > I use a separate SQLite connection for each thread to avoid such issues. > > When does the "last inserted rowid" become obsolete? > In your example, do you mean to imply that the last inserted rowid may be > retrieved once and once only? > > Why not just: > > (lock mutex) > (execute query) > if sqlite3_changes() ** may still be 0, see below > then > rowid = sqlite3_last_insert_rowid() > else > rowid = undefined > (unlock mutex) > > What about if the query creates more than 1 row (as in INSERT INTO ... > SELECT or even multiple VALUES tuples)? Why would you want to know only the > last rowid and not all of them? > > If you need to count changes made by triggers and foreign keys you may > need to: > > (lock mutex) > Before = sqlite_total_changes > (execute query) > After = sqlite_total_changes > If (Before < After) > ... > (unlock mutex) > > Also note that INSERT OR IGNORE does not change the last inserted rowid. > > I also suspect that INSERT OR REPLACE may very well change the last > inserted rowid (without tallying sqlite3_changes!!! See documentation). > > -----Ursprüngliche Nachricht----- > Von: Mikael [mailto:[email protected]] > Gesendet: Montag, 06. Mai 2013 14:40 > An: Richard Hipp; [email protected] > Betreff: [sqlite] How do sqlite3_reset_last_insert_rowid() or functional > equivalent? > > Dear list, > > After having made an SQLite statement the ID of a newly inserted row can > be retrieved with sqlite3_last_insert_rowid . > > It'd be a great thing to be able to produce a general mechanism for > retrieving this value with regard to the most recently performed query only > right after a query has been made, as programming aimed at getting this > value lazily won't work as other local code may have made another query to > the SQLite handle meanwhile. > > The most robust way to do this would be through having a > sqlite3_reset_last_insert_rowid() procedure to invoke right before a query, > because, sqlite3_last_insert_rowid is only updated on a successful insert. > > The code to check if a query is non-readonly can be done using > sqlite3_stmt_readonly , but then the step from there to check if it's an > *insert* and not only that but a successful insert, is a huge step and > possibly the only reliable way to tell this, would be by SQLite telling it, > and the most straightforward way for it to do this would be through > sqlite3_last_insert_rowid , so then what about un-problematizing that value > as to guarantee it won't return any obsolete value, by introducing a > sqlite3_reset_last_insert_rowid() ? > > Please let me know the best practice for solving this particular problem - > the ability to make a "Query" abstraction atop SQLite, that has its very > own "ID of row inserted" method, I believe is a reasonable aim. > > (I.e., not having such a property but that is not guaranteed to actually > contain the right thing, depending on very specific circumstances.) > > Example: > > (mutex for sqlite3 lock) > sqlite3_reset_last_insert_rowid(sqlite3*); > (perform SQLite query on sqlite3*) > sqlite3_last_insert_rowid(sqlite3*) => Guaranteedly returns the rowid of > any row inserted by the query, or 0 if no insert was done. > (mutex for sqlite3 unlock) > [starting here sqlite3_last_insert_rowid(sqlite3)'s return value is > undefined] > > > > Thanks, > Mikael > _______________________________________________ > sqlite-users mailing list > [email protected] > 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: [email protected] > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
