Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
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
  • Outside of code tags, you may need to use entities for some characters:
            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 pondering the Monastery: (12)
    As of 2014-07-14 16:10 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      When choosing user names for websites, I prefer to use:








      Results (268 votes), past polls