Running Win2K, with all current patches, I use the SQLite3.exe program to
create a database named "test.db" using the .read command. The file
"test.sql" contains the following table definition:
create table Author
(
AuthorKey integer primary key not null,
LastName varchar (40) not null,
FirstName varchar (20) null,
Middle char (5) null
);
create index IX_Author_1 on Author(LastName, FirstName);
C:\dbtest>sqlite3.exe test.db
SQLite version 3.0.4
Enter ".help" for instructions
sqlite> .read test.sql
Now, I execute a few inserts one at a time, and after each one do a "select
last_insert_rowid()" from the table to get the last key I inserted. It
works fine the first time, but every subsequent time, I get as many rows
(all with the same value, the last rowid) returned as there are records in
the table. I later added two rows at once, and once again got as many rows
returned as there are records.
A transcript is below.
sqlite> insert into author (AuthorKey, LastName, FirstName, Middle) values
(null, "Smith", "John", "Q." );
sqlite> select last_insert_rowid() from Author;
1
sqlite> insert into author (AuthorKey, LastName, FirstName, Middle) values
(null, "Jones", "James", "J." );
sqlite> select last_insert_rowid() from Author;
2
2
sqlite> insert into author (AuthorKey, LastName, FirstName, Middle) values
(null, "Doe", "Jane", "F." );
sqlite> select last_insert_rowid() from Author;
3
3
3
sqlite> insert into author (AuthorKey, LastName, FirstName, Middle) values
(null, "Babe", "D.", "Licious" );
sqlite> insert into author (AuthorKey, LastName, FirstName, Middle) values
(null, "Horndog", "M.", "F." );
sqlite> select last_insert_rowid() from Author;
5
5
5
5
5
Is this just the SQLite3.exe doing this, or is it the library itself? I
don't use the library directly, rather I use a wrapper written by someone
else, so I can't test that directly myself. The wrapper I'm using doesn't
return more than one value (though I don't know if that's because the
library doesn't, or because something in the wrapper is filtering it.)
