Managing & Using MySQL, 2nd Edition

Setting Up the Database and Program

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.

Example 9-2. Beginning of SQL commands to load database

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 ...