Hi all -

I'm writing a module that receives a DBI database handle as input to my constructor. I'd like to be able to test if the handle is transaction-capable, so that I can conditionally wrap certain code in a transaction like so:

 if ($self->{_can_do_transactions}) {
     local $dbh->{AutoCommit} = 0;
     eval {
         code_that_touches_db();
         $dbh->commit;
     };
     if ($@) {
         my $msg = $@;
         eval { $dbh->rollback };
         die $msg;
     }
 }
 else
 {
     code_that_touches_db();
 }
[download]

First question, is there a better way to do this?

UPDATE: Thanks to feedback, am now using something like this to handle transactions:

 if ($self->{_can_do_transactions}) {

     # If RaiseError is true, begin_work() will:
     #     return true if a new transaction was started
     #     croak if already in a transaction
     #     croak if transactions not supported
     #
     local $dbh->{RaiseError} = 1;
     my $started_a_new_transaction = 0;
     eval { $started_a_new_transaction = $dbh->begin_work };

     eval {
         $code->(@_);
         $dbh->commit if $started_a_new_transaction;
     };
     if ($@)
     {
         my $msg = $@;
         eval { $dbh->rollback } if $started_a_new_transaction;
         die $msg;
     }
 }
 else
 {
     code_that_touches_db();
 }
[download]

 

Second, for testing the handle I am doing this in my constructor:

 eval {
     local $dbh->{AutoCommit} = 0;
 };
 $self->{_can_do_transactions} = $@ ? 0 : 1;
[download]

which seems to work for the DBs that I have tried, but is it safe, e.g. what if the handle is already mid-transaction? Is there a better way?

UPDATE: Thanks to feedback, am now using something like this to test for transaction capability:

    # If RaiseError is false, begin_work() will:
    #     return true if a new transaction was started
    #     return false if already in a transaction
    #     croak if transactions not supported
    #
    my $started_a_new_transaction = 0;
    eval {
        local $dbh->{RaiseError} = 0;
        $started_a_new_transaction = $dbh->begin_work;
    };
    $self->{_can_do_transactions} = $@ ? 0 : 1;
    eval { $dbh->rollback } if $started_a_new_transaction;
[download]

 

Third, with the AutoCommit test set up as above, I'm occasionally seeing my test harness report errors like:

 closing dbh with active statement handles during global destruction
[download]

if I croak later on in the constructor. For reasons I can't determine, changing the test just by adding a line like so causes those error messages to go away:

 eval {
     my $val = $dbh->{AutoCommit};   # makes error go away?
     local $dbh->{AutoCommit} = 0;
 };
 $self->{_can_do_transactions} = $@ ? 0 : 1;
[download]

Any ideas/comments much appreciated!

 

Larry

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.