Chapter 2: Working with a Database

Overview

Working with a database in Perl requires very little extra work in your programs. Since the DBI module is well written and stable, writing a database-enabled program takes minimal extra thought on the programmer's part. Sure, you need to plan what data you want to access and how to query it-but connecting to the database and executing SQL queries on it is easy.

Cross Reference 

Appendix A offers instructions on how to install the DBI module and DBD driver modules. Please make sure you have these installed before continuing, since we'll be using them.

To make sure that the DBI module is installed and functioning and to see what database drivers are available on your system, let's write a short script to query the system:

01: #!/usr/bin/perl -w 02: # connect_test.pl 03: # Chapter 2 04: # Listing 1 05: use strict; 06: use DBI; 07: print "Available Database Drivers:\n"; 08: print "*" x 40, "\n"; 09: print join("\n", DBI->available_drivers()), "\n\n";

Line 1 tells the system where to find Perl and turns on warnings with the -w switch. Warnings are helpful when writing programs because they cause Perl to print additional diagnostic messages that can be helpful debugging aids. Turning warnings off in production code is perfectly acceptable once you have the program running the way you want it to. Leaving warnings on in production code can produce unneeded diagnostic messages in your server log files.

Lines 2-4 are simply comments about this program.

Line 5 turns strict mode on. In a program this small, strict is not needed, but I make it a habit to program with strict mode on because it can be a lifesaver when debugging. strict forces the programmer to declare variables and turns additional error checking on.

Tip 

Always use strict when you begin programming. Trying to put strict into an existing program is usually a major task; without strict mode on, Perl lets the programmer get away with a lot of sloppy coding practices. When strict is added, all of the sloppy code must be fixed at once.

To eliminate this problem, begin programming all of your applications by using strict mode from the very start.

Line 6 loads the DBI module; this is needed for database access.

Line 7 prints a text message.

Line 8 prints a line of 40 asterisks. The x 40 tells Perl to print the quoted text 40 times. Then we have a comma and a newline to put the output onto the next line.

Line 9 uses Perl's join function. join is used to combine an array of data with a common string. The string we are joining with here is the newline \n, and the array that we are joining is actually DBI->available_drivers(), since this function returns an array. We follow this up with two newlines to add two blank lines to the program output.

If you enter the preceding program and the DBI is installed properly, you should get output that looks something like this:

Available Database Drivers: **************************************** ExampleP Pg Proxy Mysql

The drivers you have loaded depend on the database drivers you have loaded (DBDs). The main database drivers I am interested in here are Mysql and Pg-which are the MySQL and PostgreSQL drivers, respectively.

If all is well so far, let's move on to the next step: connecting to a database.