InformIT: Mac OS X Unleashed > Perl/MySQL Integration

Table of Contents

Perl/MySQL Integration

Perl and MySQL can be combined to create database applications that can be used for anything from storing your personal movie collection to enterprise-wide solutions. This portion of the chapter will introduce you to the MySQL and Perl connection. You can later apply this knowledge to create Web applications in Chapter 28.

This section is intended to provide an example of Perl module installation, as well as the integration of Perl and MySQL functionality. Even if you don't intend to use these two applications together, the installation instructions apply to just about any Perl module.

CPAN

Perl can be extended to offer additional functionality ranging from Internet access to graphics generation. Just about anything you could ever want to do can be done using Perl—you just need the right module. The best place to find the right Perl module is CPAN—the Comprehensive Perl Archive Network. CPAN contains an ever-increasing list of Perl modules with their descriptions and documentation. To browse CPAN, point your Web browser to www.cpan.org.

There are two ways to install modules that are located in the CPAN archive. The first is using a built-in Perl module that interacts directly with CPAN from your desktop computer. The second is the traditional method of downloading, unarchiving, and installing—just like with any other software. Perl modules are a bit easier to install than most software because the installed software ends up in the Perl directory, rather than needing to be placed in a variety of directories across the entire system hierarchy.

There are two Perl modules (DBI::DBD and DBD::mysql) that are needed to interact with MySQL. Conveniently, this corresponds to the two available installation methods. So, let's take a look at how these modules can be installed using the interactive connection to CPAN and by downloading, and working with, the module archive directly.

CPAN Installation (`DBI::DBD`)

Using the interactive method of installing Perl modules is as simple as install <module name> . To start the interactive module installation shell, type sudo perl -MCPAN -e shell at a command line. The CPAN installer shell will start:

cpan shell — CPAN exploration and modules installation (v1.52)
ReadLine support available (try ``install Bundle::CPAN'')

cpan>

The DBI::DBD module provides a database interface to Perl's arsenal of capabilities. At the cpan> prompt, type install DBI::DBD to begin the installation process:

cpan> install DBI::DBD

Issuing "/usr/bin/ftp -n"
Local directory now /private/var/root/.cpan/sources/modules
GOT /var/root/.cpan/sources/modules/03modlist.data.gz
Going to read /var/root/.cpan/sources/modules/03modlist.data.gz
Running make for T/TI/TIMB/DBI-1.18.tar.gz
Issuing "/usr/bin/ftp -n"
Local directory now /private/var/root/.cpan/sources/authors/id/T/TI/TIMB
GOT /var/root/.cpan/sources/authors/id/T/TI/TIMB/DBI-1.18.tar.gz

  CPAN: MD5 security checks disabled because MD5 not installed.
  Please consider installing the MD5 module.

DBI-1.18
DBI-1.18/DBI.xs
DBI-1.18/lib
DBI-1.18/lib/DBD
DBI-1.18/lib/DBD/NullP.pm
DBI-1.18/lib/DBD/Sponge.pm
DBI-1.18/lib/DBD/ADO.pm
DBI-1.18/lib/DBD/ExampleP.pm
DBI-1.18/lib/DBD/Multiplex.pm
DBI-1.18/lib/DBD/Proxy.pm
DBI-1.18/lib/DBI
DBI-1.18/lib/DBI/FAQ.pm
...
Installing /usr/bin/dbiproxy
Installing /usr/bin/dbish
Writing /Library/Perl/darwin/auto/DBI/.packlist
Appending installation info to /System/Library/Perl/darwin/perllocal.pod
  /usr/bin/make install  — OK

Depending on your Perl installation and version, you might notice several additional messages during the installation. Each time the CPAN shell is used, it checks for new versions of itself. If a new version is found, it will provide instructions on how to install the update. Don't concern yourself too much about these messages unless the installation fails.

That's it. The DBD::DBI module, which provides the basis for database access from within Perl, is now installed.

For more control within the CPAN shell, you can use these additional commands:

get <module name> — Download the named module.
make <module name> — Download and compile the module, but do not install.
test <module name> — Download, compile, and run the named module's tests.
install <module name> — Download, compile, test, and install the module.

Modules that have been downloaded are stored in the .cpan directory within your home directory. Keep track of the size of this directory because it will continue to grow as long as you install new modules.

Archive-Based Installation (`DBD::mysql`)

