Re: [sqlite] last_insert_rowid() returns every growing list

Thanks, that explains what I saw. What I was trying to accomplish was retrieve the autoincrement key for the row that had just been previously inserted. I missed the "last_insert_rowid" method in the docs for the Tcl Sqlite interface -- it's only 3 lines :-)

Re: [sqlite] last_insert_rowid() returns every growing list

On 2/17/20, The Tick wrote: > sql eval { insert into test (id, number, data) values( $a, $b, $c ) } What were you hoping to accomplish here? It seems like you might be wanting the rowid of the last insert by any database connection into the "test" table. If so, that is not what last_insert_rowi

Re: [sqlite] last_insert_rowid() returns every growing list

On Monday, 17 February, 2020 17:20, The Tick wrote: >I'm running tcl 8.6.8 on win7x64. I built the latest sqlite Tcl package >with the 3310100 source using mingw gcc under msys2. >Everything seems to work but I ran into a strange result with >last_insert_rowid(). >The following example returns

[sqlite] last_insert_rowid() returns every growing list

I'm running tcl 8.6.8 on win7x64. I built the latest sqlite Tcl package with the 3310100 source using mingw gcc under msys2. Everything seems to work but I ran into a strange result with last_insert_rowid(). The following example returns an ever-growing list of rowid's: -8x- package req

Re: [sqlite] last_insert_rowid and FTS in 3.17

tter"); > sqlite> COMMIT; > sqlite> SELECT last_insert_rowid(); > 3 > sqlite> > sqlite> SELECT * FROM sqlite_sequence; > Foo|3 > sqlite> > sqlite> > > > -Original Message- > > From: sqlite-users [mailto:sqlit

Re: [sqlite] last_insert_rowid and FTS in 3.17

INTO Foo(id_foo, bar) VALUES(NULL, "otter"); sqlite> COMMIT; sqlite> SELECT last_insert_rowid(); 3 sqlite> sqlite> SELECT * FROM sqlite_sequence; Foo|3 sqlite> sqlite> > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailin

Re: [sqlite] last_insert_rowid and FTS in 3.17

On 3/15/17, Hugo Beauzée-Luyssen wrote: > Hi, I'm having some issues with last_insert_rowid starting with 3.17 > Basically it seems to be returning the row inserted by a trigger, > instead of the explicitly inserted row. Please try the latest pre-release snapshot at https://www.sqlite.org/downloa

[sqlite] last_insert_rowid and FTS in 3.17

Hi, Hi, I'm having some issues with last_insert_rowid starting with 3.17 Basically it seems to be returning the row inserted by a trigger, instead of the explicitly inserted row. As far as I understand, this contradicts the last_insert_rowid() documentation. I wrote a small test case to demonstr

