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]
-----------------------------------------------------------------------------
