I'm trying to connect to two separate databases in a single perl script and it's not working. Is it possible? I connect to one to check a username/pass combo. If they're in there, proceed with the script. I'm using a sub file to handle connecting to the database. Here are the routines in there. 
####################################
## Create database connection
sub Conn_to_DB_Forums{
  use DBI;
  $DSN = "DBI:mysql:me_forums:db.site.com";
  $sqluser = "me1";
  $sqlpass = "12345";
  $dbh = DBI->connect($DSN,$sqluser,$sqlpass)
    || die "Cannot connect: $DBI::errstr\n" unless $dbh;
  return;
} 
## end of connect to DB subroutine
####################################


####################################
## Create database connection
sub Conn_to_DB{
  use DBI;
  $DSN = "DBI:mysql:me_content:db1.site.com";
  $sqluser = "me2";
  $sqlpass = "67890";
  $dbh = DBI->connect($DSN,$sqluser,$sqlpass)
    || die "Cannot connect: $DBI::errstr\n" unless $dbh;
  return;
} 
## end of connect to DB subroutine
####################################

####################################
## Exec SQL command
sub Do_SQL{
  eval {
    $sth = $dbh->prepare($SQL);
  }; # end of eval

  # check for errors
  if($@){
    $dbh->disconnect;
    print "Content-type: text/html\n\n";
    print "An ERROR occurred! $@\n";
    exit;
  } else {
    $sth->execute;
  } # end of if/else
  return ($sth);
}
## End of Do_SQL subroutine
#####################################


####################################
## Exec SQL2 command
sub Do_SQL2{
  eval {
    $sth2 = $dbh->prepare($SQL2);
  }; # end of eval

  # check for errors
  if($@){
    $dbh->disconnect;
    print "Content-type: text/html\n\n";
    print "An ERROR occurred! $@\n";
    exit;
  } else {
    $sth2->execute;
  } # end of if/else
  return ($sth2);
}
## End of Do_SQL2 subroutine
#####################################

[download]


SO... This script allows people to post opinions. In the script I do this: 


if ($submit || $preview) {

    &Conn_to_DB_Forums;

    $SQL = "SELECT * from $forum_table WHERE (username='$username' AND
+ user_password!='$user_password' AND user_active='1')";
    &Do_SQL;
    &error(not_registered) unless ($pointer = $sth->fetchrow_hashref);

I do some other stuff here...then disconnect.


$sth->finish();
$dbh->disconnect;

}


if ($submit) {


&Conn_to_DB;
    $SQL = "SELECT * from $op_tbl WHERE (prodID='$prodID' AND dateadde
+d='$dateadded' AND prodtype='$prodtype' AND (username='$username' OR 
+title='$titlefix'))";
    # run SQL against the DB
    &Do_SQL;
    &error(no_spam) if ($pointer = $sth->fetchrow_hashref);

    $SQL = "INSERT INTO $op_tbl (username,name,email,location,features
+,usability,title,body,dateadded,prodID,prodtype)
        VALUES ('$username','$namefix','$emailfix','$locationfix','$fe
+atures','$usability','$titlefix','$bodyfix','$dateadded','$prodID','$
+prodtype')";
    # run SQL against the DB
    &Do_SQL;
    $pointer = $sth->fetchrow_hashref;
    
    $filename = $pointer->{'filename'};
}

[download]

The $filename field doesn't get set which means I'm not connecting successfully to the 2nd databsae. I think the deal is that I'm not disconnecting from the first database because if I leave that call out (&Conn_to_DB_Forums;), everything else works.

I can also leave these two disconnect lines out, and it makes no difference: $sth->finish();
$dbh->disconnect;
I hope my question makes sense. Is there a way to get out of the first database and then read from a 2nd? Thanks!

In reply to Connecting to 2 mysql database in one script by htmanning

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.