On 05-09-15 22:27, Simon Slavin wrote: > On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk? <lazna at volny.cz> wrote: > >> Have some shell scripts working with sqlite. Receiving incoming payments >> from bank via HTTP API and pushing it into database. This script will start >> periodically, every single hour. >> >> Want to prevent situation only few payments are written and script failed >> for some reason. Have read about sqlite transactions and understood so I >> should start every writing sequence with BEGIN; statement, than made all >> inserts and than COMMIT; statement. Is this all how it is working? Should I >> do something more to prevent such unwanted situation? > You have it correct. If you put multiple change commands in one transaction, > then either they are all executed or none of them are executed. Even if your > program crashes in the middle of a command, when SQLite opens the file again > it works out what happened and restores a 'clean' database. > > Theoretically you would have to always open a transaction for SQL. Issuing > INSERT before BEGIN would result in an error message. But SQL is kind and > opens a one-command transaction if you forget. >
Suppose i have 'test.sql': .echo on DELETE FROM test; BEGIN; INSERT INTO test VALUES(1,'test1'); INSERT INTO test VALUES(3,'test3',3); INSERT INTO test VALUES(2,'test2'); COMMIT; SELECT * FROM test; And a database 'test.sqlite' with the following table: PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test(i int, t text); COMMIT; If i do: type test.sql | sqlite3.exe test.sqlite Than there is an error executing this line: INSERT INTO test VALUES(3,'test3',3); But the transaction is not stopped, or rolled back. What am i missing? Output of 'type test.sql | sqlite3.exe test.sqlite': DELETE FROM test; BEGIN; INSERT INTO test VALUES(1,'test1'); Error: near line 5: table test has 2 columns but 3 values were supplied INSERT INTO test VALUES(2,'test2'); COMMIT; SELECT * FROM test; 1|test1 2|test2
