Dabbling in Live Databases: MySQL

The real effort this time around was learning to setup and use MySQL in the first place. Now, we've got that settled and sorted out. We've even got the Clients database re-created. So let's get ready to query.

As with last month, you will need to retrieve and add some Perl modules to your Perl installation, all of which are available from the Perl Modules page on the Downloads page at your nearest MySQL mirror:

• If your Perl installation is version 5.004 or older, you'll need the DataDumper module. The related DataShowTable module is optional but recommended.

• You definitely need the DBI module!

• You certainly want the Msql-MySQL modules (this includes the DBD::MySQL module).

Each of these modules contains simple installation instructions, and all are standard procedure if you've ever previously compiled and installed a Perl module in Unix.

We begin and end this article with a simple Perl script, which simply queries the billing table for all fields and dumps the output to the screen. Last month's coverage of the DBI module contains everything else you need to know to write your own Perl scripts to query this database, be it MySQL, Access, or whatever other database you install a DBD module for.

#Perl-based Perl/DBI/MySQL example
use DBI;

#open connection to Access database
$dbh = DBI->connect('dbi:mysql:Clients','serf','readonly');

The syntax of our DBI connect() call should be straightforward. Whereas we specified the dbi:odbc interface last month, this time around we call on dbi:mysql, courtesy of the DBD::MySQL module which you installed from the Msql-MySQL module package. For the sake of prudence, we connect to the database using our simpleton read-only account named serf, password "readonly", created earlier in this article.

What if, you wonder, the query script is on a different host than the MySQL server? You can specify the network location of the MySQL server in your DBI connect() call. Of course, be sure that the MySQL user account you specify is allowed to connect from the remote host on which the script is running!

$dbh = DBI->connect
('dbi:mysql:Clients:fast.server.com','serf','readonly');

The above would attempt to connect to the MySQL server running on the machine fast.server.com. This assumes the default MySQL server port 3306. You can also specify an alternate port, such as 4040:

$dbh = DBI->connect
('dbi:mysql:Clients:fast.server.com:4040','serf','readonly');

Once the database connection is made, you can simply rely on the DBI standard methods to prepare, execute, and retrieve the results of your SQL statements, exactly as we did last month. Here's our full script, basically last month's with only the MySQL modifications at the beginning.

#Unix-based Perl/DBI/MySQL example
use DBI;

#open connection to Access database
$dbh = DBI->connect('dbi:mysql:Clients','serf','readonly');

#prepare and execute SQL statement
$sqlstatement="SELECT ClientName,ClientEmail FROM billing";
$sth = $dbh->prepare($sqlstatement);
$sth->execute || 
      die "Could not execute SQL statement ... maybe invalid?";

#output database results
while (@row=$sth->fetchrow_array)
 { print "@row\n" }

In addition to the standard DBI methods, the DBD::MySQL module offers several MySQL-specific methods. You may not need to use these methods at all -- and probably shouldn't if you need to write Perl code that is truly database independent -- but you can survey them at the DBI Interface section of the MySQL reference manual.