$dbh->begin_work is a Perl DBI method that applies generically to any DB accessible via the DBI. The SQLite SQL command would be "BEGIN TRANSACTION", but don't do that. Use the generic DBI method because in MySQL this is "START TRANSACTION". Do not mess around with autocommit. Leave it alone and at the default of "1" or "on". When you issue a begin_work, that will automatically turn autocommit off until the commit is seen.

I think there is some misunderstanding about $dbh->rollback. In general, you do not need to do that. When a transaction starts, SQLite will create a journal file on disk to keep track of what it is doing with the transaction. Nothing at all is changed in the DB until the commit. At the commit, all pending operations are done at once (this is very quick). At that point the changes become permanent to the DB.

So what happens if your program dies during a transaction? This will leave remnants of the aborted transaction in various SQLite log files. The next time you connect to SQLite, it will see this garbage associated with a connection that no longer exits and it will clean up all that stuff automagically. There is no need for you to "rollback".

In most circumstances, there is no obvious corrective action that can be taken in light of a failed SQL operation. Reporting the SQL error, line number, and stopping is about all you can do. Now if you do have a sophisticated program that can keep going after a failure, then a rollback will clear out the pending transaction's operations so that you can start anew without restarting your program/re-connecting to the DB.

So if you are in the simple model of "I am going to stop if my DB operation fails", enable RaiseError and don't worry about checking for error flags or any sort of try/catch stuff - just do the DB operations and let error reporting and death happen if errors.

If you are an experienced DB developer and have a sophisticated app, then there are multiple error recovery strategies that can be used. My advice is to stay away from complicated stuff if you don't need complicated stuff.

In reply to Re^4: SQL: Update column(s) value with extra WHERE by Marshall
in thread SQL: Update column(s) value with extra WHERE by bliako

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.