Luuk <luuk34 at gmail.com> writes: > 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.
Not quite. Even if some statement failed, if you COMMIT in the end, it will succeed and database will be (partially) modified. It's application developer responsibility to check for errors and issue ROLLBACK instead of COMMIT, if desirable (BTW, in case of some errors^[], sqlite3 may cancel [and automatically ROLLBACK] transaction, it is application developer responsibility to check if transaction is still active after errors [with sqlite3_get_autocommit()]). ^[] IIRC, exact conditions are not documented, this is implementation-defined behavior. >> 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. (yes, if program crashes (or closes connection) before COMMIT was executed, it means "implicit ROLLBACK"). >> 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 Add .bail on here. > 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 normally I'd expect to see DELETE *inside* transaction); > 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
