Perl Tutorial: How to connect to MySQL with Perl

By /
Perl Tutorial How To Connect To MySQL With Perl

Perl is a cross-platform general-purpose programming language. It was developed in 1987 by Larry Wall. Perl was first developed for text manipulation and today it is used for managing cloud data, speech recognition, system administration, log management, etc.

In this tutorial, we will learn about how to connect to a MySQL table using the Perl programming language.

When connecting to any third-party application we need middleware or an integration tool, likewise, when we are connecting to MySQL using Perl then it will be needing a middleware. One of the features of Perl is the DBI, which is a database integration interface, used for connecting with third-party databases such as Oracle, MySQL, Postgres, etc.

Pre-requisites for this tutorial

For a follow along with this tutorial, there are some pre-requisites enlisted below:

1. MySQL: A basic knowledge of SQL is a must. Download MySQL from below if not already installed on your system.
https://dev.mysql.com/downloads/

2. Perl: Basic understanding of Perl is advisable for this tutorial such as variables, conditional statements, etc. Download Perl from the below link if not already installed.
https://www.perl.org/get.html

Setup for MySQL connectivity

Assuming that we have all the required installations on our desktop for the tutorial. We now need to install a few dependencies for database connectivity for Perl.

We’ll be installing DBD(database driver) for Perl using CPAN. CPAN stands for Comprehensive Perl Archive Network which is a Perl repository for different software modules as well as documentation for applications assisting Perl. Earlier in this tutorial, we discussed that it is important to have a middleware to establish communication between two different applications. Therefore, we’ll be needing a Perl database driver for MySQL, luckily, we have DBD for that.

Open the command prompt, and let’s connect to CPAN using the below command:

perl -MCPAN -e shellCode language: Bash (bash)
Perl mysql connection

After entering CPAN, let’s install the database driver. As we are working on MySQL, we’ll be installing MySQL DBD only. Let’s see the command below:

install DBD:mysqlCode language: Bash (bash)
Install Perl Dbd For MySQL

Connecting to MySQL using Perl

Now that we are through with the setup, let’s dive into the part where we write the code for connectivity to MySQL. To connect to MySQL we’ll be needing a database and a table present in it.

This is the SQL query to create a database:

CREATE DATABASE databaseName;Code language: SQL (Structured Query Language) (sql)

Below is the Query for creating a table:

CREATE TABLE tableName;Code language: SQL (Structured Query Language) (sql)

Now, that we have our table, we will write a Perl code. You can use an IDE for writing this code if working on a project, otherwise, you can just use notepad and save the file with the .pl extension. Before writing the code let’s see all the steps involving Perl-MySQL database connectivity.

There are four simple steps to connect to the database using Perl:

  1. Establish Connection
  2. Prepare SQL statement
  3. Execute Statement
  4. Fetch Resultset

Now let’s dive into the coding part and understand how it works:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

#Create Connection
my $dbh = DBI->connect("DBI:mysql:database=db;host=localhost","sam","root");

die "failed to connect to MySQL database:DBI->errstr()" unless($dbh);

# prepare SQL statement
my $sth = $dbh->prepare("SELECT empid, fname, lname FROM employee")
                   or die "prepare statement failed: $dbh->errstr()";

$sth->execute() or die "execution failed: $dbh->errstr()"; 

my($empid,$fname,$lname);

#Fetch Resultset
# loop through each row of the result set, and print it
while(($empid,$fname,$lname) = $sth->fetchrow()){
   print("$empid, $fname\t$lname\n");                   
}

$sth->finish();
$dbh->disconnect();Code language: Perl (perl)

Let’s understand the code line by line:

  • Up until line 4: We are using all the necessary modules for database connectivity.
  • Line 7: Establishing connection with MySQL using Database name as DB, hostname as localhost, username as sam, and password as root.
  • Line 9: If the connection does not get established then we will receive the message with an error note.
  • Line 12: SELECT statement is used in this for fetching the data from the database, if the prepared statement fails we will see the error message.
  • Line 15: Statement execution, if not executed successfully we will see the error output.
  • Line 21: Returning the resultset and displaying the data.

Let’s get the execute the program, to do so let’s open the command prompt. After opening the command prompt enter into the folder where this Perl program is saved and write the below command to execute the program:

perl codename.plCode language: Bash (bash)
Perl-mysql connectivity.

Conclusion

In this tutorial we learned briefly about Perl language, we also learned what is DBI and how to install it, and lastly, we learned the Perl MySQL database connectivity with simple and easy steps.