Testing Perl and MySQL

Before I go into detail on how to use Perl to connect to and interact with MySQL, I'll go through a quick exercise first. This script will demonstrate how I will be writing Perl scripts throughout the chapter. Further, it will test that MySQL support is available before you start writing the remaining Perl scripts.

While this chapter will in no way replace a solid Perl book or tutorial, I'll quickly go over the steps of a basic Perl script. Even those programmers coming from other languages (such as PHP) ought to be able to follow these guidelines to write basic Perl documents. Also, most of the steps will be explained in some detail here, and knowledge of them will be assumed (and therefore not explained) in later sections.

To write a simple Perl script:

Create a new document in your text editor (Script 8.1).

Since Perl scripts are just text files, it will not matter what text editor you use as long as it can save a file as plain text (not RTF).

Script 8.1. This simple Perl script tests for the presence of the MySQL driver.

1 #!/usr/bin/perl -w 2 3 # Script 8.1 - test.pl 4 # This script just reveals 5 # what drivers are installed. 6 7 # Use what needs to be used. 8 use strict; 9 use DBI; 10 11 # Print a header message. 12 print "The following drivers are installed:\n"; 13 14 # Create the @drivers array. 15 my @drivers = DBI->available_drivers(); 16 17 # Print each driver. 18 foreach (@drivers) { 19 print "$_ \n"; 20 }

Include the shebang line (unless you are using ActivePerl on Windows).

#!/usr/bin/perl -w

This line tells the computer to use the Perl application to process this text file. You'll need to change this line to match the location of the perl file on your system. Another common variant on this line would be #!/usr/local/bin/perl -w. On Windows with ActivePerl, you can omit this line entirely, although it does not hurt to use it.

The -w flag enables many useful warnings when executing the code. This is recommended for debugging purposes.

Enforce strict programming.

use strict;

The use strict command ensures a safer form of programming without adding too much overhead to your scripts. I'll be using it throughout this chapter, with the main result being that I need to declare variables before using them (see Step 5).

For non-Perl programmers, I'll also mention that every line in Perl, aside from the shebang and control structures, must end in a semicolon. Single-line comments can be preceded by a number (or pound) sign.

Include the DBI module and print an introductory message.

use DBI;
print "The following drivers are installed:\n";

This first line will be required in all of the scripts throughout this chapter. It tells the script to make use of the DBI module that is necessary for interacting with MySQL. The second line tells the user what is to follow.

Declare and initialize an array.

my
@drivers = DBI-> available_drivers();

The @drivers array will contain all of the drivers that are available to the DBI in this particular installation of Perl. Those values are automatically determined by the available_drivers() method of the DBI class. The my statement is required when doing strict programming, and you'll see an error if it is omitted.

Loop through and print each driver.

foreach (@drivers) {
    print "$_ \n";
}

The construct here will loop through the @drivers array, accessing each element one at a time. The elements, now referred to by the special $_ variable, will be printed, followed by a new line (\n).

Save the file as test.pl.

I'll be using the .pl extension throughout this chapter (which ActivePerl on Windows will recognize automatically).

You must make sure that your text editor does not add another hidden extension (like Notepad, which may try to add .txt to the end of the file's name).

Change the permissions of the file using the command line, if required (Figure 8.11).

Figure 8.11. To make a file executable on Unix operating systems (which means it can be run by entering `./filename.pl`), you need to change its permissions.

chmod u+x test.pl

On Unix and Mac OS X operating systems, you need to tell the OS that this file should be executable. To do so, you use the chmod command to add executable status (x) to the file for the file's user (u). Again, this step is not necessary for Windows users.

You will need to be within the directory where test.pl was saved in order to run this command.

Run the file.

On Windows systems, either

Enter perl C:\path\to\test.pl at the command prompt and press Enter.

or
Enter just perl C:\path\to\test.pl at the command prompt and press Enter.

Windows users can also cd to the directory where test.pl is and type test.pl or perl test.pl (Figure 8.12).

Figure 8.12. Running test.pl (here, on Windows) confirms that you can run a Perl script and that the MySQL support has been established.

[View full size image]

Unix and Mac OS X users can do one of two things:
Move into the directory where you saved the script, type ./test.pl at the command prompt, and press Return (Figure 8.13).

Figure 8.13. Running the script prints out a list of every DBI driver that your Perl installation can now use.

or
Enter perl C:\path\to\test.pl at the command prompt and press Return.

If you use the ./test.pl method, include the shebang line and change the file's permissions. Otherwise, skip those two steps.

In all of these examples, replace path/to/ with the actual path to your script.

However you run the file, you should see the word mysql included in the list of drivers printed out (Figures 8.12 and 8.13). This confirms that you can connect to MySQL from a Perl script.

Tips

To check a script for problems without executing it, use perl -c /path/to/filename.pl.
When specifically using Perl for Web development, you have the option of using the mod_perl Apache module, in which case you would use Apache::DBI rather than just DBI. You'll also need the CGI module.
If you are interacting with MySQL 4.1 or greater, you need at least version 2.9003 of DBD::mysql.