To complete the integration of Perl with MySQL, we need the DBD::mysql module. This package provides the MySQL driver that works with the DBI::DBD software. Instead of using the CPAN shell, let's take a look at installation of the module distribution archive directly. This is almost identical to installing other types of software, so there shouldn't be many surprises here.

First, download the package to install from CPAN:

[primal:~] jray% curl -o ftp://ftp.cpan.org/pub/CPAN/modules/ by-module/DBD/DBD-mysql-2

      
   .0901.tar.gz

Next, unarchive the module:

[primal:~] jray% tar zxf DBD-mysql-2.0901.tar.gz

Enter the distribution directory and enter this command: perl Makefile.PL —cflags="-I'/usr/local/mysql/include'". In this particular example, you need to add the —cflags option to tell Perl where the MySQL header directory is located. Typically, module installations need nothing more than perl Makefile.PL at this stage.

[primal:~/DBD-mysql-2.0901] jray% perl Makefile. PL —cflags="-I'/usr/local/mysql/include'"
This is an experimental version of DBD::mysql. For production
environments you should prefer the Msql-Mysql-modules.

I will use the following settings for compiling and testing:

  testpassword  (default     ) =
  testhost      (default     ) =
  testuser      (default     ) =
  nocatchstderr (default     ) = 0
  libs          (mysql_config) = -L/usr/local/lib/mysql -lmysqlclient -lz -lm
  testdb        (default     ) = test
  cflags        (Users choice) = -I'/usr/local/mysql/include'

To change these settings, see 'perl Makefile.PL —help' and
'perldoc INSTALL'.

Using DBI 1.18 installed in /Library/Perl/darwin/auto/DBI
Writing Makefile for DBD::mysql

Now, the installation becomes identical to any other software. The same make commands apply. The best step to take next is to type make to compile and then make test to test the compiled software:

[primal:~/DBD-mysql-2.0901] jray% make
cc -c -I/Library/Perl/darwin/auto/DBI -I'/usr/local/mysql/include' -g -pipe -pipe

      
    -fno-common -DHAS_TELLDIR_PROTOTYPE -fno-strict-aliasing -O3     -DVERSION=\"2.0901\"

      
    -DXS_VERSION= \"2.0901\"  -I/System/Library/Perl/darwin/CORE  dbdimp.c
...
t/00base............ok
t/10dsnlist.........ok
t/20createdrop......ok
t/30insertfetch.....ok
t/40bindparam.......ok
t/40blobs...........ok
t/40listfields......ok
t/40nulls...........ok
t/40numrows.........ok
t/50chopblanks......ok
t/50commit..........ok, 14/30 skipped: No transactions
t/60leaks...........skipped test on this platform
t/ak-dbd............ok
t/akmisc............ok
t/dbdadmin..........ok
t/insertid..........ok
t/mysql2............ok
t/mysql.............ok
All tests successful, 1 test and 14 subtests skipped.
Files=18, Tests=758, 25 wallclock secs ( 3.59 cusr +  0.35 csys =  3.94 CPU)

Finally, sudo make install to install the Perl module:

[primal:~/crud/DBD-mysql-2.0901] jray% sudo make install
Skipping /Library/Perl/darwin/auto/DBD/mysql/mysql.bs (unchanged)
Installing /Library/Perl/darwin/auto/DBD/mysql/mysql.bundle
Files found in blib/arch: installing files in blib/lib into architecture dependent tree
...
Installing /usr/share/man/man3/Bundle::DBD::mysql.3
Installing /usr/share/man/man3/DBD::mysql.3
Installing /usr/share/man/man3/DBD::mysql::INSTALL.3
Installing /usr/share/man/man3/Mysql.3
Writing /Library/Perl/darwin/auto/DBD/mysql/.packlist
Appending installation info to /System/Library/Perl/darwin/perllocal.pod

The module is installed and ready to use. To view documentation for any of the installed modules, type perldoc <module name> .

Using Perl and MySQL (`DBD::mysql`)

The DBD::mysql module uses an object-oriented model to carry out database translations. Because object-oriented programming is a bit beyond the scope of this book, we'll take a look at two examples: Adding information to a database and displaying information contained in a table. You should be able to modify these examples to your own applications, or, if you need more functionality, I recommend adding a Perl book to your library.

Displaying a Table

The easiest way to retrieve information from a MySQL database is to compose a query and retrieve the results, one record at a time. To be able to do this, you must connect to the database, issue the query, determine the number of results, and loop through a display of each one. Listing 22.1 shows the surprisingly short code necessary to do just that.

