Perl MySQL Connect

Summary: in this tutorial, you will learn step by step how to connect to a MySQL database using Perl DBI API.

Let’s get started by creating a simple database in MySQL named perlmysqldb for the demonstration.

The following CREATE DATABASE statement creates a new database in the MySQL Server:

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

Connecting to MySQL database

When you connect to a MySQL database, you need the following information:

First, you need to tell DBI where to find the MySQL database server. This is called the data source name or DSN. The data source name specifies the driver to use and the database to connect. Perl requires the data source name to begin with dbi: and the name of the driver. For MySQL, the driver name is mysql followed by a colon : e.g., dbi:mysql: , and then the database name e.g., dbi:mysql:perlmysqldb .
Second, you need to provide the username and password of the MySQL user account that connects to the database.
Third, the optional connection attributes specify the way DBI handles exceptions that may occur when connecting to the MySQL database.

The syntax for creating a connection to the MySQL database is as follows:

$dbh = DBI->connect($dsn,$username,$password,\%attr);Code language: Perl (perl)

The connect() method returns a database handle if the connection to the database is established successfully.

For example to connect to the perlmysqldb database, you use the following script:

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function

use DBI;
say "Perl MySQL Connect Demo";
# MySQL database configuration
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';

# connect to MySQL database
my %attr = ( PrintError=>0,  # turn off error reporting via warn()
             RaiseError=>1);   # turn on error reporting via die()           

my $dbh  = DBI->connect($dsn,$username,$password, \%attr);

say "Connected to the MySQL database.";Code language: Perl (perl)

How it works.

First, use the use DBI; statement at the top of the script.
Next, define some variables that hold the data source name, username, and password.
Then, define a hash variable that contains the connection’s attributes.
After that, pass the corresponding arguments to the connect() method to create a connection to the perlmysqdb database.
Finally, show a message to indicate that the program has been connected to the MySQL database successfully.

The following is the output of the script:

Perl MySQL Connect Demo
Connected to the MySQL database.Code language: Shell Session (shell)

Handling errors

Perl DBI allows you to handle errors manually and/or automatically. Perl DBI detects errors when they occur and calls either warn() or die() function with an appropriate error message.

The PrintError attribute instructs DBI to call the warn() function that shows the errors to the output. The RaiseError attribute tells DBI to call the die() function upon error and to abort the script immediately.

Perl DBI enables the PrintError by default. However, we strongly recommend that you turn the PrintError attribute off and RaiseError attribute on to instruct DBI to handle the error automatically.

If you don’t turn the RaiseError on, you have to handle the error manually as follows:

# withou RasieError off:
 my $dbh  = DBI->connect($dsn,$username,$password) or 
            die("Error connecting to the database: $DBI::errstr\n");Code language: Perl (perl)

When an error occurs, DBI stores the error message in the $DBI::errstr variable. The above statement means if the connection to the database fails, it displays the error message and aborts the script immediately.

Another benefit of turning on the RaiseError attribute is that the code will look more readable because you don’t have to include the or die() statement everywhere you call a DBI method.

Disconnecting from MySQL Database

If you are no longer interacting with the MySQL database, you should explicitly disconnect from it. It’s a good practice.

To close a database connection, you use the disconect() method of the database handle object as follows:

# disconnect from the MySQL database
$dbh->disconnect();Code language: Perl (perl)

In this tutorial, you have learned how to connect to and disconnect from a MySQL database by using the Perl DBI API.

Was this tutorial helpful?