Establish Database Connection with Perl

It's been a while since I use Perl to connect to a database and my memory about it has faded long ago. But now since I'm no longer purely involve in web development so I thought this method could be useful. Let see how it goes.

To cut the chase, here is the sample code

use strict;

use DBI;

# Connect to the database
# See footnote 1
my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_password')
or die "Couldn't open database: $DBI::errstr; stopped";

# Prepare the SQL query for execution
my $sth = $dbh->prepare(<execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

# Fetch each row and print it
while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) {
print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n";
}

# Disconnect from the database
$dbh->disconnect();

DBI is Perl's DataBase Interface. There are many different databases: Oracle, mSQL, MySQL, Ingres... even files containing data separated by columns could be considered databases. DBI works with practically all of them. DBI requires a database driver module to talk to the database. The driver module handles all of the details about connecting to and communicating with the database. DBI sits on top of the driver; its job is to make reading from (and writing to) all databases as similar as possible. That way, if your project grows from something that could be handled with mSQL to something needing Oracle, you can make that change without altering the code very much.

Database driver modules are Perl modules like any other, and often they contain documentation. They are all inside the root package DBD. So, to get information on an Oracle driver you've installed, you can type:

perldoc DBD::Oracle

Other than installing the module, and possibly reading the documentation, you rarely need to deal directly with the database driver module. DBI handles all of that.