On 18 Mar 2015, at 8:11pm, Jason Vas Dias <jason.vas.dias at gmail.com> wrote:
> The problem is, if this transaction runs in a session, then
> NO transactions can ever run again in that session - eg.
> if I try to run the same transaction twice :
> On linux command line:
> $ echo '
> BEGIN TRANSACTION;
> INSERT INTO db VALUES("This breaks a constraint");
> COMMIT;
> BEGIN TRANSACTION;
> INSERT INTO db VALUES("This breaks a constraint");
> COMMIT;
> ' | sqlite3 my_db_file.db
> Error: near line 1: UNIQUE constraint failed: db.some_field
> Error: near line 2: cannot start a transaction within a tranaction
>
> It is the second error that worries me, since it shows that the
> first failed transaction was not closed, even though I had clearly
> written "COMMIT;" at the end of it, and no transactions
> can ever run again in the same session.
Which Unix/Linux command shell are you using ? I'm guessing that what you're
seeing makes no sense because of the way the sqlite3 is receiving the lines
from the shell. If I execute the commands singly I get this:
164:Desktop simon$ sqlite3 ~/Desktop/test.sdb
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (myCol TEXT UNIQUE);
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
sqlite> BEGIN;
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
Error: UNIQUE constraint failed: myTable.myCol
sqlite> COMMIT;
sqlite> BEGIN;
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
Error: UNIQUE constraint failed: myTable.myCol
sqlite> COMMIT;
sqlite>
which is fine. If I then use your command in bash I get this:
164:Desktop simon$ echo '
> BEGIN TRANSACTION;
> INSERT INTO myTable VALUES("This breaks a constraint.");
> COMMIT;
> BEGIN TRANSACTION;
> INSERT INTO myTable VALUES("This breaks a constraint.");
> COMMIT;
> ' | sqlite3 ~/Desktop/test.sdb
Error: near line 3: UNIQUE constraint failed: myTable.myCol
Error: near line 6: UNIQUE constraint failed: myTable.myCol
164:Desktop simon$
which is again fine.
Simon.
