Managing & Using MySQL, 2nd Edition

Basic Perl Example

Our first program is about as simple as Perl DBI can get. Example 9-1 is a program you can run at the command line to query a database and display the results. We’ll describe how it works in this section, but you won’t actually be able to run it until you’ve done the setup described in the next section.

Example 9-1. Submitting a query to MySQL using Perl DBI

#!/usr/bin/perl -w
  
use strict;
use DBI;
  
my $server = 'localhost'
my $db = 'Books';
my $username = 'andy' ;
my $password = 'ALpswd' ;
  
my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password);
  
my $query = "SELECT * FROM Titles"; 
my $sth = $dbh->prepare($query);
$sth->execute(  );
  
while (my $row = $sth->fetchrow_arrayref) {
    print join("\t", @$row),"\n";
}
  
$dbh->disconnect;

The basic sequence of events (which will be the same for nearly any Perl DBI application) is:

1. Connect to your database.

2. Build a query.

3. Build a statement handle.

4. Execute the statement handle.

5. Retrieve and process the results.

Now for a look at the code, line by line.

As with all Perl modules, you must issue a use statement to get access to the DBI module:

use DBI;

All interactions between Perl and MySQL are conducted through what is known as a database handle . This is an object that implements all the methods used to communicate with the database. You may have as many database handles open at once as you wish, limited only by your system resources.

The first DBI method you invoke, therefore, is a connect( ) method that creates the database ...