Perl - How to Connect to DB and Perform Insert, Update, Delete, Commit, Rollback and other some other DBI functions

DBI stands for Database Independent Interface for Perl which means DBI provides an abstraction layer between the Perl code and the underlying database, allowing you to switch database implementations really easily.

The DBI is a database access module for the Perl programming language. It provides a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

How to Conect to DB with Perl
------------------------------

#!/usr/bin/perl

use DBI
use strict;

my $driver = "mysql";
my $database = "TestDataBase";
my $dsn = "DBI:$driver:database=$database";
my $userid = "scott";
my $password = "tiger";

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

If the Database connection is successful then connect will return DBI handler for futher use.
Else returns the error errstr

How to insert record in to DB with Perl
------------------------------------------

my $sth = $dbh->prepare("INSERT INTO emp
                       (FIRST_NAME, LAST_NAME, SAL )
                        values
                       ('scott', 'james', 100)"); //Prepare the sql statement
$sth->execute() or die $DBI::errstr; //Execute the statement if successful continue else return errstr
$sth->finish(); //Release the connection
$dbh->commit or die $DBI::errstr; //Commit the changes done

or

$sth = $dbh->prepare(qq{
       INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)
       });
 $sth->execute("Joe", undef);

How to insert record and pass values during runtime
----------------------------------------------------

my $first_name = "john";
my $last_name = "poul";
my $SAL = 200
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
                       (FIRST_NAME, LAST_NAME, SAL )
                        values
                       (?,?,?)");
$sth->execute($first_name,$last_name,$sal)
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

How to select rows from DB using Perl
---------------------------------------

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME
                        FROM emp
                        WHERE AGE > 30");
$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();

How to update DB records using Perl
------------------------------------

my $sth = $dbh->prepare("UPDATE TEST_TABLE
                        SET   AGE = AGE + 1
                        WHERE SAL > 200");
$sth->execute() or die $DBI::errstr;

print "Number of rows updated :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr

How to delete DB records using Perl
-------------------------------------

$age = 30;
my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                        WHERE AGE = ?");
$sth->execute( $age ) or die $DBI::errstr;

print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr

Get metadata from DB using Perl
--------------------------------
This will fetch available drivers

@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);

get all data sources
@ary = DBI->data_sources($driver);