How to access MySQL database using Perl

Posted by Vivek Gite [Last updated: December 21, 2006]

Q. How do I access my MySQL database server using Perl programming language?

A. DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql.

So you can connect Perl to your MySQL database server using the DBI Perl module. Here is small program.

Sample Perl code

Make sure you replace database name, MySQL server hostname, username and password according to your setup.

!/usr/bin/perl -w

use DBI;

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

## mysql user database name
$db ="mysql";
## mysql database user name
$user = "vivek";

## mysql database password
$pass = "myPassword";

## user hostname : This should be "localhost" but it can be diffrent too
$host="localhost";

## SQL query
$query = "show tables";

$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);
$sqlQuery  = $dbh->prepare($query)
or die "Can't prepare $query: $dbh->errstr\n";

$rv = $sqlQuery->execute
or die "can't execute the query: $sqlQuery->errstr";

print "<h3>********** My Perl DBI Test ***************</h3>";
print "<p>Here is a list of tables in the MySQL database $db.</p>";
while (@row= $sqlQuery->fetchrow_array()) {
my $tables = $row[0];
print "$tables\n<br>";
}

$rc = $sqlQuery->finish;
exit(0);