I have a base class, Generic::Database, which handles all interactions with MS SQL Server. The actual SQL is generated by classes that inherit from Generic::Database. I am using the DBI module and have autocommit set to false. Most operations on the database are going to be simple updates of one record. For these operations, I decided to handle committing the transaction with the DESTROY method in the base class (these are ISAPI scripts and therefore not persistent database connections). The DESTROY method looks like this:

sub DESTROY {
    my $self = shift;
    if ( $self->{ _error } ) {
        cluck "There was a problem with the transaction: $self->{ _err
+_str }";
        $self->{ _dbh }->rollback;
    } else {
        $self->{ _dbh }->commit;
    }
    $self->{ _dbh }->disconnect;
}

[download]

For most of the program, this works fine. However, for some (not all) update sql statements, the DESTROY method is never triggered and I need to explicitly call $dbh->commit for them:

sub update_admin_message {
    my ( $self, $data_hash ) = @_;
    my ( $fields, $values ) = $self->_format_update_data( $data_hash )
+;
    my $sql = "UPDATE adminMessage SET $fields";
    my $data = $self->_update_database( $sql, $values );
    $self->{ _dbh }->commit if ! $self->{ _error };
}

[download]

The update_admin_message is in the Generic::Database::Modify class which inherits from Generic::Database. If I take out the last line of that method, the update silently fails because DESTROY is never called. Any ideas?

Cheers,
Ovid

Vote for paco!

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

In reply to Committing database transactions with DESTROY by Ovid

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.