Well as far as portability goes, you are correct in that BEGIN is not technically an offical SQL statement, but rather an extremely common alias for the standard SQL command "START TRANSACTION". I am unaware of a DB that doesn't have the BEGIN alias. But I guess there could be such a critter out there. COMMIT is a standard SQL command.

I am certainly not an SQL guru and most of my DB code is fairly straightforward. The Perl DBI is fantastic in how easily command line SQL can be converted into Perl code. I tend to debug my SQL operations from the SQL command line and then transfer that knowledge into Perl DBI code. From that perspective, using BEGIN and COMMIT makes some sense and fits with my work flow. However as youi point out, there are DBI method calls for these SQL commands. I suspect that there is some efficiency to using them. However for me, a typical transaction is 500 operations and that would make no difference.

Error handling is a big, a very big subject,
I normally use the option RaiseError=>1 in the connect. If a fatal SQL error occurs, the program bombs. In a case like was posted, if a fatal error happens, the transaction fails. No rollback is needed. When the connection to the DB is lost, the DB will throw away the pending transaction (because COMMIT never occured).

When you go to the trouble of trapping an error (as you show), You have to explictly do something to rollback or otherwise cancel the pending transaction. Then the question becomes "Now What?". If there is no answer to that question, then trapping the error doesn't matter.

I have applications that are completely "restartable" as long as each transaction either fully works or doesn't work, the application can just be run again from scratch with an updated input file. Of course in some instance like shown, where a "delta" is being applied to the DB, that simple approach won't work. Lots of possilities that seemed to be outside the scope of the OP's question...

In reply to Re^3: run 2 query mysql by Marshall
in thread run 2 query mysql by bigup401

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.