MySQL: Two Ways of Extracting Data using Perl

With Perl, you can extract data from a MySQL database using the DBI module, with one of two methods it offers, fetchrow_array() and fetchrow_arrayref().

When interacting with the Perl DBI module, MySQL returns one row of data at a time, one element of data per column. fetchrow_array() allows you to work with each row of data as it is called, usually by using a loop of some sort. fetchrow_arrayref() stores the entire result of a query, which may have multiple rows, into an array, which you can unroll later.

fetchrow_array() is best for calls you where you want to process the results immediately. fetchrow_arrayref() is more suited for those times when you have to draw multiple sets of data from a database. Using fetchrow_arrayref(), your program fetches everything it needs first, through multiple SQL calls, and then parses the results later.

fetchrow_arrayref

Here's how to implement: First, For a Perl program to draw MySQL data, first you call the DBI module:

use DBI;

Next you open a connection with the database, filing in the values for the database, log-in name and password, thusly:

$dbh = DBI->connect('DBI:mysql:[NAME OF DATABASE]', '[LOG IN NAME]', '[LOG IN PASSWORD]')
|| die "ERROR: $DBI::errstr";

Then you prepare the SQL statement:

$query = "[SQL QUERY (w/o trailing semicolon)]";

Here you prepare and execute:

$sth = $dbh->prepare($query);
$sth->execute();

From here, you use either fetchrow_array() or fetchrow_arrayref().

Using fetchrow_array(), you'd set up a loop that catches each column of data as a separate variable. Here is an example using a MySQL table with 3 columns per row:

while ( @row = $sth->fetchrow_array) {
      $variable1 = $row[0];
      $variable2 = $row[1];
      $variable3 = $row[2];
                                                    }

Finally, after you've completed your database calls, you should close the connection:

$sth->finish():
$dbh->disconnect();

Here is a working template for a Perl fetchrow_array-based program:

#!/usr/bin/perl
# Note the perl DBI module needs to be installed, along with the DBI connector
# for your specific database--DBI--in this case.... 
# NOTE, for this program to work, you need to fill in the particulars of 
# your own database here, in the [] brackets

use DBI;

$dbh = DBI->connect('DBI:mysql:[NAME OF DATABASE]', '[LOG IN NAME]', '[LOG IN PASSWORD]')
			|| die "ERROR: $DBI::errstr";
$sth = $dbh->prepare('[SQL QUERY STATEMENT GOES HERE]');
$sth->execute();

while ( @row = $sth->fetchrow_array) {
	print "@row\n";
	print "$row[0]\n";
	print "$row[1]\n";
	print "$row[2]\n";
					}

fetchrow_arrayref

In the second approach, fetchall_arrayref(), stores the entire set of results from the query within a single array. Then you extract data from the array.

As in the previous example, you prepare and execute the data query the same way, but then you use fetchall_arrayref() to store the results in a Perl array. So:

$data = $sth->fetchall_arrayref();
$sth->finish;

foreach $data ( @$data) {

        ($variable1, $variable2, $variable3) = @$data;
                    print "$variable1\n";
                     print "$variable2\n";
                     print "$variable3\n";
                                   }

Note you close "sth" before the data is processed here. This allows you to make multiple calls before processing the data with Perl. Don't forget to close the database though ("$dbh->disconnect()").

A working template for a fetchall_arrayref()-based program can be found below:

#!/usr/bin/perl

#This approach stores the results of a SQL call in an array. In this example,
#the query is calling for rows with three columns of data, and printing each
#row

use DBI;

$dbh = DBI->connect('DBI:mysql:[DATABASE]', '[USER]', '[PASSWORD]')
			|| die "ERROR: $DBI::errstr";

$query = "[SQL QUERY GOES HERE]";
$sth = $dbh->prepare($query);
$sth->execute();
$data = $sth->fetchall_arrayref();
$sth->finish;

foreach $data ( @$data) {
	($variable1, $variable2, $variable3) = @$data;
	print "$variable1\n";
	print "$variable2\n";
	print "$variable3\n";
					}

$dbh->disconnect();

Core material taken from the book "MySQL in a Nutshell." All mistakes are my own, though.

MySQL

Back