Managing DBI Transactions

Grygonos has asked for the wisdom of the Perl Monks concerning the following question:

Fellow monks,

I come seeking your guidance in managing some transactions I'm doing w/ DBI. I have the following subroutine, which will be handling the transaction

sub insertRecord
{
    my ($record,$dbh,$tapeID) = @_;
    
    my %max = getMaxID($dbh);
    
    my $sth;
    
    $sth = $$dbh->prepare(addressQuery());
    
    $sth->execute($max{'addresses'},
                  $$record{'subscriber_address_one'},
                  $$record{'subscriber_address_two'},
                  $$record{'subscriber_city'},
                  $$record{'subscriber_state'},
                  $$record{'subscriber_zip'},
                  $$record{'subscriber_zip4'},
                  $$tapeID) or do{$$dbh->rollback(); return 0};
      ##Execute more queries in the same fashion
      $$dbh->commit();
      return 1;                   
}
[download]

The variable that the return code is received into then is checked and the main program will write the record to an error log if we return 0 at any point. Is the the $sth->execute(....) or do {$$dbh->rollback; return 0;};portion of the code an ok way to handle this? I have a set of tables which obviously have keys into each other, so they must be inserted in a certain order to achieve integrity, so there are about 7 queries being run .. but all in the same fashion as the one I listed

Any other monks have experience with managing transactions of this nature w/ DBI?

<PS> the getMaxID($dbh); sub just returns the ~~max~~ max+1 of an id column for each table that i'm inserting into. Since I have to know the value of my foreign key for inserting into other tables I can't allow SQL Server to have these be identity columns(also for identity insert reasons), but they are primary keys</PS>

Grygonos

Comment on Managing DBI Transactions Select or Download Code

Replies are listed 'Best First'.
Re: Managing DBI Transactions by dragonchild (Archbishop) on Mar 30, 2004 at 15:17 UTC
Personally, I would set RaiseError on the $dbh and put the execute in an eval block. Something like: `my $dbh = DBI->connect( $connect_string, $user, $password, { RaiseError => 1, PrintError => 0, AutoCommit => 0, } ); sub insertRecord { my ($record, $dbh, $tapeID) = @_; my %max = getMaxID($dbh); eval { my $sth = $dbh->prepare_cached(addressQuery()); $sth->execute( $max{'addresses'}, ( map { $record->{"subscriber_$_"} qw( address_one address_two city state zip zip4 )), $tapeID, ); ##Execute more queries in the same fashion $dbh->commit(); } if ($@) { $dbh->rollback; return 0; } return 1; }` [download] I also added a few changes to your syntax, which improves the readability. (In my opinion, of course. They are functional-neutral to what you had, which worked just fine.) Well, mostly worked fine. Your syntax implied that you were passing a reference to the $dbh into your subroutine. There is no reason to do that, as it's already a reference. If you're not doing that, you have a potential error. Update: Added a sample $dbh definition with the appropriate settings. ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose	[reply] [d/l]
Re: Managing DBI Transactions by bart (Canon) on Mar 30, 2004 at 15:24 UTC
the getMaxID($dbh); sub just returns the max of an id column for each table that i'm inserting into. In that case, you're trying to use the same primary key more than once. You should increment the values. Oh, yeah, and I basically see nothing wrong with your approach, except I'm wondering why you seem to be passing a reference to $dbh. A copy of an "object" (handle) is still the same object. Well, that, and you should try to get a lock, use semaphores, something, to avoid a collision of doing two inserts at the same times.	[reply]
Re: Re: Managing DBI Transactions by Grygonos (Chaplain) on Mar 30, 2004 at 15:33 UTC
didn't realize the `$dbh` was a reference. The primary keys won't be reused. I meant to say max + 1 of the id column. Every time insert record is called it will grab a new set of maxes for the primary keys..I'll fix that in my original. Grygonos	[reply] [d/l]
Re: Managing DBI Transactions by mpeppler (Vicar) on Mar 30, 2004 at 15:32 UTC
I'll second dragonchild's comment - use RaiseError and eval. This is a typical situation where that type of error handling makes the code much more readable, and will avoid processing errors. Michael	[reply]