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 musicianstable. It is calledshowmusicians.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 theprepare()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 theprepare()method succeeds, it returns back an object, known as the state handler, that is assigned to$sth. If theprepare()method fails, it returns back false, and if so, wedie()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();

Theexecute()method executes the query, storing the result into the$sthobject. Ifexecute()fails, wedie(), and explain why by executing$sth->errstr().

The$sthobject has the result of the query stored within it, so we have to retrieve that information. Thefetchrow()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";
}

Thefetchrow()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 theplayer_id,name, andphonefrom themusicianstable, we take those three pieces of information 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
$