That are SQL 101 questions, this is a Pharo list ;-) You should wrap your SQL statements in a transactions, https://sqlite.org/lang_transaction.html
On 16 Jul 2016, at 08:19, Hilaire <hilaire@drgeo.eu> wrote:
Hi guys,
In a multi-thread context use of Sqlite, I try to find out how one will fetch securely the automatically last row index of a Primary key.
The problem seems to be that between the insert of rows in a table and the moment you request the last row id, you may have another insert from another thread in the same database.
In the code bellow, I put the insert and last_insert_rowid() call in a transaction. The returned rowid value is the right one as expected, and now I am wondering if enclosing in a transaction is enought to make the last_insert_rowid() safe.
Any trips how you handle this situation?
Thanks
Hilaire
The organisation table is defined as: CREATE TABLE organisation ( idOrg INTEGER PRIMARY KEY, name VARCHAR(30), address TEXT, zipcode INTEGER, city VARCHAR(30), phone VARCHAR(15), email VARCHAR(20) );'
The code is as follow:
| row res s id | database execute: 'INSERT INTO organisation (name) values (''Pharo consortium'')'. res := database beginTransaction. self assert: res = 0. s := database prepare: 'INSERT INTO organisation (name, address, zipcode, city, phone, email) values (?, ?, ?, ?, ?, ?)'. s at: 1 putString: 'Dupont & co'. s at: 2 putString: '12, rue du pont'. s at: 3 putInteger: 75000. s at: 4 putString: 'Paris'. s at: 5 putString: '0105060708'. s at: 6 putString: 'contact@dupont.fr'. s step. s finalize. row := database execute: 'SELECT last_insert_rowid()'. res := database commitTransaction. id := row next at: 'last_insert_rowid()'. row close. self assert: res = 0. self assert: id = 2.
-- Dr. Geo http://drgeo.eu