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 :-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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_rowid() does.
The last_insert_rowid() is the rowid of the most recent insert from
the current database connection into *any* table. The SQLite database
file does not keep track of the order of inserts, and so it cannot
determine the last insert on any particular table. It only has that
information for an individual connection. And it only keeps a single
integer which applies to the most recent insert, regardless of what
table was inserted into.
If you need to track the last insert into individual tables, and do so
globally, you can accomplish that using triggers.
To get the last_insert_rowid() from TCL, it is faster to use the "db
last_insert_rowid" TCL command.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 an ever-growing list of rowid's:
>-8x-
>package require sqlite3
>console show
>
>#file delete -force test.db
>sqlite3 sql test.db
>#sql eval {create table test (id integer primary key, number integer,
>data blob)}
>set a [clock seconds]
>set b [expr {[clock seconds] % 100}]
>set c [string repeat "\u3456" 25]
>sql eval { insert into test (id, number, data) values( $a, $b, $c ) }
># The next statement returns a list... but shouldn't it only have one
>element?
>set oid [sql eval { select last_insert_rowid() from test }]
>puts "\[[llength $oid]\] $oid"
>-8x-
>If the "file delete" and "create table" are uncommented, I only get a
>single rowid the first time of course.
>I am a complete sql novice so it's likely that it's a user error but I
>thought I'd ask.
last_insert_rowid() is an SQL Function that returns the last inserted rowid on
the connection. You have asked for that value to be returned as many times as
there are rows in the table test. If there is one row in the table test you
get the last_insert_rowid() value returned once. If there are 47583 rows in
table test you get that value returned for each of the 47583 rows.
select last_insert_rowid();
is to
select 1;
as
select last_insert_rowid() from test;
is to
select 1 from test;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
