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);
