Perl Database Access

« Previous Tutorial Next Tutorial »

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

« Previous Tutorial Next Tutorial »