Chazin Home | Ca-binding Protein DB | Vanderbilt Home
	Research Description | Publications | Wisdom | Search
	How to contribute | About this page

Accessing a PostgreSQL Database with the Perl Modules DBI and DBD:Pg

Written by: Melanie Nelson, fall 1998

See also: Basic Procedures in PostgreSQL

This document was written for perl 5.004, DBI 1.02, DBD:Pg 0.73, and postgresql 6.3.2, running under IRIX 6.2. However, most of the procedures described in this document are very basic, and are unlikely to be operating system dependent.

This document does not attempt to be a complete reference about Perl, DBI, or PostgreSQL! For more information about PostgreSQL, see the postgresql website (http://www.postgresql.org/index.html). For more information about DBI, see Alligator Descartes' DBI pages. The DBI article from The Perl Journal is particularly helpful. The DBI specification is also available online. I also recommend Jeffery Baker's Perl DBI examples.

Procedures are in rough alphabetical order, by keyword. You can skip to a specific procedure using the links below:

To connect to a database:

Here is one way to connect to a database named mydb:
$dbh = DBI->connect ( "dbi:Pg:dbname=mydb", "", "");
if ( !defined $dbh ) {
die "Cannot connect to database!\n";
}
This syntax works if you do not need to specify a database user. If you do need to specify a user, put the username and user_password in the two empty fields in the above statement.

The syntax may be a bit more complicated if you have more than one database driver (DBD) module installed.

You must tell Perl to use the DBI module before these statements appear. Do this by putting the following line at the top of your script:
use DBI;

When you are finished accessing the database, you shoudl disconnect as follows:
$dbh->disconnect;

To execute a basic SQL statement:

There are three steps to executing a basic SQL statement:
1. Prepare the statement:
This example is a statement that will retrieve the rows in a table called reference.
$sth = $dbh->prepare( "
SELECT reference.ref_num, type, reference.title, pub_year, summary
FROM reference
");
if ( !defined $sth ) {
die "Cannot prepare statement: $DBI::errstr\n";
}

2. Execute the statement:
This is done using the following line:
$sth->execute;

3. Fetch the rows into an array or variables:
This is an example that fetches the attributes from the rows into variables:
while ( ($ref_num, $type, $title, $pub_year, $summary ) = $sth->fetchrow()){
...intervening code...
}

After you are finished with the SQL statement, you should release the statement handle as follows:
$sth->finish;

To execute nested SQL statements:

By nested SQL statements, I mean statements where the while loop reading the rows from the first SQL statement includes commands that execute a second SQL statement. This seems to be possible, as long as each statement is given a distinct statement handle:
while ( @row1 = $sth1->fetchrow()){
...intervening code...
while (@row2 = $sth2->fetchrow()){
...second while loop code...
}
}

Remember to use the same statement handle (sth1 or sth2 in the above example) for the prepare, execute, fetch, and finish statements. If you fail to do this, you are likely to get an error similar to the following:
DBD::Pg::st fetchrow failed: no statement executing
with no indication of which fetchrow statement is causing the trouble.