Perl Database Access

You are free to write database applications by using DBI (Database Independent Interface) module. DBI in perl, provides an abstraction layer between the perl code and the underlying database, that makes allowing you to switch the database implementations easily.

Database Connection in Perl

Here we have assumed that we are going to work with the MySQL database. Therefore, before connect to a database make sure these things listed here:

• Firstly, you have created a database named MYDATABASE
• Now, you have created a table named MYTABLE present inside the database named MYDATABASE
• Now the table named MYTABLE have fields namely, FIRSTNAME, LASTNAME, AGE, SEX, and FEE
• Now, user id, "testuser123" and password, "testpassword123", must set to access the database named MYDATABASE
• Now in last, perl module DBI is installed properly on your platform for successful operation

Following is the example of connecting with the MySQL database named "MYDATABASE":

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql"; 
my $database = "MYDATABASE";
my $dsn = "DBI:$driver:database=$database";
my $userid = "testuser123";
my $password = "testpassword123";

my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

Create/Insert Records into Table in Perl

Here is a sample example, inserting the record into the table (MYTABLE) present in the database (MYDATABASE):

my $sth = $dbh->prepare("INSERT INTO MYTABLE
                       (FIRSTNAME, LASTNAME, SEX, AGE, FEE)
                        values ('Anoop', 'Singh', 'M', 30, 1300)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Read Data from the Database in Perl

Here is a sample example, reading the record from the table (MYTABLE) present in the database (MYDATABASE):

my $sth = $dbh->prepare("SELECT FIRSTNAME, LASTNAME
                        FROM MYTABLE 
                        WHERE AGE > 20");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;
while (my @row = $sth->fetchrow_array())
{
   my ($first_name, $last_name ) = @row;
   print("First Name = $first_name, Last Name = $last_name\n");
}
$sth->finish();

Perl Online Test