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

Reply via email to