I use a separate SQLite connection for each thread to avoid such issues.
When does the "last inserted rowid" become obsolete?
In your example, do you mean to imply that the last inserted rowid may be
retrieved once and once only?
Why not just:
(lock mutex)
(execute query)
if sqlite3_changes() ** may still be 0, see below
then
rowid = sqlite3_last_insert_rowid()
else
rowid = undefined
(unlock mutex)
What about if the query creates more than 1 row (as in INSERT INTO ... SELECT
or even multiple VALUES tuples)? Why would you want to know only the last rowid
and not all of them?
If you need to count changes made by triggers and foreign keys you may need to:
(lock mutex)
Before = sqlite_total_changes
(execute query)
After = sqlite_total_changes
If (Before < After)
...
(unlock mutex)
Also note that INSERT OR IGNORE does not change the last inserted rowid.
I also suspect that INSERT OR REPLACE may very well change the last inserted
rowid (without tallying sqlite3_changes!!! See documentation).
-----Ursprüngliche Nachricht-----
Von: Mikael [mailto:[email protected]]
Gesendet: Montag, 06. Mai 2013 14:40
An: Richard Hipp; [email protected]
Betreff: [sqlite] How do sqlite3_reset_last_insert_rowid() or functional
equivalent?
Dear list,
After having made an SQLite statement the ID of a newly inserted row can be
retrieved with sqlite3_last_insert_rowid .
It'd be a great thing to be able to produce a general mechanism for retrieving
this value with regard to the most recently performed query only right after a
query has been made, as programming aimed at getting this value lazily won't
work as other local code may have made another query to the SQLite handle
meanwhile.
The most robust way to do this would be through having a
sqlite3_reset_last_insert_rowid() procedure to invoke right before a query,
because, sqlite3_last_insert_rowid is only updated on a successful insert.
The code to check if a query is non-readonly can be done using
sqlite3_stmt_readonly , but then the step from there to check if it's an
*insert* and not only that but a successful insert, is a huge step and possibly
the only reliable way to tell this, would be by SQLite telling it, and the most
straightforward way for it to do this would be through
sqlite3_last_insert_rowid , so then what about un-problematizing that value as
to guarantee it won't return any obsolete value, by introducing a
sqlite3_reset_last_insert_rowid() ?
Please let me know the best practice for solving this particular problem - the
ability to make a "Query" abstraction atop SQLite, that has its very own "ID of
row inserted" method, I believe is a reasonable aim.
(I.e., not having such a property but that is not guaranteed to actually
contain the right thing, depending on very specific circumstances.)
Example:
(mutex for sqlite3 lock)
sqlite3_reset_last_insert_rowid(sqlite3*);
(perform SQLite query on sqlite3*)
sqlite3_last_insert_rowid(sqlite3*) => Guaranteedly returns the rowid of any
row inserted by the query, or 0 if no insert was done.
(mutex for sqlite3 unlock)
[starting here sqlite3_last_insert_rowid(sqlite3)'s return value is undefined]
Thanks,
Mikael
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--------------------------------------------------------------------------
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [email protected]
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