Re: [sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

On 02/28/2017 12:15 AM, Cezary H. Noweta wrote: Hello, On 2017-02-27 11:41, Dan Kennedy wrote: CREATE VIRTUAL TABLE f USING fts3(x); BEGIN; INSERT INTO f VALUES('one'); INSERT INTO f VALUES('two'); INSERT INTO f VALUES('three'); INSERT INTO f VALUES('four'); COMMIT; IN

Re: [sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Hello, On 2017-02-27 11:41, Dan Kennedy wrote: CREATE VIRTUAL TABLE f USING fts3(x); BEGIN; INSERT INTO f VALUES('one'); INSERT INTO f VALUES('two'); INSERT INTO f VALUES('three'); INSERT INTO f VALUES('four'); COMMIT; INSERT INTO f VALUES('five'); SELECT last_insert_

Re: [sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

On 02/27/2017 05:03 AM, Cezary H. Noweta wrote: Hello, While working on the Perl DBD:SQLite driver, I found the following bug in sqlite : the last_insert_rowid() method (or SQL function) returns the constant value 10 after any insert into a fts5 virtual table. This bug is new in fts5 : previous

Re: [sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Hello, While working on the Perl DBD:SQLite driver, I found the following bug in sqlite : the last_insert_rowid() method (or SQL function) returns the constant value 10 after any insert into a fts5 virtual table. This bug is new in fts5 : previous versions fts4 and fts3 returned the correct rowi

[sqlite] last_insert_rowid() returns wrong value after insert in a fts5 virtual table.

Hi, While working on the Perl DBD:SQLite driver, I found the following bug in sqlite : the last_insert_rowid() method (or SQL function) returns the constant value 10 after any insert into a fts5 virtual table. This bug is new in fts5 : previous versions fts4 and fts3 returned the correct rowi

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

> Keith: > > The documentation for sqlite3_last_insert_rowid clearly states that it > returns the rowid last inserted on the connection, as does the documentation > for sqlite3_changes. While I agree that it may very well be possible to > maintain the data by statement, that is not what the cu

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

Thanks. I'm reading you with attention. > Clemens: > Please note that transactions work on the connection level. That was clear. > Simon: > Which, of course, decreases the point of you having competing threads in the > first place. Which is related to the FAQ pointing at >

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

On Sunday, 13 December, 2015 17:04, Olivier Mascia wrote: > Thanks. I'm reading you with attention. > > Clemens: > > Please note that transactions work on the connection level. > That was clear. > > Simon: > > Which, of course, decreases the point of you having competing threads in > the fir

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

On 13 Dec 2015, at 5:34pm, Clemens Ladisch wrote: > Olivier Mascia wrote: >> should the design of competing threads revolve around each one having >> a distinct connection handle? > > Yes. Which, of course, decreases the point of you having competing threads in the first place. Which is rela

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

Olivier Mascia wrote: > even if using two distinct sqlite3_stmt* handles (linked to a same > sqlite3* handle), two competing threads would get unusable answers > from both these API. Yes. Please note that transactions work on the connection level. > should the design of competing threads revolve

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

Dear all, Why isn't there some: sqlite3_int64 sqlite3_stmt_last_insert_rowid(sqlite3_stmt*); int sqlite3_stmt_changes(sqlite3_stmt*); in addition to these: sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*); int sqlite3_changes(sqlite3*); Reading this: "If a sep

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

On Sun 2015-12-13 13:47, Simon Slavin wrote: > On 13 Dec 2015, at 5:34pm, Clemens Ladisch wrote: > > Olivier Mascia wrote: > >> should the design of competing threads revolve around each one having > >> a distinct connection handle? > > Yes. > Which, of course, decreases the point of you havi

Re: [sqlite] last_insert_rowid() with conflict

On Tue, Jun 19, 2012 at 11:02 AM, Baruch Burstein wrote: > If I have a column that has unique values, and the ON CONFLICT clause is > IGNORE, is there a way to get the rowid of the last insert (on > success) *or*the last conflict (on conflict). I would like to just > keep adding to the > table wit

[sqlite] last_insert_rowid() with conflict

If I have a column that has unique values, and the ON CONFLICT clause is IGNORE, is there a way to get the rowid of the last insert (on success) *or*the last conflict (on conflict). I would like to just keep adding to the table without worrying if there is a duplicate, but after each insert I need

Re: [sqlite] last_insert_rowid() question

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/27/2010 11:41 AM, Scott A Mintz wrote: > I know that I can use sqlite3_last_insert_rowid() to retrieve the value. > However, in a multi-threaded environment, it's possible for another thread > to do an INSERT before I get the first result. Wha

Re: [sqlite] last_insert_rowid() question

Quoth Scott A Mintz , on 2010-12-27 14:41:23 -0500: > I know that I can use sqlite3_last_insert_rowid() to retrieve the value. > However, in a multi-threaded environment, it's possible for another thread > to do an INSERT before I get the first result. That'll only affect the last_insert_rowid i

Re: [sqlite] last_insert_rowid() question

Scott A Mintz wrote: > If I have a table with an INTEGER PRIMARY KEY column, and I INSERT a new > row into the table with a NULL binding to that column, sqlite will > generate a unique value for me. > > I know that I can use sqlite3_last_insert_rowid() to retrieve the value. > However, in a multi

Re: [sqlite] last_insert_rowid() question

On 27 Dec 2010, at 7:41pm, Scott A Mintz wrote: > I know that I can use sqlite3_last_insert_rowid() to retrieve the value. > However, in a multi-threaded environment, it's possible for another thread > to do an INSERT before I get the first result. What's the best way to > retrieve the rowid?

[sqlite] last_insert_rowid() question

If I have a table with an INTEGER PRIMARY KEY column, and I INSERT a new row into the table with a NULL binding to that column, sqlite will generate a unique value for me. I know that I can use sqlite3_last_insert_rowid() to retrieve the value. However, in a multi-threaded environment, it's po

Re: [sqlite] last_insert_rowid & INTEGER PRIMARY KEY columns

>> [...] does >> >> db last_insert_rowid >> >> reliably return the_key of the most recently inserted database row, >> so that the returned value may safely (across vacuums etc) be used >> as a foreign reference to t's the_key column? > > Yes. Actually there *is* a caveat, which is that if the inse

Re: [sqlite] last_insert_rowid & INTEGER PRIMARY KEY columns

Eric Smith wrote: > The tcl interface spec says: > >> The "last_insert_rowid" method returns an integer which is the ROWID of >> the most recently inserted database row. > > So if I have > > db eval {CREATE TABLE t(the_key INTEGER PRIMARY KEY, data TEXT)} > > and I > > db eval {INSERT INTO t

[sqlite] last_insert_rowid & INTEGER PRIMARY KEY columns

The tcl interface spec says: > The "last_insert_rowid" method returns an integer which is the ROWID of > the most recently inserted database row. So if I have db eval {CREATE TABLE t(the_key INTEGER PRIMARY KEY, data TEXT)} and I db eval {INSERT INTO t VALUES(NULL, 'foo')} then does db

Re: [sqlite] last_insert_rowid() syntax

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karl Lautman wrote: > Thanks, Roger. Your second suggestion does the trick. The first, however, > returns: . Can you explain why? > Thanks again. Both pysqlite make the cursor an iterator. Remember that a query can return zero, one or more rows o

Re: [sqlite] last_insert_rowid() syntax

SQLite Database Subject: Re: [sqlite] last_insert_rowid() syntax -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karl Lautman wrote: > Can someone point out to me the syntax error in the following? I've omitted > the set-up code for brevity, but cur is a cursor with a connection to the

Re: [sqlite] last_insert_rowid() syntax

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karl Lautman wrote: > Can someone point out to me the syntax error in the following? I've omitted > the set-up code for brevity, but cur is a cursor with a connection to the > database. Thanks. > x = cur.execute('last_insert_rowid()') last_i

[sqlite] last_insert_rowid() syntax

Can someone point out to me the syntax error in the following? I've omitted the set-up code for brevity, but cur is a cursor with a connection to the database. Thanks. >>> x = cur.execute('last_insert_rowid()') Traceback (most recent call last): File "", line 1, in x = cur.execute('las

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

D. Richard Hipp wrote: > > I think the FTS virtual table is doing the INSERTs inside its xCommit > method, after the trigger has been exited. So the mechanism that > resets the last_insert_rowid when a trigger exits does not apply since > the trigger has already existed by the time the FTS

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

On May 22, 2008, at 3:10 PM, Dennis Cote wrote: > Bram de Jong wrote: >> >> I have found a bug which happens in both FTS2 and FTS3. >> >> The bug happens when a trigger updates an FTS table: the insert ID >> gets trashed: >> > I think both Richard and Scott may have misread this one a little bi

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Bram de Jong wrote: > > I have found a bug which happens in both FTS2 and FTS3. > > The bug happens when a trigger updates an FTS table: the insert ID gets > trashed: > > <<< > create table one > ( > id integer not null primary key autoincreme

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Bram, I don't think there is a solid workaround of the form you suggest. The last_insert_rowid() you're getting is for the segment in the internal segdir table, and is unrelated to the rowid of the overall virtual table. What you should be able to do is to call last_insert_rowid() IMMEDIATELY aft

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

On Tue, May 20, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > In Bram's case, he was surprised that the last-insert-rowid changed > because he is not thinking about how FTS works behind the scenes. And > this is reasonable. There is a lot of magic in FTS that programmers > are not

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Hello, On Tue, May 20, 2008 at 10:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > So it is not at all clear to me whether this behavior is a bug or a > feature. SQLite is doing what the documentation says it ought to do. > The question is, should the specification of what SQLite ought to do >

Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

On May 20, 2008, at 1:21 PM, Bram de Jong wrote: > Hello all, > > > I have found a bug which happens in both FTS2 and FTS3. > > The bug happens when a trigger updates an FTS table: the insert ID > gets trashed: > > <<< > create table one > ( >

[sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3

Hello all, I have found a bug which happens in both FTS2 and FTS3. The bug happens when a trigger updates an FTS table: the insert ID gets trashed: <<< create table one ( id integer not null primary key autoincrement, value text not null d

Re: [sqlite] last_insert_rowid

Mike Johnston <[EMAIL PROTECTED]> wrote: > So I'm using the last_insert_rowid() with no issue. The note at > http://www.sqlite.org/c3ref/last_insert_rowid.html states that the > function returns unpredictable results should "a separate thread" do > a new insert. Does that apply to a separate pro

Re: [sqlite] last_insert_rowid

Mike Johnston wrote: > So I'm using the last_insert_rowid() with no issue. The note at > http://www.sqlite.org/c3ref/last_insert_rowid.html states that the > function returns unpredictable results should "a separate thread" do > a new insert. Does that apply to a separate process as well? > > I

[sqlite] last_insert_rowid

Hi, So I'm using the last_insert_rowid() with no issue. The note at http://www.sqlite.org/c3ref/last_insert_rowid.html states that the function returns unpredictable results should "a separate thread" do a new insert. Does that apply to a separate process as well? I have two processes

Re: [sqlite] last_insert_rowid()

Mikey C <[EMAIL PROTECTED]> wrote: > Hi, > > Does last_insert_rowid() return the identity of the main insert or would it > return the id of a row inserted by a trigger if the main insert caused a 2nd > insert to occur on another table via a trigger? > Both. If last_insert_rowid() is called from

[sqlite] last_insert_rowid()

Hi, Does last_insert_rowid() return the identity of the main insert or would it return the id of a row inserted by a trigger if the main insert caused a 2nd insert to occur on another table via a trigger? SQL Server has a scope_identity() function to make sure you can get the original id regardl