Using Perl with MySQL - Learning MySQL - page 539

CHAPTER 17
Using Perl with MySQL
Now that you can find your way about Perl, let's see how you can use it to connect to
a MySQL server. In this chapter, we look at how we can use the Perl scripting language
to connect to a MySQL database and interchange data with it. We create command-
line clients to import data into a database, and to query a database and display results.
The Perl DBI module supports a variety of database drivers. Naturally, we're interested
in the MySQL driver for this topic, but there are others. Theoretically, you should be
able to simply change the driver referenced by your script to get your script to work
with another supported database management system, such as Oracle. In practice,
however, you'll need to put in some additional thought into writing your scripts so that
you don't use MySQL-specific constructs such as SHOW TABLES that won't necessarily
work on other database servers. Of course, this isn't an issue if you're certain you won't
ever change databases, but it's a good idea to think carefully about how your application
is likely to be used in a few years' time.
Connecting to the MySQL Server and Database
To access the MySQL server from a Perl script, we need the DBI module discussed in
“Setting up Perl” in Chapter 2. In the script, we must tell Perl that we want to use this
module:
use DBI;
Then, we provide connection parameters to the DBI connect( ) function and store the
returned connection in a database handler ( dbh ):
my $dbh=DBI->connect("DBI:mysql:host=localhost;database=mysql",
" the_username ",
" the_password ");
For a Mac OS X server using the XAMPP Perl installation, you would write:
my $dbh=DBI->connect("DBI:mysql:
host=localhost;mysql_socket=/Applications/xampp/xamppfiles/var/mysql/mysql.sock;