Rollback transaction if error

[BareFeetWare]

Hi all,

I want to dynamically construct SQL queries that rollback if any part of the transaction fails. For instance, my app constructs the SQL needed to replace a trigger definition, such as:

begin immediate;
drop trigger if exists "My Trigger";
create trigger "My Trigger"
... new definition
;
commit;

If the new definition fails, I want to rollback so that the old definition remains.

I was under the impression that if any error occurred in the transaction that SQLite would rollback, but it seems that it only does this in some cases. According to:
http://www.sqlite.org/lang_transaction.html

Response To Errors Within A Transaction
• SQLITE_FULL: database or disk full
• SQLITE_IOERR: disk I/O error
• SQLITE_BUSY: database in use by another process
• SQLITE_NOMEM: out or memory
• SQLITE_INTERRUPT: processing interrupted by application request
For all of these errors, SQLite attempts to undo just the one statement it was working on and leave changes from prior statements within the same transaction intact and continue with the transaction. However, depending on the statement being evaluated and the point at which the error occurs, it might be necessary for SQLite to rollback and cancel the entire transaction.

So, it seems I have to, in my application code, step through each command in the SQL transaction, note any error, then when it gets to a "commit" command, replace it instead with a "rollback".

Is there a way to do this in pure SQL, without my application code having to check for errors along the way and then interrogate the SQL to look for a "commit" type line and replace it? This seems pretty error prone and convoluted.

Thanks,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

[Drake Wilson]

Post by BareFeetWare
Is there a way to do this in pure SQL, without my application code
having to check for errors along the way and then interrogate the
SQL to look for a "commit" type line and replace it? This seems
pretty error prone and convoluted.

Why would you introspect the SQL rather than generating the beginning
and end of the transaction from a different part of the application
code to each statement inside? Begin transaction, then start
executing statements from a list; if any of them fail, stop executing
further statements and roll back, otherwise commit at the end. Don't
include the begin/end in the list. Does that not work for you?

Post by BareFeetWare
Thanks,
Tom
BareFeetWare

---> Drake Wilson

[BareFeetWare]

Post by Drake Wilson

Post by BareFeetWare
Is there a way to do this in pure SQL, without my application code
having to check for errors along the way and then interrogate the
SQL to look for a "commit" type line and replace it? This seems
pretty error prone and convoluted.

Why would you introspect the SQL

I want to avoid having to introspect.

Post by Drake Wilson
rather than generating the beginning and end of the transaction from a different part of the application code to each statement inside? Begin transaction, then start executing statements from a list; if any of them fail, stop executing further statements and roll back, otherwise commit at the end. Don't
include the begin/end in the list. Does that not work for you?

It works, but it requires a whole extra level of interaction, whereas I want to SQL to take care of it itself, without needing to dissect and branch the SQL in application code.

I want to be able to just send a series of SQL commands in a transaction as a one block of text and have SQLite tell me either that it succeeded so committed, or that it failed so everything has been rolled back. It is my understanding that this is one of the primary purposes of transactions. In this way, I can already string together a series of inserts, updates and deletes and execute as one block in a transaction, I'd like to be able to do the same with other SQL commands (eg drop and create).

Thanks,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

[Max Vlasov]

Post by BareFeetWare
I want to be able to just send a series of SQL commands in a transaction as
a one block of text and have SQLite tell me either that it succeeded so
committed, or that it failed so everything has been rolled back. It is my
understanding that this is one of the primary purposes of transactions. In
this way, I can already string together a series of inserts, updates and
deletes and execute as one block in a transaction, I'd like to be able to do
the same with other SQL commands (eg drop and create).

Tom, I think that the idea is to move only first (begin) and last
(commit/rollback) from you queries. In this case your queries can still
contain other queries like drop and create. You just have to check for error
in step command and, analyze sqlite3_get_autocommit() and do rollback
optionally (or do it anyway with a error being not error). Anyway, I think
that getting rid of transaction commands make you queries more portable
since you will be able to concatenate them before wrapping into begin/end

Max Vlasov

[BareFeetWare]

I want to be able to just send a series of SQL commands in a transaction as one block... and have SQLite tell me either that it succeeded so committed, or that it failed so everything has been rolled back. It is my understanding that this is one of the primary purposes of transactions. In this way, I can already string together a series of inserts, updates and deletes and execute as one block in a transaction, I'd like to be able to do the same with other SQL commands (eg drop and create).

Tom, I think that the idea is to move only first (begin) and last (commit/rollback) from you queries. In this case your queries can still contain other queries like drop and create. You just have to check for error in step command and, analyze sqlite3_get_autocommit() and do rollback optionally (or do it anyway with a error being not error). Anyway, I think that getting rid of transaction commands make you queries more portable since you will be able to concatenate them before wrapping into begin/end

Thanks for the reply, but you're kind of restating the problem.

Yes, I know that in my application code I can step through each command in the transaction, check for an error, and if there is an error, end the transaction with a "rollback" instead of a "commit". But I want to avoid having to check the result of each command in application code. I want the SQL to take care of the commit/rollback decision so I can just give SQLite a transaction block that will work as a whole or else just rollback and have no effect. Then I can send that SQL transaction block via whatever SQLite front end (eg the sqlite3 command line) I like, so it's portable.

This works for transactions containing insert, update and delete, but not for create and drop commands, which seems inconsistent to me. If I put these commands inside a transaction, surely I want to have the transaction rollback if anything fails within it.

Thanks,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

[Max Vlasov]

Post by BareFeetWare
. I want the SQL to take care of the commit/rollback decision so I can just
give SQLite a transaction block that will work as a whole or else just
rollback and have no effect. Then I can send that SQL transaction block via
whatever SQLite front end (eg the sqlite3 command line) I like, so it's
portable.

Hmm, I thought that for semicolon-delimited statements you have to iterate,
either with your own full steps or with sqlite3_exec loop (with an exception
of trigger syntax). So either way SQLite knows only about the query
currently executing. So when you tell "give SQLite a transaction block" what
api you mean by that?

Max

[Roger Binns]

Post by BareFeetWare
Yes, I know that in my application code I can step through each command in the transaction, check for an error, and if there is an error, end the transaction with a "rollback" instead of a "commit".

What programming language are you using?

For Python we use exceptions which makes doing the above really easy.
Rather than checking error codes, exceptions are thrown. Calling code
catches the exception and rolls back.

execute("BEGIN")
try:
execute(... whatever ...)
execute("COMMIT")
except:
execute("ROLLBACK")

If you are using a language without exceptions then you may be able to
emulate something like that through macros or other mechanisms.

BTW the above is a little verbose for explanatory purposes. In Python you'd
actually reduce it to two lines which does all of the above behind the scenes:

with connection:
connection.execute(... whatever ...)

Roger