Error handling and RollBack Transaction in SQLITE from SQL Statement

Question

I'm Altering multiple sqlite tables with SQL script by calling ExecuteNonQuery. I want to do this operation in transaction and want to roll it back when anything fails.

I looked at BEGIN TRANSACTION and its clear that I have to call ROLLBACK TRANSACTION when anything goes wrong. But I don't know how could TRY...CATCH (Transact-SQL) kind of thing here.

NOTE: Whole of Sql Script file (which contains many other statements apart from these few statements which needs to be fired in one transaction) is read by .ReadToEnd() and then executed in one go as of now. I want to handle this in sql script file itself and don't want to change the code.

ralf.w. · Accepted Answer · 2011-03-15 20:10:40Z

6

Please take a look at SQLite on conflict clause

Start with BEGIN TRANSACTION

You have to add ON CONFLICT ROLLBACK on your actions

And COMMIT TRANSACTION at the end ;-)

answered Mar 15, 2011 at 20:10

ralf.w.

1,6941 gold badge25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

IsmailS Over a year ago

+1 ON CONFLICT ROLLBACK will go at the end of my statment or in the begining? For example if I'm updating a table in one of the many statments like Update Orders Set Name = 'xyz' Where OrderId = 3 ON CONFLICT ROLLBACK. Is that right?

IsmailS Over a year ago

As I read it on the link provided. I think that its Update OR ROLLBACK Orders Set Name = 'xyz' Where OrderId = 3 or it can be written as UPDATE ON CONFLICT ROLLBACK Orders Set Name = 'xyz' WHERE OrderId = 3.

IsmailS Over a year ago

But will this roll back in case when there is a syntax error or something else goes wrong? Because it is written on the link you gave that

The ON CONFLICT clause applies to UNIQUE, NOT NULL, and CHECK constraints (and to PRIMARY KEY constraints which for the purposes of this section are the same thing as UNIQUE constraints). The ON CONFLICT algorithm does not apply to FOREIGN KEY constraints.

ralf.w. Over a year ago

If there is a syntax-error the statement itself will never be executed and there can be no rollback

C Perkins Over a year ago

This answer fails to consider statements for which ON CONFLICT does not apply, e.g. DROP, CREATE TABLE (the entire statement, not just column definitions) , etc. The specified pattern does not work as a general substitute for TRY...CATCH in T-SQL or proper error handling and transaction rollback in the host environment/language.

|

Collectives™ on Stack Overflow

Error handling and RollBack Transaction in SQLITE from SQL Statement

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related