last_insert_rowid() returns every growing list

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

last_insert_rowid() returns every growing list

The Tick
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.

PS. I tried the rss feed in thunderbird but all I get is a date and a
link to all the author's posts. It would be nice if the message body was
included with each message.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: last_insert_rowid() returns every growing list

Keith Medcalf

On Monday, 17 February, 2020 17:20, The Tick <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: last_insert_rowid() returns every growing list

Richard Hipp-3
In reply to this post by The Tick
On 2/17/20, The Tick <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: last_insert_rowid() returns every growing list

The Tick
In reply to this post by The Tick
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users