How to connect to MySQL using Perl

Last updated on August 24th, 2022 at 02:58 pm

Click to rate this tutorial!
[Total: 7 Average: 4.3]

This tutorial covers some of the basic configuration and code required to connect to MySQL using Perl. We are going to display a table inside MySQL.

In Linux

Make sure that required modules are installed before running the script.

#!/usr/bin/perl
use DBI;
#use DBD::mysql;
# HTTP HEADER
print "Content-type: text/html \n\n";
# MYSQL CONFIG VARIABLES
$host = "localhost";
$database = "";
$tablename = "";
$user = "";
$pw = '';
# PERL DBI CONNECT()
$driver = "DBI:mysql:database=$database;host=$host";
$connect_me = DBI->connect($driver, $user, $pw);
# SELECT DB
$run_query = $connect_me->prepare("SELECT * FROM $tablename");
$run_query->execute();
#looping and displaying the result
print "<hr />";
$run_query->execute();
while(@result = $run_query->fetchrow_array()){
print "<b>Value returned:</b> $result[1]\n";
}

Update these variables according to your configuration

$host = "";
$database = "";
$tablename = "";
$user = "";
$pw = '';

While executing the script if you are seeing similar error,

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (you may need to install the DBD::mysql module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.30.0 /usr/local/share/perl/5.30.0 /usr/lib/x86_64-linux-gnu/perl5/5.30 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.30 /usr/share/perl/5.30 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base) at (eval 8) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,

It means that DBD module is not installed. If you are using Ubuntu and derivatives you can install DBD::mysql from the repositories with the following command:

sudo apt-get install libdbd-mysql-perl

More details can be found in this link.

Take a look at this tutorial to easily install perl modules.

In Windows

Here I have installed active perl. For that goto http://www.activestate.com/activeperl/downloads and download the perl binaries. Even if i have XAMPP installed with built in perl in it i am not using it for the time being. Now once installation is done, You need to install DBD-mysql module. I am using windows XP , so go to perl installation path will be something like C:\perl\bin, then run ppm install DBD-mysql.It will look like this (Not verified in latest Windows Version)

C:\perl\bin>ppm install DBD-mysql
Downloading ActiveState Package Repository packlist...done
Updating ActiveState Package Repository database...done
Syncing site PPM database with .packlists...done
Downloading DBD-mysql-4.020...done
Unpacking DBD-mysql-4.020...done
Generating HTML for DBD-mysql-4.020...done
Updating files in site area...done
  12 files installed

Once that is done use this code. Its self explanatory for a perl programmer. I am using fetchrow_hashref() for fetching each row information and also fetchrow_array() for fetching particular info in to an array.Any one of those can be used.The code will look like this

#!"C:\perl\bin\perl.exe"
use DBI;
#use DBD::mysql;
# HTTP HEADER
print "Content-type: text/html \n\n";
# MYSQL CONFIG VARIABLES
$host = "localhost";
$database = "demo";
$tablename = "users_details";
$user = "root";
$pw = "******";
# PERL DBI CONNECT()
$driver = "DBI:mysql:database=$database;host=$host";
$connect_me = DBI->connect($driver, $user, $pw);
# SELECT DB
$run_query = $connect_me->prepare("SELECT * FROM $tablename");
$run_query->execute();
#looping and displaying the result
while($result=$run_query->fetchrow_hashref()) {
  print "<b>Value returned:</b> $result->{time}\n";
}
print "<hr />";
$run_query->execute();
while(@result = $run_query->fetchrow_array()){
print "<b>Value returned:</b> $result[1]\n";
}
Click to rate this tutorial!
[Total: 7 Average: 4.3]

3 replies on “How to connect to MySQL using Perl”

I was having trouble with xampp’s built in perl and the installation of db modules. This helped me to make my script work. Thanks!

Leave a Reply

Your email address will not be published.