OK, I discovered the OR clause of the INSERT statement, so I'm trying:
BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO db VALUES("This breaks a constraint");
COMMIT;
But now I get another error after the constraint violation :
'Error: cannot commit - no transaction is active'
which gets back to the original point of this post,
which is that SQLite is evidently not considering
all text within 'BEGIN TRANSACTION; ... ; $EOT;'
to be part of the same transaction (for $EOT in COMMIT, ROLLBACK, END
TRANSACTION etc.) ; or if a transaction fails, it considers the next
transaction to be part of the failed transaction (and so disallows
another BEGIN TRANSACTION ).
This seems very buggy to me.
On 18/03/2015, Simon Slavin <slavins at bigfraud.org> wrote:
>
> 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.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
