I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call is valid or not. This makes the OR IGNORE clause or the sqlite3_last_insert_rowid() function useless for *my purposes*. I would have never pursued this path in tests, if I would've known beforehand that it is not reliable if used with ON CONFLICT clauses.
Mike -----Ursprüngliche Nachricht----- Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 14:04 An: sqlite-users@sqlite.org Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() The sqlite3_last_insert_rowid function is completely, 100% reliable in your scenario. The problem is that in your scenario you shouldn't be calling that function. The function is called sqlite3_last_insert_rowid, not sqlite3_last_insert_or_ignore_rowid, and not sqlite3_last_insert_or_fail_rowid. It makes perfect sense that it returns the row id of the last row inserted successfully. This function should only be called after a successful insert. In your scenario you have not performed a successful insert. There is no reason to think that the function will return a meaningful row id after a failed insert attempt. I hope my response was not too harsh. You seem so adamant that there is a problem with the function or documentation, and I completely disagree. Shawn -----Original Message----- From: Michael Ruck [mailto:[EMAIL PROTECTED] Sent: Sunday, October 28, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() I'd suggest putting this into the documentation of sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such as this one. -----Ursprüngliche Nachricht----- Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 17:48 An: sqlite-users@sqlite.org Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: > Yes, I am well aware of this possibility as I've written in my > initial mail. > It just doesn't fit with the > description of sqlite3_last_insert_rowid() in my understanding. I > think this > is a bug - either in the documentation > or in the implementation. sqlite3_last_insert_rowid() should return > the > correct id, no matter what and it doesn't. > Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
