Perl DBI

In this tutorial, We'll see database access inside our Perl script. From Perl 5, database applications use DBI module to access database.

Database is accessed within a Perl script using DBI module. DBI (Database Independent Interface), provides an abstraction layer between Perl code and database.

The DBI is a database access module which provides variables, set of methods for a consistent database interface, independent of the actual database being used.

DBI Architecture

DBI is independent of database and can be used with Oracle, MySQL, etc. DBI takes all SQL commands through API (Application Programming Interface) and forward them to the appropriate driver for the execution.

DBI Connection

We are working with MySQL database. Start your MySQL server. Now complete the following steps:

• Create a database. Our database name is "perll".
• Create a table inside this database. We have created a table "users" with the fields 'name', 'course' and 'fee'.
• Perl module DBI should be installed properly.

After creating database successfully, connect this database with Perl file with following script.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $driver = "mysql";
my $database = "perll";
my $dsn      = "dbi:$driver:database=$database";
my $user     = "root";
my $password = "";
my $dbh = DBI->connect($dsn, $user, $password, {
   PrintError       => 0,
   RaiseError       => 1,
   AutoCommit       => 1,
   FetchHashKeyName => 'NAME_lc',
});
$dbh->disconnect;

Look at the above script,

dsn -> Database Source Name

dbh -> Database Handle Object

dsn contains the database being loaded. User is 'root' and we haven't set any password for our database.

dbh stores the database handle object call returns.

Database disconnect command is optional, it will be automatically called when $dbh is out of scope.

INSERT into Database

Through INSERT operation, we will pass records into our database table "users". The following script allows insertion of single record in the database. You can insert multiple records, using same script.

Follow these steps:

• Use API prepare() function with SQL insert statement.
• Use API execute() function to select all results from the database.
• Use API finish() function to close the code.

my $sth=$dbh->prepare("insert into users(name, course, fee) values('john', 'PHP', '9000')"); 
$sth->execute();
$sth->finish();

Inserting Bind values

Bind values are used when required values will be taken at run time. To insert data, we call $dbh->do method. Here we pass (?) as a place holder at place of actual data. This is followed by undef which is followed by the values containing data which will replace place holders.

my $name = 'nisha';
my $course = 'java',
my $fee = '10000';
$dbh->do('INSERT INTO users (name, course, fee) VALUES (?, ?, ?)',
  undef,
  $name, $course, $fee);

READ Database

READ record is used to fetch information from the database. Once we have inserted some records into our database, we can fetch those records using READ query.

Follow these steps:

• Use API prepare() function with SQL SELECT statement.
• Use API execute() function to select all results from the database.
• Use API fetchrow_array() function to fetch and print all results one by one.
• Use API finish() function to close the code.

# Fetching records from database
my $sth = $dbh->prepare("SELECT * FROM users");
$sth->execute() or die $DBI::errstr;
while (my @row = $sth->fetchrow_array()) {
   my ($name, $course, $fee) = @row;
   print "Name = $name, Course = $course, Fee = $fee\n";
}
$sth->finish();

UPDATE Database

With the help of UPDATE operation, you can edit the information present in the database table.

Here we'll update "course" column as PHP for all the students who have "Java" in their course.

Follow these steps:

• Use API prepare() function with SQL UPDATE statement with required condition.
• Use API execute() function to select all results from the database.
• Use API finish() function to close the code.

my $sth = $dbh->prepare("UPDATE users SET course = 'PHP' WHERE course = 'Java'");
$sth->execute() or die $DBI::errstr;
$sth->finish();

Look at the output, all the Java courses are updated to PHP.

DELETE Database

DE#LETE operation is performed to delete some records from database. We are deleting all records from the table where fee is 9000.

Follow these steps:

• Use API prepare() function with SQL DELETE statement with required condition.
• Use API execute() function to select all results from the database.
• Use API finish() function to close the code.

my $fee = 9000;
my $sth = $dbh->prepare("DELETE FROM users
                        WHERE fee = ?");
$sth->execute( $fee ) or die $DBI::errstr;
$sth->finish();

Look at the output, all the rows which have fee as 9000 are deleted from the SQL table.

DBI Functions

Using do Statement

The do statement is a shortcut to perform CRUD operation in database. If do statement is succeeded it returns TRUE, if failed, it returns FALSE. Following is the example which shows do statement script.

To INSERT Record,
$dbh->do('INSERT INTO users (name, course, fee) VALUES (?, ?, ?)', 
undef,
$name, $course, $fee);
To UPDATE Record,
$dbh->do('UPDATE users SET course = "PHP" WHERE course = "Java"'
  undef,
  $course,
  $course);
To DELETE Record,
$dbh->do('DELETE FROM users WHERE fee =9000');

Using COMMIT Operation

Once you have given the commit command, you can't retrieve back the changes you made. You can call commit command in the following way,

$dbh->commit or die $dbh->errstr;

Using ROLLBACK Operation

If you want to revert the changes made during the operation, you can call rollback API.

$dbh->commit or die $dbh->errstr;

Using AUTOMATIC Error Handling

By specifying RaiseError option, your errors will be handled automatically by DBI. Your program will be aborted on encountering an error rather than running a failure code. The value of RaiseError can be 1 or 0.

my $dbh = DBI->connect($dsn, $user, $password,
{ RaiseError       => 1 })
or die $DBI::errstr;

Common Methods to All Handles

err

$rv = $h->err;

It returns native database engine error code from last driver method called which is typically an integer.

The DBI resets $h->err to undef before all DBI method calls, so this value has a short lifespan.

errstr

$str = $h->errstr;

It returns native database engine error message from last DBI method called. This has the smae lifespan as err method. The returned value may contain multiple messages separated ny new line character.

To test errors, err method should be used. Because errstr method may return success or warning message for methods that have not failed.

trace

$h->trace($trace_settings);

DBI trace method generate runtime tracing information, which can be used to track down the problems in your DBI program. Different values can be set to trace level.

rows

$rv = $h->rows;

The rows method returns number of affected rows by previous SQL statement.