Example 22.1. Display the Result of a MySQL Query

1: #!/usr/bin/perl
2:
3: use DBI;
4:
5: $user="";
6: $pass="";
7: $database="employee";
8: $dsn="DBI:mysql:database=$database;host=localhost";
9: $sql="select firstname,lastname,title from tblemployee,tbljobclassification where

      
    tblemployee.titleID=tbljobclassification.titleID";
10:
11: $dbh=DBI->connect($dsn,$user,$pass);
12: $sth=$dbh->prepare($sql);
13: $sth->execute;
14:
15: $numrows=$sth->rows;
16: $numfields=$sth->{ 'NUM_OF_FIELDS'} ;
17: $nameref=$sth->{ 'NAME'} ;
18:
19: for ($x=0;$x<$numrows;$x++) {
20:     $valueref = $sth->fetchrow_arrayref;
21:     print "----------------------------\n";
22:     for ($i=0;$i<$numfields;$i++) {
23:         print "$$nameref[$i] = $$valueref[$i]\n";
24:     }
25: }

The following lines describe how the Perl code interacts with the MySQL database through the DBI module:

Line 3— Use the DBI module. This must be included in any Perl application that accesses MySQL.
Lines 5–9— Set up the username, password, database name, and sql that will be used to access the database. The $dsn variable contains a string that will be used to set up the connection to MySQL. The format of this string cannot change, although the database and hostname can.
Line 11— Connect to the database using the previously defined connection string and username and password. The variable $dbh is a handle that references the database connection.
Line 12— Prepare the SQL for execution.
Line 13— Execute the SQL statement, and return a reference to the results in the variable $sth.
Line 15— Store the number of returned rows in the $numrows.
Line 16— Store the number of fields (columns) in the result within $numfields.
Line 17— Store a reference to an array containing the field names in the variable $nameref.
Lines 19–25— Loop through each of the rows in the result.
Line 20— Fetch a row of the result and return the field values in an array referenced by $valueref.
Line 21— Print a divider between each output record.
Line 22–24— Loop based on the number of fields in the result. Display each fieldname followed by the value stored in that field.

Executing the code (assuming that the employee database from earlier in the chapter is in place) produces output like this:

[primal:~/perlex] jray% ./display.pl
----------------------------
firstname = Maddy
lastname = Green
title = Programmer/Analyst
----------------------------
firstname = Will
lastname = Ray
title = Programmer/Analyst
----------------------------
firstname = Joan
lastname = Ray
title = Programmer/Analyst
----------------------------
firstname = Jack
lastname = Derifaj
title = Programmer/Analyst
----------------------------
firstname = Russ
lastname = Schelby
title = Programmer/Analyst
----------------------------
firstname = Robyn
lastname = Ness
title = Web Developer
----------------------------
firstname = Anne
lastname = Groves
title = Web Developer
----------------------------
firstname = Julie
lastname = Vujevich
title = Web Developer
----------------------------
firstname = Bill
lastname = Gates
title = CEO/President
----------------------------
firstname = Steve
lastname = Jobs
title = CEO/President

Obviously, the syntax of this code is a bit different from the Perl that you've seen so far, but it should be easy enough to understand that you can modify the code to fit your application.

Storing Data

You probably noticed that the code for displaying the results of a query was very modular. In fact, you can use the same code to insert a record into the database. Listing 22.2 demonstrates the code needed to store data in the tblemployee table.

Example 22.2. Display the Result of a MySQL Query

1: #!/usr/bin/perl
2:
3: use DBI;
4:
5: $user="";
6: $pass="";
7: $database="employee";
8: $id="11"; $firstname="Troy"; $lastname="Burkholder";
9: $titleID="2"; $salary="45000";
10: $dsn="DBI:mysql:database=$database;host=localhost";
11: $sql="insert into tblemployee values ('$id','$firstname',' $lastname','$titleID'

      
   ,'$salary')";
12:
13: $dbh=DBI->connect($dsn,$user,$pass);
14: $sth=$dbh->prepare($sql);
15: $sth->execute;

The only difference between this code and the previous script is the definition of the values for an insert (lines 8 and 9) and the definition of the insert statement itself (line 11). The SQL statement can be whatever arbitrary SQL code you'd like. If the statement returns results, they can be read and displayed with the techniques in the previous code.

Summary | Next Section

Previous Section