Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Connecting to remote db with DBI

by dana (Monk)
on Mar 23, 2007 at 19:49 UTC ( #606334=perlquestion: print w/replies, xml ) Need Help??
dana has asked for the wisdom of the Perl Monks concerning the following question:


I'm trying to connect to a MySQL database that is located on a server, but I think I am having problems with the connect statement. The program appears to hang (see output; I have to kill the process), so I turned on tracing. My code:

use strict; use warnings; use DBI; my $db = 'homo_sapiens_core_42_'; my $host = ''; my $user = 'xx'; my $pass = 'yy'; DBI->trace(1); print "HERE\n"; my $dbh = DBI->connect("DBI:mysql:$db;host=$host", $user, $pass, { RaiseError => 1 } ) or die ( "Couldn't connect to database: " . DBI->errstr ); print "DONE\n";
The output:
DBI 1.54-ithread default trace level set to 0x0/1 (pid 25943) at q line 23 HERE -> DBI->connect(DBI:mysql:homo_sapiens_core_42_;host= +, xx, ****, HASH(0x9fc8c28)) -> DBI->install_driver(mysql) for linux perl=5.008005 pid=25943 ru +id=56 euid=56 install_driver: DBD::mysql version 4.004 loaded from /usr/lib/p +erl5/site_perl/5.8.5/i386-linux-thread-m ulti/DBD/ <- install_driver= DBI::dr=HASH(0xa063d18) !! warn: 0 CLEARED by call to connect method

I looked through the DBI documentation and several Tutorials (DBI recipes, The fine art of database programming, Reading from a database, and Tricks with DBI), but I couldn't find anything that seemed to address this issue. I also tried several google searches.

I can connect to the database via the command line, so I don't think it is a problem with the server or the database itself.

$ /usr/local/mysql-standard-5.0.27-linux-i686-glibc23/bin/mysql -u xx +-h HOST -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3100 to server version: 5.0.27-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> mysql> use homo_sapiens_core_42_ Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed [deleted test select query] SELECT query successful.

Your wisdom and insights are appreciated. Thanks.

Replies are listed 'Best First'.
Re: Connecting to remote db with DBI
by ferreira (Chaplain) on Mar 23, 2007 at 20:02 UTC

    It looks like you're making a mistake when building the DSN: constrast yours ("DBI:mysql:$db;host=$host") and the one provided in the documentation of DBD::mysql:

    use DBI; $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";

    You're missing the "database=" before $db.

      Thank you.

      I needed the 'database=' in the statement. I also had to use the hostname rather than the IP address, however this may be a network issue.

      My final connection code looks like the following:
      my $dsn = "DBI:mysql:database=$db;host=$host";
      my $dbh = DBI->connect( $dsn, $user, $pass, { RaiseError => 1 }) or die ( "Couldn't connect to database: " . DBI->errstr );

        Worked the first try !!
Re: Connecting to remote db with DBI
by dragonchild (Archbishop) on Mar 23, 2007 at 20:00 UTC
    my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", $user, $pass, { RaiseError => 1 } ) or die ( "Couldn't connect to database: " . DBI->errstr );

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
      Hi Perl forks Your problem is inside the DBI. Your DBD is corrupted or incomplete. Install perl DBD for mysql. I assure you this works.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://606334]
Approved by jettero
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2017-12-16 11:57 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (450 votes). Check out past polls.