Accessing MySQL from Perl Programs

This chapter describes:

• How to install the Perl database driver for MySQL and other required software.
• A sample Perl to demonstrate how to use DBI to access MySQL server.

Installing Perl Database Driver for MySQL

Here is what we need to access MySQL from Perl programs:

• Perl implementation.
• MySQL server.
• Perl DBI module.
• Perl DBD::mysql module.

For Perl implementation, I have installed ActivePerl. See my other book "Herong's Notes on Perl" for detail information.

To install MySQL server, see the previous chapter, "Installing MySQL".

Perl DBI (Database Interface) module is the basic abstraction layer for working with external database servers. If DBI module is not avaible on your Perl installation, you could to go to http://search.cpan.org/dist/DBI/ and download DBI-1.42.tar.gz. But installing DBI module from the .tar file seems to complicated.

Another easy way to install the DBI module is to use PPM (Perl Package Management) directly from Internet, if you have ActivePerl installed:

\perl\bin\ppm
PPM interactive shell (2.1.5) - type 'help' for available commands.
PPM> install DBI
Install package 'DBI?' (y/N): y
Installing package 'DBI'...
Bytes transferred: 435739
Installing D:\Perl\site\lib\auto\DBI\dbd_xsh.h
Installing D:\Perl\site\lib\auto\DBI\DBI.bs
......
Installing D:\Perl\html\site\lib\DBI.html
Installing D:\Perl\html\site\lib\Win32\DBIODBC.html
......
Installing D:\Perl\site\lib\DBI.pm
Installing D:\Perl\site\lib\Win32\DBIODBC.pm
......
Installing D:\Perl\bin\dbiprof
Installing D:\Perl\bin\dbiprof.bat
Installing D:\Perl\bin\dbiproxy
Installing D:\Perl\bin\dbiproxy.bat
Writing D:\Perl\site\lib\auto\DBI\.packlist
PPM> quit
Quit!

I don't know how PPM exactly works. My guess is that it comes with a list of modules (packages), not installed, but defined with information about where to go on the Internet to get them and how to install them. Intalling additional modules is so easy in this way, as you can see from this example. But I feel that there is a security risk here by allowing PPM to interact with Internet freely without you knowing what it is doing exactly.

The DBD:mysql module is the database driver for MySQL required by the DBI module. It can also be installed by PPM, if you have ActivePerl installed:

\perl\bin\ppm
PPM> install DBD-mysql
Install package 'DBD-mysql?' (y/N): y
Installing package 'DBD-mysql'...
Bytes transferred: 179124
Installing D:\Perl\site\lib\auto\DBD\mysql\mysql.bs
Installing D:\Perl\site\lib\auto\DBD\mysql\mysql.dll
......
Installing D:\Perl\html\site\lib\Mysql.html
Installing D:\Perl\html\site\lib\DBD\mysql.html
......
Installing D:\Perl\site\lib\Mysql.pm
Installing D:\Perl\site\lib\Mysql\Statement.pm
......
Writing D:\Perl\site\lib\auto\DBD\mysql\.packlist

But if you want to download the DBI module and install it yourself, you can go to http://www.mysql.com and download DBD-mysql-2.9003.tar.gz.

HelloMySQL.pl - My First Perl Program with MySQL

Now we are ready to write a Perl program using the DBI module to access MySQL server. Here my first example, HelloMySQL.pl:

# HelloMySQL.pl
# Copyright (c) 1999 by Dr. Herong Yang
#
   use DBI;
   $dbh = DBI->connect("DBI:mysql:test");
   $dbh->do("create table hello (message varchar(80))");
   $dbh->do("insert into hello (message) values ('Hello world!')");
   $sth = $dbh->prepare("select * from hello");
   $sth->execute();
   while ((@row) = $sth->fetchrow_array()) {
      print "$row[0]\n";
   }
   $sth->finish();
   $dbh->do("drop table hello");
   $dbh->disconnect();
   exit;

Let's start MySQL server, and run HelloMySQL.pl:

\perl\bin\perl HelloMySQL.pl
Hello world!

Congratulations, all required software and additional modules are working.