Client 1 - Connecting to the Server
Client 1 ”Connecting to the ServerBefore 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 get_drivers.pl 1 #!/usr/bin/perl -W 2 # 3 # Filename: get_drivers.pl 4 # 5 use strict; 6 use DBI; 7 8 # Get the list of drivers from the DBI 9 # 10 my @driver_names = DBI->available_drivers(); 11 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.
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 get_drivers.pl $ ./get_drivers.pl 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 get_datasources.pl 1 #!/usr/bin/perl -W 2 # 3 # Filename: get_datasources.pl 4 # 5 use strict; 6 use DBI; 7 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: $ ./get_datasources.pl dbi:Pg:dbname=movies dbi:Pg:dbname=perf dbi:Pg:dbname=template0 dbi:Pg:dbname=template1 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 get_datasources.pl 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.
DBI URLsA 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: dbi:Pg: option = value [; option = value ]... Where option=value can be any of the values shown in Table 14.1. Table 14.1. PostgreSQL DBI URL Options
To connect to the movies database, you could use any of the following URLs: dbi:Pg:dbname=movies dbi:Pg:dbname=movies;host=arturo;port=8234 dbi:Pg: 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 client1.pl1 #!/usr/bin/perl -W 2 # 3 # Filename: client1.pl 4 # 5 6 use strict; 7 use DBI; 8 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 client1.pl , 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 client1.pl # Make sure the script is executable $ ./client1.pl DBI->connect() failed: FATAL 1: Database "korry" does not exist in the system catalog. at ./client1.pl 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 ./client1.pl 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. |
EAN: 2147483647
Pages: 220