Daniel, My usecase is the following: I maintain a lot of tables, which are simply catalogs of predefined or user entered values. I wanted to reduce the amount of code and memory to maintain those tables, as the user can enter values as free text in a lot of web forms and also choose from previously entered values via ajax autocompletion. To simplify my code I wanted to use INSERT OR IGNORE in those catalog tables, as I don't care if the value is already there or not. I just need the rowids as foreign keys for other table(s).
An example would be: - CREATE TABLE song (id INTEGER PRIMARY KEY, artist INTEGER, writer INTEGER, title INTEGER) - CREATE TABLE artists (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE writers (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE titles (id INTEGER PRIMARY KEY, value TEXT UNIQUE) For an insert of a new song I wanted to do (pseudo code) INSERT OR IGNORE INTO artists VALUES (NULL, 'user entered or chosen artistname'); rowid-of-first-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO writers VALUES (NULL, 'user entered or chosen writer name'); rowid-of-second-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO titles VALUES (NULL, 'user entered or chosen title'); rowid-of-third-insert = sqlite3_last_insert_rowid(); INSERT INTO song (NULL, rowid-of-first-insert, rowid-of-second-insert, rowid-of-third-insert); This is just a trivial example of what I want to do. My understanding of INSERT OR IGNORE was that it always succeeds, even though the record is already there (which is exactly what I want.) In contrast to INSERT OR REPLACE it doesn't remove the old row and thus keeps the same rowid. In conjunction with the documentation for sqlite3_last_insert_rowid(), which states: "This routine returns the rowid of the most recent INSERT into the database from the database connection given in the first argument. If no inserts have ever occurred on this database connection, zero is returned." I was assuming that I'll receive the rowid even in case where the conflict clause from INSERT OR IGNORE caused the insert not to happen. I was just a bit surprised about this and that's why I asked if this was expected behavior. Dr. Hipps answer cleared the reason for this up and I already started looking for alternatives. The only thing I was asking for is that this behavior (sqlite3_last_insert_rowid() and its results with CONFLICT clauses) be explicitly mentioned in the documentation of sqlite3_last_insert_rowid(). Unfortunately the solution proposed by Shawn Odekirk fails too, as I don't have the rowids for insert statements. If i did, I wouldn't INSERT OR IGNORE or INSERT OR REPLACE in the first place. The id column is automatically maintained by SQlite and I don't want to mess with it. So I'll just do blind inserts and check the return value and do a select if insert fails. Little more code, but it works. Mike -----Ursprüngliche Nachricht----- Von: Daniel Önnerby [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 30. Oktober 2007 12:03 An: sqlite-users@sqlite.org Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Why are you using the INSERT OR IGNORE? If you read the http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not return any errors. Isn't the default behavior INSERT OR ABORT (or just plain INSERT) what you are looking for? The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a conflict. If you get a conflict then do not trust the sqlite3_last_insert_rowid since (I guess) it will return the last successful insert rowid. Best regards Daniel Michael Ruck wrote: > 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] > ---------------------------------------------------------------------- > ------- > > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
