This the "happy path" and you can rely on IdEnd as being the ID you look for.Įlse you have to need to do an extra SELECT where you can use criteria based on IdLast to IdEnd (any additional criteria in WHERE clause are good to add if any) In the vast majority of cases IdEnd=IdLast+1. Int IdEnd = sqlite3_last_insert_rowid(m_db) // After Insertion most probably the right one, #1 BEFORE my Insert, then #2 AFTER my insertĪs in : int IdLast = sqlite3_last_insert_rowid(m_db) // Before (this id is already used)Ĭonst int rc = sqlite3_exec(m_db, sql,NULL, NULL, &m_zErrMsg) Small but unacceptable risk.Ī workaround is to call twice the sqlite3_last_insert_rowid() If need traditional PK AUTOINCREMENT, yes there is a small risk that the id you fetch may belong to another insertion.
See also if you absolutely need to fetch SQlite internal PK, can you design your own predict-able PK: See if the clause on RETURNING won't solve your issueĪs this is only available in recent version of SQLite and may have some overhead, consider Using the fact that it's really bad luck if you have an insertion in-between your requests to SQLite ID reservation is NOT implemented in SQLite, you can also avoid PK using your own UNIQUE Primary Key if you know something always variant in your data. However the good news is that you can play with the chance, see below Sqlite3_last_insert_rowid() is unsafe in a multithreaded environment (and documented as such on SQLite) UpdateMessagesRecipients() //method not shown MessagesRecipients.AddMessages_RecipientsRow(row) Var row = messagesRecipients.NewMessages_RecipientsRow() reference to the new row in the Messages table, but it does not.įoreach (var recipient in message.Recipients) Var x = messagerow //I hoped the messagerow would hold a Messagerow=messages.AddMessagesRow(message.Sender, Pm_databaseDataSet.MessagesRow messagerow
Sqlite autoincrement only unique code#
My Persistence code (messages and messagesRecipients are DataTables): public void Persist(Message message) But how do I call this statement from ADO.Net? But how can I hold a reference to the MessageRow I just added in order to retrieve this new MessageID? I can always search the database for the last row added of course, but that could possibly return a different row in a multithreaded environment?ĮDIT: As I understand it for SQLite you can use the SELECT last_insert_rowid(). The ID of a new message will be created after it has been stored in the database. This table represents the many to many relation between recipients and messages.
I created a table Messages_Recipients with two columns: MessageID (referring to the ID column of the Messages table and Recipient (referring to the username column in the Users table). I have a table Users with columns username (primary key, text) and Hash.Ī message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages. I have a table Messages with columns ID (primary key, autoincrement) and Content (text).
0 kommentar(er)
