[sqlite] how to determine the status of last statement in sqlite3 shell ?

[James K. Lowden]

On Wed, 18 Mar 2015 21:15:20 +
Simon Slavin  wrote:

> 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.

$ sqlite3 :memory:<", writing standard SQL, and relying
on -bail to exit on error, whereupon SQLite reaches end-of-input
without an explicit COMMIT, and rolls back.  

--jkl

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Simon Slavin]

On 19 Mar 2015, at 12:29am, Keith Medcalf  wrote:

> A statement may fail, however that does not affect other statements within 
> the transaction.  You still have to end a transaction with either a commit 
> (to commit the changes made by statements WHICH DID NOT FAIL) or rollback to 
> discard the changes made by the statements which did not fail.

Then what is the BEGIN for ?

Hmm.



"a transaction will also ROLLBACK if the database is closed or if an error 
occurs and the ROLLBACK conflict resolution algorithm is specified."

"If certain kinds of errors occur within a transaction, the transaction may or 
may not be rolled back automatically."



"The ON CONFLICT clause is not a separate SQL command. It is a non-standard 
clause that can appear in many other SQL commands. It is given its own section 
in this document because it is not part of standard SQL"

Oh dear.  It's non-standard SQL.  And the answer is that it depends.

The default behaviour is as you wrote, however.  So I was wrong.  Thanks for 
the correction.

Simon.

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Petite Abeille]

> On Mar 18, 2015, at 9:51 PM, Jason Vas Dias  
> wrote:
> 
> I don't see how anything like that is possible in the sqlite3 shell .

Not in the shell per se, no (.bail on|off may or may not help in your case). 

Perhaps ON CONFLICT clause might help you:

https://www.sqlite.org/lang_conflict.html

Or you can trail all your transactions with a commit; rollback; pair.  

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Simon Slavin]

On 18 Mar 2015, at 8:51pm, Jason Vas Dias  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.

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Jason Vas Dias]

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 ?
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 ?

[sqlite] how to determine the status of last statement in sqlite3 shell ?

[Keith Medcalf]

>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.

This is incorrect.  

A statement may fail, however that does not affect other statements within the 
transaction.  You still have to end a transaction with either a commit (to 
commit the changes made by statements WHICH DID NOT FAIL) or rollback to 
discard the changes made by the statements which did not fail.

If you however submit a statement BATCH (that is, say 400 INSERT statements as 
a single line of text) and ONE of them ABORTS, then the batch is aborted (that 
is, no further statements in the batch are executed, from that point on -- 
statements which have executed successfully are NOT rolled back by magic).  
COMMIT will result in committing the database operations in the batch prior to 
the ABORT, and ROLLBACK will roll them all back.

The default conflict resolution method for INSERT is ABORT.  That means that 
the current statement is aborted and no further statements in the same batch 
(text string submitted for execution) are executed.  It has no effect 
whatsoever on the state of the transaction, which is still open.

This is why there is a conflict resolution method IGNORE:

>sqlite < abort2.sql
Error: near line 2: no such table: foo
create table foo( value text, constraint uk unique( value ) );
begin transaction;
insert or ignore into foo( value ) values( 'bar' );
commit;
begin transaction;
insert or ignore into foo( value ) values( 'bar' );
commit;
begin transaction;
insert or ignore into foo( value ) values( 'baz' );
commit;
begin transaction;
insert or ignore into foo( value ) values( 'baz' );
commit;


** In my original example, abort1.sql contains:

.echo on
drop table foo;
create table foo( value text, constraint uk unique( value ) );
begin transaction;
insert into foo( value ) values( 'bar' );
commit;
begin transaction;
insert into foo( value ) values( 'bar' );
commit;
begin transaction;
insert into foo( value ) values( 'baz' );
commit;
begin transaction;
insert into foo( value ) values( 'baz' );
commit;

** abort2.sql now contains (INSERT changed to INSERT OR IGNORE):

.echo on
drop table foo;
create table foo( value text, constraint uk unique( value ) );
begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit;
begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit;
begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit;
begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit;


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.