I'm not blaming anyone. I just think it should be mentioned in the docs. Mike
-----Ursprüngliche Nachricht----- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 29. November 2007 20:12 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE is not logical and to blame others for your oversight is not helpful. Michael Ruck wrote: > 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] > ---------------------------------------------------------------------------- - > > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
