Executing an SQL Query with DBI

The last example, connect.pl, demonstrated how we can connect to the database. Now it is time to talk about how to execute arbitrary SQL queries.⁷ We will first look at a program that will connect to the database musicians_db and display all the rows in the musicians table. It is called showmusicians.pl :

#!/usr/bin/perl -w
# showmusicians.pl

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# prepare the query to get the data out
# of the musicians table
my $sth = $dbh->prepare("SELECT player_id,name,phone FROM musicians")
             or die "prepare failed: " . $dbh->errstr();

$sth->execute() or die "execute failed: " . $sth->errstr();

my($player_id, $name, $phone);

# loop through each row of data, printing
it
while (($player_id, $name, $phone) = $sth->fetchrow()) {
    print "$player_id : $name : $phone\n";
}

$sth->finish();

$dbh->disconnect();

This program connects as before, prepares and executes an SQL query, and then loops through the result of the query. Here is the code that prepares the query:

# prepare the query to get the data out
# of the musicians table
my $sth = $dbh->prepare("SELECT player_id,name,phone FROM musicians")
             or die "prepare failed: " . $dbh->errstr();

The database handler, $dbh , executes the prepare() method. This method’s job is to take its argument, an SQL query, and compile it and prepare it to execute. The query in this case is
"SELECT player_id,name,phone FROM musicians" , which will select those three fields from the musicians table. If the prepare() method succeeds, it returns back an object, known as the state handler, that is assigned to $sth . If the prepare() method fails, it returns back false, and if so, we die() printing $dbh->errstr() , the reason for the failure.

If all is well at this point, we then execute the query.

$sth->execute() or die "execute failed: " . $sth->errstr();

The execute() method executes the query, storing the result into the $sth object. If execute() fails, we die() , and explain why by executing $sth->errstr() .

The $sth object has the result of the query stored within it, so we have to retrieve that information. The fetchrow() method does this for us.

# loop through each row of data, printing it while (($player_id, $name, $phone) = $sth->fetchrow()) {
    print "$player_id : $name : $phone\n";
}

The fetchrow() method returns the next row of information returned by the query. This code takes that row and copies it memberwise into three variables. Since our query asked for the player_id , name , and phone from the musicians table, we take those three pieces of infor mation and store them in $player_id , $name , and $phone , respectively. Those variables are then printed.

After we are done with the state handler, it is good practice to finish it with this code:

$sth->finish();

Executing this code produces the following:

$ perl showmusicians.pl
1 : Roger Waters : 555-1212
2 : Geddy Lee : 555-2323
3 : Marshall Mathers III : 555-3434
4 : Thom Yorke : 555-4545 
5 : Lenny Kravitz : 555-5656
6 : Mike Diamond : 555-6767
$