Learning MySQL

Using Queries That Return Answer Sets

Most of the queries used for database access—for example, SELECT queries—read information from the database for display or processing. For such queries, you first call the prepare() function to set up a statement handler and send it to the server, and then call the execute() function function to run the query:

my $sth=$dbh->prepare("SELECT * FROM artist");
$sth->execute();

You can examine the result of a query by using the fetchrow_hashref() function to fetch the answer rows one by one and place them in a hash; thus, you can access the individual hash elements for processing, as for the artist_id and artist_name fields below:

while(my $val=$sth->fetchrow_hashref())
{
    printf ("%-5s %-128s\n", $ref->{artist_id}, $ref->{artist_name});
}

Finally, you deallocate resources assigned to the statement handler:

$sth->finish();

Example 17-1 lists the artists and their ID numbers from the artist database.

Example 17-1. Perl script to select data from the database

#!/usr/bin/perl
use DBI;
use strict;

my $DB_Database="music";
my $DB_Username="root";
my $DB_Password="the_mysql_root_password"; my $dbh=DBI->connect( "DBI:mysql:host=localhost;database=$DB_Database", "$DB_Username", "$DB_Password", {PrintError=>0, RaiseError=>0}) or die("Failed connecting to the database ". "(error number $DBI::err):$DBI::errstr\n"); my $count=0; my $Query="SELECT * FROM artist"; my $sth=$dbh->prepare($Query); $sth->execute(); printf ("%-5s %-30s\n", "ID:", "Name:"); printf ("%-5s ...