Perl DBI - Connecting Perl to MySQL database

Knowing how to connect Perl with a database is imperative and very useful. Luckily there is a Perl module called DBI which makes our things easier. In this article I will demonstrate how to write a Perl program to connect to a MySQL database and fetch some rows. Consider the following code:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# Main program
main();

# Subroutines
sub main {
  
  my $dbn="dbi:mysql:testdb:localhost:3306";
  my $dbh=DBI->connect($dbn, "user", "password") or die "Unable to connect: $DBI::errstr\n";
  my $select_query="SELECT * FROM t1 ORDER BY id ASC";
  my $select_h = $dbh->prepare($select_query) or die "Unable to prepare: $DBI::errstr\n";
  $select_h->execute() or die "Unable to execute: $DBI::errstr\n";
  while ( my @data = $select_h->fetchrow_array()) {
    print "Column 1: $data[0], Column 2: $data[1]\n";
  }
  $select_h->finish();  
  $dbh->disconnect;
}

1. Before starting make sure you type the use DBI statement.
2. Define the database handle as follows: dbi:mysql:db_name:db_host:db_port:db_user:db_password.
3. Connect to instance using DBI->connect().
4. Prepare statement using $db_handle->prepare().
5. Execute statement using execute().
6. Fetch rows by looping the fetchrow_array() function.
7. Finish the statement and Disconnect from handle.