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 <hila...@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: 'cont...@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
>
>
