DBI in Perl

To connect to a database and run SQL statements, the typical steps are as below:

1. create connection with database;

2. get prepare with connection;

3. execute the SQL statement;

4. fetch results and store them into local variables;

5. finish the execution;

6. close the connection if everything is done.

 

Below are the sample snippet,

Supposed now you have database:

(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=shop.abc.com)(PORT=1234))(CONNECT_DATA=(SID=SHOP)))

TNS naming: shopsid in your server.

User/pwd: admin/admin123

And table: productlist (vchar(40) productname,  double price);

1. create connection with database;

use DBI;

$shopdbi = DBI->connect(‘dbi:Oracle:shopsid’,’admin’,’admin123′,)  || print “shop Database connection not made: $DBI::errstr”;

# You can also use

# DBI->connect(‘dbi:Oracle:’,q{admin/admin123@(DESCRIPTION= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=shop.abc.com)(PORT=1234))(CONNECT_DATA=(SID=SHOP)))}, “”)  || print “shop Database connection not made: $DBI::errstr”

2. get prepare with connection;

my $productlistsql = qq(
SELECT distinct( B.productname), price
FROM productlist p
where p.price > 30
);
my $productliststmt = $shopdbi->prepare( $productlistsql );

3. execute the SQL statement;

$productliststmt->execute( );

4. fetch results and store them into local variables;

my ( $allnames );
my ( $allprices);

my ( $allinfo);

$productliststmt->bind_columns( undef , \$allnames , \$allprices);
my $i=0;
while( $productliststmt->fetch() )
{
$allinfo[$i][0] = $allnames;
$allinfo[$i][1] = $allprices;
$i++;
}

5. finish the execution;

$productliststmt->finish;

6. close the connection if everything is done.

$shopdbi->disconnect();