Client 1 - Connecting to the Server

Before you try to connect to a PostgreSQL server, take a moment to examine the basic components of a typical Perl/DBI script.

Listing 14.1 shows a Perl script that will print the list of available DBD drivers.

Listing 14.1

 1 #!/usr/bin/perl -W

 2 #

 3 #  Filename:

 4 #

 5 use strict;

 6 use DBI;


 8 # Get the list of drivers from the DBI

 9 #

10 my @driver_names = DBI->available_drivers();


12 # Print the name of each driver

13 #

14 foreach my $driver ( @driver_names ) {

15     print( "Driver: $driver\n" );

16 }

The first line of the script identifies this file as an executable. When you run a program on Unix/Linux systems, or if you are using Cygwin in the Windows environment, a script file is (directly) executable when the first line of the file contains the characters #! followed by the name of the script interpreter (of course, you must hold execute privileges for the script, too). For example, a bash shell script would start with the line #!/bin/bash. For Perl scripts, the interpreter is named perl and is usually found in the /usr/bin directory. So, the first line of each of our Perl scripts will be #!/usr/bin/perl -W[2]. The -W flag is passed to the perl interpreter and tells perl to display all warnings?this is useful when you are trying to debug new scripts. The next feature common to all our Perl applications is seen at line 5. If you don't include use strict, Perl will be happy to let you misspell variable names and it will just assume that a misspelled name is a variable that it has never seen before. The use strict directive tells the Perl interpreter to catch this kind of mistake by requiring that you declare all variables before they are used.

[2] You can also run a Perl script without including the magic first line?just type perl followed by a space and then the name of the script file. So you can invoke this program as ./ or as perl

The use DBI directive (at line 6) tells Perl that you want to use features defined in the DBI module. You must include a use DBI directive in every application that uses the DBI module.

In this application, you call the DBI->available_drivers() method to retrieve the names of all drivers currently installed on our host. available_drivers() returns an array of driver names. The loop at lines 14 through 16 iterates through the array and prints each driver name.

To run this script, you first have to be sure that its "x" (executable) permission is turned on:

$ chown a+x

$ ./

Driver: ExampleP

Driver: Pg

Driver: Proxy

You can see that there are three DBD drivers installed on my system: ExampleP, Pg, and Proxy.

The DBI class also can give you a list of the data sources accessible through a driver. Let's pick one of these drivers (Pg is the PostgreSQL driver) and print the list of data sources. Listing 14.2 shows the required code:

Listing 14.2

 1 #!/usr/bin/perl -W

 2 #

 3 #  Filename:

 4 #

 5 use strict;

 6 use DBI;


 8 foreach my $data_source ( DBI->data_sources( "Pg" )) {

 9     print $data_source . "\n";

10 }

This script calls the DBI->data_sources() method to obtain a list of the data sources accessible through the Pg driver. Each driver is free to define a data source however it sees fit; the PostgreSQL driver considers a data source to be equivalent to a database. The PostgreSQL driver connects to the template1 database to obtain a list of valid database names. When you run this program, you will see a list of all databases in your database cluster:

$ ./





If you don't see a list of database names when you run this program, you may have to define the DBI_USER and DBI_PASS environment variables. DBI_USER should hold your PostgreSQL user name, and DBI_PASS should hold your PostgreSQL password. In the next two sections, you'll see a better way to supply a username and password to PostgreSQL.

The list returned by shows the same set of databases that would be returned using the psql -l command:

$ psql -l

       List of databases

   Name    | Owner | Encoding


 movies    | bruce | SQL_ASCII

 perf      | bruce | SQL_ASCII

 template0 | bruce | SQL_ASCII

 template1 | bruce | SQL_ASCII

Notice that these two lists are not identical. The list produced by psql includes the owner and encoding[3] of each database. The list produced from DBI->data_sources() is actually a list of data source names, or DSNs. A DSN is similar in concept to the connection strings that you have seen in earlier chapters.

[3] You won't see the Encoding column on your system if you have not enabled multibyte support.


A DBI data source name is encoded in the form of a URL (Uniform Resource Locator). A DBI URL is composed of three parts: a protocol (always dbi), a driver name, and a driver-specific string of connection options. For example, the URL for the movies database is dbi:Pg:dbname=movies. The PostgreSQL driver can work with connection URLs of the following form:


Where option=value can be any of the values shown in Table 14.1.

Table 14.1. PostgreSQL DBI URL Options


Environment Variable Used as Default











To connect to the movies database, you could use any of the following URLs:




The final URL doesn't include any connection options. DBD::Pg uses the environment variables shown in Table 14.1 to default any values missing from the connection URL.

At this point, you know how to obtain the list of installed drivers, how to get the list of data sources accessible through a given driver, and how to construct a connection URL. Now, let's try to connect to a database (see Listing 14.3).

Listing 14.3

1 #!/usr/bin/perl -W

2 #

3 #  Filename:

4 #


6 use strict;

7 use DBI;


9 my $dbh = DBI->connect( "dbi:Pg:" );

The DBI->connect() method tries to connect to the URL that you provide (dbi:Pg:). If successful, connect() will return a database handle. If connect() fails, things get complicated. The connect() method can perform a number of different actions, depending on the attributes that you specify. In, you didn't supply any attributes?I'll get to attributes in a moment.

Let's run this script to see how it reacts to error conditions:

$ chmod a+x  # Make sure the script is executable

$ ./

DBI->connect() failed: FATAL 1:  Database "korry" does not exist

    in the system catalog. at ./ line 9

This error is telling you that client1 tried to connect to a database named korry and you don't have a database named korry. Why did you try to connect to that database? Take a look at line 9 in Listing 14.3. When you asked DBI to create a connection, you didn't provide a database name. According to Table 14.1, the DBD::Pg driver looks to the PGDATABASE environment variable if you don't specify a database name in the connection URL. If you don't supply a database name in the connection URL and you haven't defined PGDATABASE, how does DBD::Pg decide which database to connect to? To find this answer, you have to look to libpq (the PostgreSQL C API); DBD::Pg is implemented using the libpq library. It's actually libpq that looks for the environment variables shown in Table 14.1. If you don't supply an explicit database in the connection URL and you didn't define PGDATABASE, libpq will try to connect to a database whose name matches your username; I'm logged-in as user korry so libpq (and therefore DBD::Pg) tries to connect to a database named korry.

Now let's run this script again, supplying a value for PGDATABASE:

$ PGDATABASE=movies ./

Database handle destroyed without explicit disconnect.

That's a little better (take my word for it). This message means that you did make a successful connection, but you didn't clean up after yourself as the script ended. Fixing that problem is easy?you need to call the $dbh->disconnect() function before you exit. You'll do that in the next client.

    Part II: Programming with PostgreSQL