One solution could be moving $dbh variables to a higher level scope, so that the objects they refer to are not disposed of while you still need them. Objects may still see them as lexicals through references, aliasing, using our, whatever.

You could also use the eval BLOCK syntax to check how a certain section of code behaves and then go for a commit or a rollback. This way, you eval BLOCK acts a little "program", but when it "exits", control returns to your code and not to the operating system. Exceptions can be caught this way.

Regarding portability, I found the DBI interface to be very database independent. Unfortunately there is no SQL abstraction layer in DBI. This, in conjunction with the fact that many programmers think they're smarter that the quote method, yields to total mess when SQL statements have to be ported.

Like I said the typical example is quoting in SQL strings, happily done with the s/// operator (if done at all) and not with the database independent quote method.

Also, variable interpolation in double quotish strings is sometimes abused, while placeholders could do a much better job, e.g. deciding where a value should be enclosed in quotes or not. Some RDBMSs (for example DB2) might be very sensitive on this point, while others (namely MySQL) aren't so fussy...

The third aspect in database independence is always related to SQL statements, but this time it's about using one RDBMS' own functions. MySQL has loads of functions and they can be very useful indeed, but one must be prepared to reimplement them in another RDBMS when not available (DB2 allows this) or to find the equivalent (e.g. UPPER() in some RDBMSs is UCASE in others).

Finally, SQL can be used for querying, inserting, changing, deleting records but also for database administration tasks, and the latter aspect is almost always unportable regardless of DBI's completeness.

-- TMTOWTDI

In reply to Re: commtting db transactions by trantor
in thread commtting db transactions by d_i_r_t_y

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.