Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Are 2 database connections neccesary w/DBI?

( #11298=categorized question: print w/ replies, xml ) Need Help??
Contributed by mculey on May 12, 2000 at 19:49 UTC
Q&A  > database programming


Description:

Okay, I'm setting up a transaction with my basic eval block. I'm running through several update and insert statements which, along the way, i must compare the rows affected to other select query information. What I've run into is that my select statements always return 0 rows when using the same database connection yet with a seperate connection, it returns properly. Having recently switched from Oraperl to DBI, I wasn't sure if this was as intended or I'm just doing something wrong...sample code follows: # Begin the SQL Stuff
eval { ############################### # Begin stu_drop.updt section # ############################### print "Beginning stu_drop.updt section...\n"; # Open stu_drop.log file open(LOG, ">stu_drop.log") or die "Couldn't open log file: $@\n"; $sql = "UPDATE saturn.sfrstcr SET sfrstcr_rsts_code = 'DB', sfrstcr_error_flag = 'D', sfrstcr_rsts_date = SYSDATE, sfrstcr_activity_date = SYSDATE WHERE sfrstcr_term_code = '$term_code' AND sfrstcr_rsts_code LIKE 'R%' AND sfrstcr_pidm IN (SELECT DISTINCT pidm FROM studrop +)"; $sth = $dbh->prepare($sql); $sth->execute(); $rows_affected = $sth->rows; $sth->finish(); print "Rows affected by stu_drop.updt: $rows_affected\n"; print LOG "$sql\n\nRows affected: $rows_affected\n\n"; $sql = "SELECT count(*) THE_COUNT FROM sfrstcr, spriden WHERE sfrstcr_term_code='$term_code' AND sfrstcr_pidm in ( SELECT pidm FROM studrop ) AND sfrstcr_rsts_code like 'R%' AND sfrstcr_pidm=spriden_pidm AND spriden_change_ind IS NULL AND spriden_entity_ind ='P'"; $sth2 = $dbh2->prepare($sql); $sth2->execute(); print LOG $sql . "\n"; while(@row = $sth2->fetchrow_array) { ($row_count) = @row; print LOG "Rows pulled from stu_drop_list: $row_count\n"; } $sth2->finish(); if ($row_count == $rows_affected) { print "No prob with counts\n"; } else { print "stu_drop_list = $row_count but other = $rows_affect +ed!\n"; $dbh->rollback; $dbh->disconnect(); $dbh2->disconnect(); exit 1; } close(LOG);

Answer: Two connections are unnecessary
contributed by mosiondz

You should not have to start up a second DBI connection. All that should be required is as follows:

# set DB and SQL values as necessary use DBI; $dbh = DBI->connect "$DB_SERVER:$DB_NAME;$DB_HOST") or die "Can't conn +ect to $DB_NAME, error "; $sth = $ccmd_auth::dbh->prepare($sql) or die "Can't prepare $sql: $dbh +->errstr\n"; $sth->execute; $sth->finish;

After the $sth is 'finished', you should be able to prepare and use $sth again in other queries.

One thing I noticed in your code is that you refer to a $dbh2 when you set up your second $sth. Is this for a connection to a second database? Otherwise it should use the same $dbh that was originally set up. This may be what is causing your problem.

Please (register and) log in if you wish to add an answer



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • 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, 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, 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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others studying the Monastery: (6)
    As of 2015-07-29 21:35 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









      Results (269 votes), past polls