Perl database connection

For database connectivity with perl we need module DBI

DBI stands for database interface.This module creates a bridge between Perl and database.Perl is capable to perform verities of database operations , including INSERT,UPDATE,DELETE, etc through the Perl module DBI. Any functions available with DBI work with all database SQL queries , including SQL Server, DB2,MySQL and Oracle.

For connection with MySql:

$dbh = DBI->connect(“DBI:MySQL:$db_name:$host:$port”,$db_user,$db_password, {
RaiseError => 1, AutoCommit => 0, PrintError => 0})
|| die “Unable to perform Database connect to $db_name: $DBI::errstrn”;

Writing some query:

$sqlq = "select * from anytable";

$sth = $dbh->prepare($sqlq);

$sth->execute
        or die "Could not execute SQL statement\n$sqlq\n for some reason";

while (@row=$sth->fetchrow_array) {
       print $row[0];
       print "\n";
       print $row[1];
}

For connection with Oracle:

First you need to have oracle database connection set right. In linux you can set in /etc/.odbc.ini file.


@driver_names = DBI->available_drivers;
@data_sources = DBI->data_sources('ODBC');
print "Driver names: @driver_names",$/,$/;
print $_,$/ for @data_sources;

#This helps you finding out whether you DSN is set or not.

my $dbh = DBI->connect("DBI:ODBC:OracleODBC-11g", "username", "password");

#DBI:ODBC:OracleODBC-11g is ORACLE driver in this case


Writing some query:

my $sth = $dbh->prepare('SELECT * FROM tableName WHERE attribute=somevalue')
                or die "Couldn't prepare statement: " . $dbh->errstr;

while (@data = $sth->fetchrow_array()) {

            print "\t$data[0]\n$data[1]\n";
          }

Popularity: 100% [?]