This example illustrates basic use of Perl’s DBI module. The database contains records of people who wish to participate in an email-based ‘pen-pal’ service. Subscribers provide their email address, some limited personal information and a set of interests chosen from a predefined set of about 100 possible topics. Searches can be made for other subscribers who share some common interests, and satisfy other constraints. The database has just one table; the rows characterize subscribers to this ‘E-Pal’ service. Each subscriber has an email address (assumed for simplicity to fit into a 32 character field); this must be unique, and serves as the primary key. Subscribers normally identify themselves as male or female, but can choose not to specify their gender (in which case they are classified as ‘e-persons’). Subscribers can specify restrictions on the type of person with whom they wish to correspond (specifying male, female, e-person or any). For simplicity, a subscriber’s interests are represented using five separate integer fields. The table has the following definition:

CREATE TABLE EPAL
   (email varchar(32) NOT NULL,
   type varchar(8) NOT NULL,
   want varchar(8) NOT NULL,
   interest1 number(4),
   interest2 number(4),
   interest3 number(4),
   interest4 number(4),
   interest5 number(4),
   CONSTRAINT id_pkey PRIMARY KEY(email),
   CONSTRAINT type_check CHECK (type in ('MALE', 'FEMALE', 'EPERSON')),
   CONSTRAINT want_check CHECK
   (want in ('MALE', 'FEMALE', 'EPERSON', 'ANY'))
   );

The ‘check’ constraints are not particularly important and can be omitted if the database that you use does not support such constraints; the program code essentially duplicates these checks. The example program is a standalone Perl program reading data from the keyboard and working directly with the database. Commands can be given to the program to add records to the database or to perform searches. The code could be adapted to form a CGI script that accepted data entered in HTML forms; this would result in a web-based E-Pal service – something rather more useful than this standalone version. Very similar input data are required for record addition and searches. Users must enter their own ‘type’, the ‘type’ of person with whom they wish to correspond, and must select five interests from the list of predefined interests; if a record is to be added, the input data must also include an email address. The criteria for a successful match in the search are:

The value for ‘type’ in the record satisfies the ‘want’ requirement of a request – with the override that a request ‘want=any’ matches any value in the type field. The ‘type’ specified in the search request satisfies the ‘want’ requirement given in the record – again with ‘any’ being matched by all possible request types. The two sets of interests have a non-zero intersection (interests are represented as numbers in the range 1–100; they are not necessarily ordered). These matching criteria are difficult to express as an SQL selection condition; it is easier for the program to retrieve all records and apply the criteria programmatically. The program uses a number of global variables, and each subroutine has local variables. The somewhat larger size of the program increases the risk of use of errors resulting from implicit declaration of variables, or the use of uninitialized variables. It is worthwhile changing the mode of the Perl interpreter so that all variables must be declared. Specifying the directive ‘use strict’ at the start of the program makes this change.

The main line invokes the initialization routine that creates a database connection and sets up table. The main function then has a ‘menu-select’ loop handling commands entered by the user. The commands handled are ‘add’, ‘search’, ‘list’ (lists contents of interest list) and ‘quit’. The code is:

initialize;
   while(1) {
   my $cmd;
   print "Enter command (add,search, list (interests), quit): ";
   $cmd = <STDIN>;
   if($cmd =~ /quit/i) { last; }
   elsif($cmd =~ /search/i) { doSearch; }
   elsif($cmd =~ /add/i) { doAdd; }
   elsif($cmd =~ /list/i) {
   print "Interest list @interestlist\n"; }
   else { print "Command not recognized\n"; }
   }

The initialization routine creates a hash that maps interest topic to identifier number. Users must enter their interests and have them checked; here, the hash serves as a faster lookup system than the original list. The other tasks for the initialization routine are opening a database connection and preparation of both a simple SQL selection query and a more elaborate parameterized SQL statement that will be used to enter records. The insert statement requires eight values to be bound – email address, ‘type’, ‘want’ and five interest identifiers.

sub initialize {
   my ($id, $interest);
   $id = 0;
   foreach $interest (@interestlist) {
   $interesttable{$interest} = $id; $id++;
   }
   $dbh = DBI->connect($data_source,
 "HSimpson", "Doh", { AutoCommit => 1})
 || die "Couldn't connect to db\n";
 $searchHandle = $dbh->prepare("SELECT * FROM epal");
 $insertHandle = $dbh->prepare(
 "INSERT INTO epal VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ");
 }

The doAdd function uses three simple helper functions (owntype, wanttype, getinterests) to obtain user input. The getinterests function repeatedly prompts the user until data defining five interests have been entered correctly; it returns a list with the corresponding identifier numbers. Note the call to the execute function; this specifies three explicit arguments and the five-element interest array. This does match the requirements for eight arguments – after all, Perl combines all arguments into a single list anyway, and then splits out the arguments inside the function.

sub doAdd {
   my ($you, $desire, $email, @interests);
   $you = owntype;
   $desire = wanttype;
   @interests = getinterests;
   print "Your email address : ";
   $email = <STDIN>;
   chomp($email);
   $insertHandle->execute( $email, $you, $desire, @interests)
   || die "Failed to insert record because $DBI::errstr";
   }

The search function similarly uses the helper functions (owntype, wanttype and getintersts) to obtain data characterising the searcher. It then runs the SQL search query: ‘select everything from the epal table’. Each record is read into a list ($searchHandle-> fetchrow_array). The retrieved data are then checked against the request data (some of Perl’s unusual ‘back-to-front’ conditional tests are illustrated in this code).