Using Perl with MySQL - Learning MySQL - page 545

The following snippet of code shows how to use the selectall_arrayref( ) function:
print "\n\nselectall_arrayref:\n";
my $ref=$dbh->selectall_arrayref($Query);
while(my $row=shift(@$ref))
{
printf ("%-5s %-128s\n", $row->[0], $row->[1]);
$count++;
}
We use the shift( ) function to fetch the rows from the array one at a time and then
access the columns by their index— 0 , 1 , 2 , and so on. There is a corresponding hash-
based selectall_hashref( ) function that you can also use:
my $ref = $dbh->selectall_hashref($Query, 'artist_id');
foreach my $artist_id (keys %$ref)
{
printf ("%-5s %-128s\n", $artist_id, $ref->{$artist_id}->{artist_name});
$count++;
}
The call to selectall_hashref( ) specifies the query and the hash key, and returns a
reference to a hash. Rows with identical hash keys are overwritten by later rows, so it's
important that the hash keys passed to selectall_hashref( ) be unique.
Using Queries That Don't Return Answer Sets
For queries such as INSERT , UPDATE , REPLACE , and DELETE that do not return a result set,
we can use the do( ) function to perform the query without the need for a prior call to
prepare( ) .
The do( ) function returns the number of rows affected by the query; if the query could
not be performed successfully, the function returns zero. If the query was performed
successfully but no rows were affected, the function returns the value 0E0 , which is 0
times 10 to the power 0 (for instance, 1E3 is 1000). Perl treats 0E0 as having the numerical
value zero but the Boolean value true, so if the query returns this value, we know that
the operation succeeded (true) but that zero rows were affected:
my $rowsaffected=$dbh->do($Query);
if($rowsaffected && $rowsaffected==0E0)
{
print "Operation was successful but no rows were affected\n";
}
elsif($rowsaffected)
{
print "Operation was successful ($rowsaffected rows)\n";
}
else
{
print "Operation failed: $DBI::errstr\n";
}