Connect to MySQL database using Perl CGI and XAMPP

Question

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.

Turn on RaiseError and see what the DBI says has gone wrong. — pilcrow
– pilcrow, Commented Apr 24, 2012 at 13:45
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
– user828647, Commented Apr 24, 2012 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
– user828647, Commented Apr 24, 2012 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
– user828647, Commented Apr 24, 2012 at 14:03

sid_com · Accepted Answer · 2014-04-09 07:25:05Z

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

edited Apr 9, 2014 at 7:25

sid_com

25.4k28 gold badges104 silver badges194 bronze badges

answered Apr 24, 2012 at 13:42

wholerabbit

11.6k2 gold badges40 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user828647 Over a year ago

@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.

wholerabbit Over a year ago

Looks like there could be a bunch of possible reasons for that. You could start here: stackoverflow.com/questions/119008/… 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.

user828647 Over a year ago

How do I make apache run as a privileged user?

user828647 Over a year ago

I can access my database through phpMyAdmin, if that is of any use

wholerabbit Over a year ago

"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).

|

socket puppet · Accepted Answer · 2012-04-25 01:31:13Z

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.

answered Apr 25, 2012 at 1:31

socket puppet

3,23923 silver badges16 bronze badges

Comments

nastaseion · Accepted Answer · 2023-03-10 14:22:29Z

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?

answered Mar 10, 2023 at 14:22

nastaseion

1731 gold badge2 silver badges10 bronze badges

Collectives™ on Stack Overflow

Connect to MySQL database using Perl CGI and XAMPP

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related