Web Development

Retrieve data from a PostgreSQL database using the Perl DBI

This document demonstrates how the Perl DBI can be used to communicate with the open-source PostgreSQL database system.

When it comes to working with a database—any database—experienced Perl hands reach for just one tool: the Perl DBI. This extremely powerful module works as a database abstraction layer, offering an integrated set of functions to interact with a variety of different data sources.

This document will demonstrate how the Perl DBI can be used to communicate with PostgreSQL, a free, open-source database system commonly used for both online and offline data storage requirements. It introduces you to the important methods supported by the DBI, and provides a simple script template for you to use in your development.

Note: The remainder of this tutorial assumes that you already have PostgreSQL and Perl installed.

Tutorial


To begin, download and install the Perl DBI module and the PostgreSQL DBD driver by running the following commands at your Perl prompt:

perl> perl -MCPAN -e "install DBI"
perl> perl -MCPAN -e "install DBD::Pg"

Note: You can also manually download and install the DBI and the PostgreSQL DBD manually.

The Perl DBI and PostgreSQL DBD should now be installed to your system.

Then, create an example table for your SQL queries, by entering the following commands at the PostgreSQL command-line client prompt:

test=# CREATE TABLE pets (species VARCHAR(255), name VARCHAR(255));
CREATE TABLE

test=# INSERT INTO pets VALUES ('dog', 'Sparky');
#INSERT 17351 1

#test=# INSERT INTO pets VALUES ('cat', 'Tabitha');
#INSERT 17352 1

Once the table has been created, proceed to build a script template (Listing A) using DBI methods:

Listing A


#!/usr/bin/perl

# load module
use DBI;

# connect
my $dbh = DBI->connect("DBI:Pg:dbname=test;host=localhost", "joe", "secret", {'RaiseError' => 1});

# execute INSERT query
my $rows = $dbh->do("INSERT INTO pets (species, name) VALUES ('parrot', 'Polly')");
print "$rows row(s) affected\n";

# execute SELECT query
my $sth = $dbh->prepare("SELECT species, name FROM pets");
$sth->execute();

# iterate through resultset
# print values
while(my $ref = $sth->fetchrow_hashref()) {
    print "$ref->{'name'} is a $ref->{'species'}\n";
}

# clean up
$dbh->disconnect();

Four steps


There are 4 simple steps to follow when executing an SQL query with the Perl DBI.

  1. Begin by initializing a database handle with a call to the connect() method. This method accepts connection parameters as a string containing the database driver ("Pg"), the host name ("localhost") and the database name ("test"). The database user name ("joe") and password ("secret") is also provided to the connect() method as the second and third argument.
  2. Create the SQL query string, and execute it with either the do() method or the prepare() and execute() methods. Use the do() method for one-off INSERT, UPDATE or DELETE queries, but use the prepare() and execute() methods for SELECT queries. The result object of these methods will be different depending on the query type and whether or not it was successful. Successful SELECT queries return a result object; successful INSERT/UPDATE/DELETE queries with do() return the number of rows affected; and unsuccessful queries return an error.
  3. For SELECT queries, the result object may be processed further to extract data from it. Used in a loop, the fetchrow_hashref() method retrieves each record as a Perl hash. You can access individual fields of the record by calling the appropriate key of the hash.
  4. End the session with a call to the disconnect() method.

This script template will come in handy when you next sit down to create a PostgreSQL-backed database application in Perl. Happy coding!

0 comments