1

I am using Perl CGI to access a MySQL database in XAMPP with the following code:

#!/xampp/perl/bin/perl -w

use DBI;

print "Content-type:text/html\r\n\r\n";

$database = "mydb";
$host = "localhost";
$port = "3306";
$user = "root";
$pw = "";

$dsn = "dbi:mysql:$database:$host:$port";

print "Trying to connect <br />";
$connect = DBI->connect($dsn, $user, $pw, {RaiseError=>1});

if (defined $connect) {
    print "Preparing query <br />";
}

$query = "SELECT * FROM reference WHERE ID = 1742031";
$query_handle = $connect->prepare($query);

$query_handle->execute();

$query_handle->bind_columns(undef, \$pmid, \$popu, \$loc);

while($query_handle->fetch()) {
   print "$pmid, $popu, $loc <br />";
}

However I am unable to print out the result to my webpage. I think I am not being able to connect to the database because the "Preparing Query" line is not getting printed. But I can connect to the database and execute this query from the mysql command line. I have no idea what the problem could be.

EDIT: After installing the missing DBD:mysql module, I get a new error: "Can't connect to MySQL server on localhost (10061)". I tried following the suggestions here: MYSQL - Cant connect to MYSQL server on 'localhost' (10061) and everything seems to be fine. I have my mysqld.exe running and I am running mysql as a service. Also, I can connect to the database through the mysql command lineas well as phpMyAdmin, so there should be no privileges issue as I only have a root account.

Please advise. I will be grateful for any help.

Community
  • 1
  • 1
user828647
  • 505
  • 1
  • 10
  • 27
  • 3
    Turn on RaiseError and see what the DBI says has gone wrong. – pilcrow Apr 24 '12 at 13:45
  • Did you literally leave $pw empty, or just remove it here? – CodeClown42 Apr 24 '12 at 13:49
  • It says "Can't locate DBD/mysql.pm in @INC." How do I install this module? I did not even use it in my program! – user828647 Apr 24 '12 at 13:59
  • @pilcrow When I look in the list of available drivers in ppm, there is no DBD-mysql. There is DBD-CSV, ODBC, Oracle and SQLite. Where do I get this mysql module from? – user828647 Apr 24 '12 at 14:02
  • @goldilocks I have not set a password for the database yet since I just testing it out myself. Does this make a difference? – user828647 Apr 24 '12 at 14:03
  • 'How do I install this module? I did not even use it in my program!' -- Yes you did: $dsn = "dbi:mysql:$database:$host:$port"; – JoelFan Apr 24 '12 at 14:15
  • @user828647: perhaps not – CodeClown42 Apr 24 '12 at 14:16
  • From the [Stack Overflow Perl FAQ](http://stackoverflow.com/questions/tagged/perl?sort=faq): [What's the easiest way to install a missing Perl module?](http://stackoverflow.com/questions/65865/whats-the-easiest-way-to-install-a-missing-perl-module) – daxim Apr 24 '12 at 14:19
  • Yaay! I finally solved it myself. It seems that the port 3306 was being blocked by a firewall. Following a suggestion [here](http://www.apachefriends.org/f/viewtopic.php?p=191979&sid=161267e7608e7ea27aa7245b25625986), I simply uncommented the line containing the bind-address in the my.ini file in \xampp\mysql\bin...and that was it!! – user828647 Apr 25 '12 at 10:33

3 Answers3

1

How do I install this module?

Use "cpan" on the command line:

> cpan 
cpan shell -- CPAN exploration and modules installation (v1.960001)
Enter 'h' for help.

cpan[1]> install DBD::mysql

If that isn't installed, you can download it:

https://metacpan.org/pod/DBD::mysql

sid_com
  • 24,137
  • 26
  • 96
  • 187
CodeClown42
  • 11,194
  • 1
  • 32
  • 67
  • @pilcrow, JoelFan Thanks for the help! I installed the DBD:mysql module. However it is now showing a new error: "Can't connect to MySQL server on localhost (10061)". What could be causing this? As I said, I can connect to the database through the mysql command line. Please help me out here. – user828647 Apr 24 '12 at 14:44
  • Looks like there could be a bunch of possible reasons for that. You could start here: http://stackoverflow.com/questions/119008/cant-connect-to-mysql-server-on-localhost-10061 Keep in mind that apache does not run as a privileged user, which may or may not be an issue; eg, if it cannot even connect to authenticate. – CodeClown42 Apr 24 '12 at 14:53
  • How do I make apache run as a privileged user? – user828647 Apr 24 '12 at 15:12
  • I can access my database through phpMyAdmin, if that is of any use – user828647 Apr 24 '12 at 15:29
  • "How do I make apache run as a privileged user?" -- you don't. There's a reason for that, and if you want to learn XAMPP stuff you need to stay inside the lines. The apache process needs to have access to the mysql server as whatever user it runs as (it actually switches from root to something else after start-up, exactly who should be in the configuration). – CodeClown42 Apr 25 '12 at 10:44
  • thanks for the info...Anyways I solved the problem at hand. Please see my last comment to my question. – user828647 Apr 25 '12 at 10:56
0

To connect to a MySQL database with DBI, use

$dsn = "dbi:mysql:$database;host=$host;port=$port";

localhost and port 3306 are the default values, so you could leave those parts out.

socket puppet
  • 3,191
  • 20
  • 16
0

I got the a similar issue. Actually, XAMPP is now using MariaDb instead of MySQL.

So you need to use:

'DBI:MariaDB:host=...'

in your connection string to make it work. Also check my answer from here: Why won't this Perl DBI/DBD MySQL (MariaDB) connect to locahost work?

nastaseion
  • 173
  • 1
  • 2
  • 9