Pardon the long post everyone, but it seems from the other posts there
is a large misconception to address here and I will attempt to do so as
briefly as possible (Also, feel free to chime in where I am vague or wrong):
On 2015-03-18 11:24 PM, Jason Vas Dias wrote:
> OK, I discovered the OR clause of the INSERT statement, so I'm trying:
>
> BEGIN TRANSACTION;
> INSERT OR ROLLBACK INTO db VALUES("This breaks a constraint");
> COMMIT;
>
> But now I get another error after the constraint violation :
> 'Error: cannot commit - no transaction is active'
This is exactly what should happen and exactly what you've asked for.
You started a transaction, instructed it to INSERT some values but
roll-back the transaction if the insert fails. So then the insert fails
and your transaction got rolled back... and then you told it to commit,
which can't possibly work because the transaction that was is now no
more, it got rolled back - remember?
SQL is not a programming language, it doesn't execute lines of code. It
can follow DATA instructions and you may string those together in a list
(as you do) for easier usage. If a transaction fails however, this is a
problem, not an opportunity for more coding, one typically needs to read
that error code in your program/connector/ODBC/list processor/whatever
and make a decision on what to do next (such as roll back and re-start
the transaction or do something else etc.) and then ask SQL to do it
based on your decision. SQL itself is not a decision-making program,
it's just a Database engine which tries to do stuff with Data that you
ask it to, and it will error out very unceremoniously if what you ask is
bogus.
Your job is to see what went wrong, fix it so that next time you ask it
to do the thing, it doesn't fail (i.e. you should have a mechanism in
place to make sure you are not asking it to insert duplicates, and if
this error still happens, you need to fix your mechanism). There is no
way to ask via SQL only to try this or that, and if it fails, maybe try
something else please - this is the decision-making domain of programs.
(btw: This is true for ALL SQL engines, not just SQLite)
You can however tell it to ignore duplicated requests, such as:
INSERT OR IGNORE INTO "t" (myVal) VALUES ('This is a duplicate');
Which will simply NOT insert the value when the duplication occurs and
continue without rolling back or moaning about it. You have to be sure
this is what you want to happen though. If you need it to error out,
then it will, but you can't ask it to pick another rabbit hole to go
down whence an error might have occurred.
> This seems very buggy to me.
That's ok - It only seems that way. SQLite is amongst the least-buggy of
the SQL engines (though not free of it - but the above is not a bug in
any way).
Also, Feel free to post questions - if you can say exactly what you want
to happen, lots of people here will gladly help you achieve that in the
best possible way or offer methods to get to the goal in more and better
ways than ever seen before. However, calling "bug" due to
misunderstanding will probably have the opposite effect.
Best of luck !
Ryan
