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

In reply to Managing DBI Transactions by Grygonos

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.