Chapter 7. Writing MySQL Programs Using Perl DBI

This chapter describes how to use the Perl DBI interface to MySQL. It does not discuss DBI philosophy or architecture. For information about those aspects of DBI (particularly in comparison with the C and PHP APIs), see Chapter 5, "Introduction to MySQL Programming."

The examples discussed here draw on our sample database, sampdb, using the tables created for the grade-keeping project and for the Historical League in Chapter 1, "Getting Started with MySQL and SQL." To get the most from this chapter, it's best if you know something about Perl. If you don't, you may be able to get along and write your own scripts simply by copying the sample code you see here, but you will probably find a good Perl book a worthwhile investment. One such book is Programming Perl, Third Edition by Wall, Christiansen, and Orwant (O'Reilly, 2000).

DBI is currently at version 1.47, although most of the discussion here applies to earlier versions as well. I assume a minimum version of DBI 1.32, which requires at least Perl 5.6.0 (and 5.6.1 is preferred to 5.6.0). You must also have the DBD::mysql Perl module installed, as well as the MySQL C client library and header files. I assume a minimum version of DBD::mysql 2.0900, the first version that includes transaction support. If you plan to write Web-based DBI scripts in the manner discussed here, you should also obtain the CGI.pm module. In this chapter, CGI.pm is used in conjunction with the Apache Web server. If you need to obtain any of these packages, see Appendix A, "Obtaining and Installing Software." Instructions for obtaining the example scripts developed in this chapter are also given in that appendix. They are part of the sampdb distribution, which you can download to avoid typing in the scripts yourself. The scripts related to this chapter can be found under the perlapi directory of the distribution.

For the most part, this chapter describes Perl DBI methods and variables only as they are needed for the discussion here. For a more comprehensive listing of methods and variables, see Appendix H, "Perl DBI API Reference." You can use that appendix as a reference for further background on any part of DBI that you're trying to use. Online documentation is available by running the following commands:

% perldoc DBI
% perldoc DBI::FAQ
% perldoc DBD::mysql

At the database driver (DBD) level, the driver for MySQL is built on top of the MySQL C client library, and therefore shares some of its characteristics. See Chapter 6, "Writing MySQL Programs Using C," for more information about the client library.