Client and Web Applications - Design and Use of Relational Databases in Chemistry - page 43

5.5.1 Perl
Perl is a general-purpose computer language with no built-in capability
to interface with an RDBMS. However, there are readily available mod-
ules that enable Perl to use all of the common RDBMS. Two modules are
needed. One is a general database interface called DBI. This allows one
to write applications without having to deal with details specific to any
particular RDBMS. The other module, DBD is specific to the particular
RDBMS being used. There are several different DBD modules, for example
DBD::Pg for PostgreSQL and DBD::Oracle. A simple way to install these is
using CPAN, the comprehensive perl archive network. On a linux system,
the following commands will install the two modules necessary to use
perl to access a PostgreSQL database. These should be run as the linux
super user by using the sudo command, or logging in as super user.
sudo cpan -i DBI
sudo cpan -i DBD:Pg
Once these modules are installed, the following perl script will select
some rows and columns from a PostgreSQL database.
use DBI;
use DBD::Pg;
#these variable must be set to values appropriate for your site
my $dbname = "book";
my $username = "reader";
my $password = "something";
my $host = "rigel";
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname; host=$host",
$username, $password);
my $sql = "Select smiles,cas from nci.structure where
matches(smiles,'c1ccccc1C(=O)NC')";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute;
while (my @row = $sth->fetchrow_array()) {
print join "\t",@row;
print "\n";
}
$dhb->disconnect;
The variable $dbh is referred to as a database handle. It is created by con-
necting to the database using the DBI->connect function. This state-
ment is typically executed only once, although it is possible to connect to
multiple databases or to connect and disconnect as needed. The variable
$sth is referred to as a statement handle. It is typically used several times,
once per SQL statement. The statement is prepared and then executed.
The results are fetched and processed as required. There are many DBI