On 18 Mar 2015, at 8:51pm, Jason Vas Dias <jason.vas.dias at gmail.com> wrote:
> When the sqlite3 shell is used to run a long stream of commands , eg. from > a script file or input pipe from another process, how can the success / > failure > status of the last statement be determined ? > IE. if the shell has just run an insert statment : > BEGIN TRANSACTION; INSERT INTO db VALUES(...); COMMIT; > how can the next statement determine if the previous statement failed ? It cannot. There's no way to do this inside the SQL command-stream apart from reading what you just tried to write and seeing if it's there. > Or, can the insert statement transaction determine if it has failed or not, > ie. > is it possible to do something like: > BEGIN TRANSACTION; INSERT INTO db VALUES(...); > ON SUCCESS: COMMIT; > ON FAILURE: ROLLBACK; > I don't see how anything like that is possible in the sqlite3 shell . > Any ideas ? If the only thing you're using the error for is to decide whether to COMMIT or ROLLBACK then you may have misunderstood how transactions work. A transaction is ended when you issue the COMMIT command. A transaction fails if any command which changes the database in it fails due to violating the schema. If a transaction fails then all commands in it are automatically ignored. There's no need to use ROLLBACK. You correctly grouped commands together into a transaction and SQL knows that if any of them fail none of them must be executed. A program would use ROLLBACK only if it decides that the whole transaction was a bad idea itself -- not because a command violated the schema and resulted in an error, but perhaps because the user hit an 'abort' button or because a long transaction failed to finish before end-of-day. Simon.
