As raw material for our Perl programs, we’ll create a simple database of information about O’Reilly & Associates. It’s a very flat database with all its information in a single table, but it’s sufficient to convey all the concepts you need for accessing MySQL with Perl.

First, you need a file of SQL commands that create the table and insert some data. The file is available on the O’Reilly online example site under the name books.sql, and it starts out with the commands in Example 9-2. We can save some room by declaring large VARCHAR fields for most text; only the isbn field has a known, fixed length.

DROP TABLE IF EXISTS Titles;

CREATE TABLE Titles (
  isbn char(10) NOT NULL default '',
  title varchar(255) default NULL,
  publisher varchar(255) default NULL,
  author varchar(255) default NULL,
  pages int(11) default NULL,
  pubdate int(11) default NULL,
  PRIMARY KEY  (isbn)
) TYPE=MyISAM;

INSERT INTO Titles VALUES ('0596000448','Designing Active Server Pages','O'Reilly & 
Associates','Scott Mitchell',376,967795200);
INSERT INTO Titles VALUES ('1565924460','Developing Asp Components','O'Reilly & 
Associates','Shelley Powers',490,930816000);
INSERT INTO Titles VALUES ('156592567X','Writing Apache Modules with Perl and C: The 
Apache API and mod_perl (O'Reilly Nutshell)','O'Reilly & Associates','Lincoln Stein, 
Doug MacEachern, Linda Mui (Editor)',746,920275200);
INSERT INTO Titles VALUES ('1565927060','Apache : Pocket Reference','O'Reilly & 
Associates','Andrew Ford, Gigi Estabrook',107,959846400);
...

Once you have the file, make sure you have been granted CREATE and INSERT privileges by MySQL, as described in Chapter 6. The MySQL root user can create an account for andy, assign him a password, and grant him the privileges he needs all at once in the following command:

mysql> GRANT ALL on Books.* TO andy@localhost identified by 'ALpaswd';

Andy (or any user knowing the password) can now execute the following commands at the shell:

$ mysqladmin -u andy -p create Books
$ mysql -u andy Books -p < books.sql

The -p option prompts for the password ALpswd we assigned when granting privileges. After you successfully execute these commands, the database will be loaded with data and ready for some Perl applications.

Before running the program, you must make sure that Perl is installed on your system (virtually every modern Unix system has it) and that the DBI module is installed. You can check whether a module is installed through a Perl one-liner such as:

$ perl -MDBI -e 1;

If you see no output, the module is loaded and can be used in your programs. If Perl prints the message Can't locate DBI.pm in @INC..., it means the module has to be downloaded and installed. Instructions on downloading and installing Perl modules are beyond the scope of this book, but all the modules described in this chapter (and most other useful, stable Perl modules) are available from the Comprehensive Perl Archive Network (CPAN) at http://www.cpan.org or one of its many mirrors that have sprung up all over the world.

The MySQL username and password for andy are included in our sample program, so it can be run by anybody on the local system once the database is created. The output follows. We have broken some lines and inserted backslashes to fit the page.

$ perl perl1.pl
0596000448      Designing Active Server Pages   O'Reilly & Associates 
  Scott Mitchell  376     967795200
1565924460      Developing Asp Components       O'Reilly & Associates 
  Shelley Powers  490     930816000
156592567X      Writing Apache Modules with Perl and C: 
  The Apache API and mod_perl (O'Reilly Nutshell) O'Reilly & Associates 
  Lincoln Stein, Doug MacEachern, Linda Mui (Editor)     746    920275200
1565927060      Apache : Pocket Reference       O'Reilly & Associates 
  Andrew Ford, Gigi Estabrook     107     959846400
...