2013/5/6 Mikael <[email protected]> > 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 > > Ah, and here you conclude this statement's inserted row ID from:
If changed rows is zero or last insert row ID checked before query and after query are the same, then there was no row inserted, so there's no insert it (false/null/etc). Otherwise, use the last insert ID checked last. So this is the test procedure that the following applies to: > 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. > > And would be resolved by this: > > 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 > Brgds > > 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
