Learning MySQL (2007)
Part V. Interacting with MySQL Using Perl
Chapter 17. Using Perl with MySQL
Now that you can find your way about Perl, let’s see how you can use it to connect to a MySQL server. In this chapter, we look at how we can use the Perl scripting language to connect to a MySQL database and interchange data with it. We create command-line clients to import data into a database, and to query a database and display results.
The Perl DBI module supports a variety of database drivers. Naturally, we’re interested in the MySQL driver for this book, but there are others. Theoretically, you should be able to simply change the driver referenced by your script to get your script to work with another supported database management system, such as Oracle. In practice, however, you’ll need to put in some additional thought into writing your scripts so that you don’t use MySQL-specific constructs such as SHOW TABLES that won’t necessarily work on other database servers. Of course, this isn’t an issue if you’re certain you won’t ever change databases, but it’s a good idea to think carefully about how your application is likely to be used in a few years’ time.
Connecting to the MySQL Server and Database
To access the MySQL server from a Perl script, we need the DBI module discussed in Setting up Perl” in Chapter 2. In the script, we must tell Perl that we want to use this module:
use DBI;
Then, we provide connection parameters to the DBI connect() function and store the returned connection in a database handler (dbh):
my $dbh=DBI->connect("DBI:mysql:host=localhost;database=mysql",
"the_username",
"the_password");
For a Mac OS X server using the XAMPP Perl installation, you would write:
my $dbh=DBI->connect("DBI:mysql:
host=localhost;mysql_socket=/Applications/xampp/xamppfiles/var/mysql/mysql.sock;
database=mysql",
"the_username",
"the_password");
Don’t add a new line before the database=mysql parameter; we had to do this so that the instruction would fit on the page. If you followed our XAMPP installation instructions in Installing Perl modules under Mac OS X” in Chapter 2 to create a symbolic link to the default MySQL socket file location (/tmp/mysql.sock), you can omit the mysql_socket parameter.
We can follow this with Perl instructions to carry out the main functions of the script. Finally, being good citizens, we disconnect from the server before we end the script:
$dbh->disconnect();
The arrow (arrow -> operator) operator is used to call functions that are associated with a class of objects. In the connection code, we’ve called on the connect() function of the DBI class. We also call on functions associated with the database handler and the statement handler in the same way. We don’t describe object-oriented design and programming in detail in this book.
Handling Errors When Interacting with the Database
If the connection to the database fails, the dbh variable will contain an undefined (effectively false) value. We should test for this and stop the program if the connection failed; otherwise, we’ll run into difficulties once the program tries to use the database, generating unhelpful error messages such as:
Can't call method "prepare" on an undefined value at ./select.pl line 9.
One way to test for connection failure is simply to check the value of the database handler variable:
my $dbh=DBI->connect("DBI:mysql:host=localhost;database=$DB_Database",
"$DB_Username", "$DB_Password");
if(!$dbh)
{
die("Failed connecting to the database.");
}
If the database handler is not valid, we use the die() function to print an error message and stop the program.
A more compact way is to use the or keyword to execute the die() function if the connection failed:
my $dbh=DBI->connect("DBI:mysql:host=localhost;database=$DB_Database",
"$DB_Username", "$DB_Password")
or
die("Failed connecting to the database.");
Finally, we can modify problem-handling behavior by setting the attributes PrintError and RaiseError in the call to the connect() function. Setting the PrintError attribute to 1 displays error messages; setting it to 0 disables this. Similarly, setting the RaiseError attribute to 1displays an error message and stops processing if an error occurs; setting it to 0 disables this. If both are set to 0, no error messages are displayed, and the program tries to continue even if the connection to the MySQL database could not be established. We can use this setting to suppress the default Perl messages and display only our own custom messages:
my $dbh=DBI->connect("DBI:mysql:host=localhost;database=mysql", "the_username",
"the_password", {PrintError=>0, RaiseError=>0})
or
die("Failed connecting to the database");
By default, PrintError is 1 and RaiseError is 0.
The DBI module includes the special variables $DBI::err and $DBI::errstr to store information if a problem occurs. These contain, respectively, the error code and human-readable error message returned by the database server. The two colons are a Perl convention for separating the name of a package (in this case, DBI) from the name of a variable defined in the package (err and errstr).
We can use these variables in our own error-handling code. For example, we can write:
my $dbh=DBI->connect("DBI:mysql:host=localhost;database=mysql",
"the_username",
"the_password", {PrintError=>0, RaiseError=>0})
or
die("Failed connecting to the database (error number $DBI::err):
$DBI::errstr\n");
If RaiseError is set to 1, a failure to connect to the database might produce the error message:
DBI connect('host=localhost;database=mysql','root',...) \
failed: Can't connect to local MySQL server through socket \
'/var/lib/mysql/mysql.sock' (2) at ./select.pl line 5
whereas our custom error message above would be displayed as:
Failed connecting to the database (error number 2002): \
Can't connect to local MySQL server through socket \
'/var/lib/mysql/mysql.sock' (2)
While detailed error messages are very useful when debugging code under development, it’s generally prudent to hide errors from your users in production code, especially in applications that are published to the Web. Instead, have the program log error messages to a file or email them to you. That way, users aren’t exposed to unsightly and possibly confusing error messages, and the internals of your system are not as exposed to potential attackers. When your program does encounter a problem, display a generic error message such as “We are experiencing technical difficulties; please contact the system administrator or try again later.”
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 %-30s\n", "---", "------------------------");
while(my $ref=$sth->fetchrow_hashref())
{
printf ("%-5s %-128s\n", $ref->{artist_id}, $ref->{artist_name});
$count++;
}
printf ("\nTotal:%d\n", $count);
$sth->finish();
$dbh->disconnect();
Save this script as select.pl and run it as:
$ ./select.pl
You should get a display similar to this:
ID: Name:
--- ------------------------
1 New Order
2 Nick Cave & The Bad Seeds
3 Miles Davis
4 The Rolling Stones
5 The Stone Roses
6 Kylie Minogue
Total:6
With fetchrow_hashref(), the example obtains a hash for each row. There are other ways to access the query results. The fetchrow_array() function returns an array, where you access elements by index rather than by key. The elements in the array are in the order returned by theSELECT statement. In our example, the SELECT * FROM artist statement returns the artist table fields in the order that they appear in the database; you can find this by running the query from the MySQL monitor or looking at the table description (here it has been truncated to fit the page):
mysql> DESCRIBE artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | YES | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.22 sec)
If we wanted to print the artist name before the artist ID, we would access array element 1 first for the artist name and then array element 0 for the artist ID:
while(my @val=$sth->fetchrow_array())
{
printf ("%-128s %-5s\n", $val[1], $val[0]);
$count++;
}
We can also return a reference to the results array, rather than the array itself, using the fetchrow_arrayref() function. Here, we find another application of the arrow operator; the elements in the array can be accessed through the array reference:
while(my $ref=$sth->fetchrow_arrayref())
{
printf ("%-5s %-128s\n", $ref->[0], $ref->[1]);
$count++;
}
Both fetchrow_array() and fetchrow_arrayref() are faster than using fetchrow_hashref(), but are more prone to problems in mixing up column index numbers. They may also have problems if you alter the order of fields—for example, with an ALTER TABLE statement. We recommend that you use fetchrow_hashref() for all but the most time-sensitive applications.
Perl is case-sensitive, so accessing the fetched results using incorrect capitalization won’t work. In our example, we’re interested in the artist_id and artist_name fields of the music.artist table. If we try to access the results using, say, uppercase names:
printf ("%-5s %-128s\n", $ref->{ARTIST_ID}, $ref->{ARTIST_NAME});
blanks will be printed where we expect the field values to be, because ref->{ARTIST_ID} is not the same as ref->{artist_id}. If in doubt, try out a SHOW CREATE TABLE table_name; or DESCRIBE table_name statement from the MySQL monitor to see exactly how to capitalize field names.
Alternatively, you can use all uppercase or all lowercase field names and ask the fetchrow_hashref() function to force all the names to uppercase or lowercase when it retrieves them:
while(my $ref=$sth->fetchrow_hashref("NAME_uc"))
{
printf ("%-5s %-128s\n", $ref->{ARTIST_ID}, $ref->{ARTIST_NAME});
$count++;
}
If you use "NAME_uc", your Perl code should use all uppercase labels; you should use all lowercase labels if you force everything to lowercase with "NAME_lc".
Before we end our discussion on accessing result sets, let’s look at a couple of high-level ways of performing all the prepare and execute operations together. The selectall_arrayref() function returns a two-dimensional array containing the query results. For example, a query to list the contents of the music.artist table might return the following array:
+---+---------------------------+
| 0 | 1 |
+---+---------------------------+
| 0 | New Order |
| 1 | Nick Cave & The Bad Seeds |
| 2 | Miles Davis |
| 3 | The Rolling Stones |
| 4 | The Stone Roses |
| 5 | Kylie Minogue |
+---+---------------------------+
Each row of the array is itself an array containing the result fields. The rows and columns can be addressed with an index starting from 0.
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";
}
Let’s explore this by looking at four possibilities. Imagine you have a database called perltest and it includes a table called testtable with two string columns; these could have been created from the monitor with the following statements:
mysql> CREATE DATABASE perltest;
Query OK, 1 row affected (0.23 sec)
mysql> USE perltest;
Database changed
mysql> CREATE TABLE testtable (col1 CHAR(40), col2 CHAR(40));
Query OK, 0 rows affected (0.01 sec)
If the query is:
my $Query="INSERT INTO testtable (col1, col2) VALUES ('abcd1', 'abcd2')";
we’d get the message:
Operation was successful (1 rows)
reflecting the number of rows inserted.
Deleting matching rows with the query:
my $Query="DELETE FROM testtable WHERE col1='abcd1' AND col2='abcd2'";
would give us:
Operation was successful (1 rows)
reflecting the number of rows deleted.
Trying to delete data items that don’t exist in the database:
my $Query="DELETE FROM testtable WHERE col1='xabcd1' AND col2='abcd2'";
would return 0E0, so the success message is printed:
Operation was successful but no rows were affected
Finally, if the do() operation could not be performed for any reason (for example, an incorrect SQL query, duplicate key, or a nonexistent table), the failure message would be printed. If we tried to access the nonexistent table nosuchtable:
my $Query="DELETE FROM nosuchtable WHERE col1='abcd1' and col2='abcd2'";
This would result in the message:
Operation failed: Table 'perltest.nosuchtable' doesn't exist
Binding Queries and Variables
The Perl DBI module offers a convenient way to write SQL queries with placeholders that can be replaced by arguments to the execute() statement. Similarly, results from a query can be mapped to Perl variables. In this section, we describe how you can use placeholders in your queries.
Binding Variables to a Query
Consider the script we wrote earlier in Reading in values from a file” in Chapter 16 to read in a list of animals and their counts from the animals.csv file of comma-separated values, and load them into the Animals hash. We’ve reproduced the main part of the script in Example 17-2.
Example 17-2. Perl script to load data from a CSV file into the AnimalDB database
#!/usr/bin/perl
use strict;
# If the user hasn't provided one command-line argument, provide a
# helpful error message.
if(@ARGV!=1)
{
die("Syntax: $0 [Input file]\n");
}
# Open the file specified on the command line; if we can't open it,
# print an error message and stop.
open(INPUTFILE, $ARGV[0])
or
die("Failed opening $ARGV[0]\n");
my %Animals;
# Read in from input file line by line; each line is
# automatically placed in $_
while(<INPUTFILE>)
{
# Remove the newline at the end of the line
chomp($_);
# Split the line by commas and load into the AnimalsData array
my @AnimalsData=split(",", $_);
# Assign the text before the first comma to the name
my $AnimalName=$AnimalsData[0];
# Assign the text between the first comma and the second comma
# (if any) to the count
my $AnimalCount=$AnimalsData[1];
# Add an entry to the Animals hash for this animal name and
# count pair:
$Animals{$AnimalName}=$AnimalCount;
}
# Close the input file
close(INPUTFILE);
Let’s modify the script to load the data into a MySQL table. First, using the MySQL monitor, create a new AnimalDB database and a new Animals table:
mysql> CREATE DATABASE AnimalDB;
Query OK, 1 row affected (0.02 sec)
mysql> USE AnimalDB;
Database changed
mysql> CREATE TABLE Animals (Name CHAR(10), Count SMALLINT);
Query OK, 0 rows affected (0.01 sec)
To load the Animals hash into the Animals table in the database, we can create and execute an SQL query for each animal name and count pair:
while ((my $Animal, my $Count) = each(%Animals))
{
my $Query="INSERT INTO Animals (Name, Count) VALUES ($Animal, $Count)";
my $sth=$dbh->prepare($Query);
$sth->execute($Animal, $Count);
}
This requires us to prepare as many queries as there are data rows, which is inefficient.
A better way is to prepare a single query with placeholders and execute the query in turn with the different parameters:
my $Query="INSERT INTO Animals (Name, Count) VALUES (?, ?)";
my $sth=$dbh->prepare($Query);
while ((my $Animal, my $Count) = each(%Animals))
{
$sth->execute($Animal, $Count);
}
Here, the question-mark (?) symbols in the query are placeholders. The placeholders are replaced by the values passed to the execute() function. For each iteration of the while loop in our example, the $Animal and $Count values passed to the execute() function are plugged into theINSERT query, and the query is executed. This is known as binding; besides being more efficient, binding variables to a query in this way helps to prevent some types of security problems.
Binding Query Results to Variables
Binding can work the other way too: we can bind the results of a query to Perl variables. Earlier in this chapter, we saw how we can access the results of a query using fetchrow_hashref() and its related functions; for example, to access the Animals table data, we could have a script that uses the fetchrow_arrayref() function, as shown in Example 17-3.
Example 17-3. Perl script to read data from the Animals database
#!/usr/bin/perl
use DBI;
use strict;
my $DB_Database="AnimalDB";
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");
# Process the data to calculate the total;
my $Animal_Name;
my $Animal_Count;
my $Total=0;
print "Pet roll call:\n".
"===============\n";
my $Query="SELECT Name, Count FROM Animals";
my $sth = $dbh->prepare($Query);
$sth->execute ();
while(my $ref=$sth->fetchrow_arrayref())
{
printf("%-10d %-20s\n", $ref->[1], $ref->[0]);
$Total+=$ref->[0];
}
print "===============\n".
"Total:\t$Total\n";
$sth->finish();
$dbh->disconnect();
Example 17-4 removes the need for the $ref variable by using the bind_columns() function to bind the result columns to the Animal_Name and Animal_Count variables.
Example 17-4. Perl fragment to read and display data from the Animals database, using binding
my $Total=0;
print "Pet roll call:\n",
"===========\n";
my $Query="SELECT Name, Count from Animals";
my $sth = $dbh->prepare($Query);
$sth->execute ();
my $Animal_Name;
my $Animal_Count;
# Bind query results to variables
$sth->bind_columns(\$Animal_Name, \$Animal_Count);
while($sth->fetchrow_arrayref())
{
print "$Animal_Name:\t$Animal_Count\n";
$Total+=$Animal_Count;
}
print "===========\n",
"Total:\t$Total\n";
The values are assigned to variables in the bind_columns() function in the order they appear in the results of the SELECT, so we fetch the query results with the fetchrow_arrayref() function, rather than fetchrow_hashref(). While fetchrow_hashref() is often more convenient in other circumstances, there is no advantage to it here, and it would run more slowly. Note that when passing the variable names to the bind_columns() function, we add a backslash symbol (\) in front of the dollar symbol so that Perl leaves the variable name intact and doesn’t replace it with the value of the variable.
The Complete Script Using Both Types of Binding
For our example of loading data into the database, we can put both parts together to write a single script that loads data into the database and accesses this data, using both types of binding, as shown in Example 17-5.
Example 17-5. Perl script with both types of binding
#!/usr/bin/perl
use DBI;
use strict;
# If the user hasn't provided any command-line arguments, provide a
# helpful error message.
if(@ARGV!=1)
{
die("Syntax: $0 [Input file]\n");
}
# If the user has provided the command line arguments, fill in the
# Animals hash with the corresponding values.
# Open the file specified on the command line; if we can't open it,
# print an error message and stop.
open(INPUTFILE, $ARGV[0])
or die("Failed opening $ARGV[0]\n");
####################################################################
# Load data from the CSV file into the Animals hash
my %Animals;
while(<INPUTFILE>)
{
# Remove the newline at the end of the line
chomp;
# Split the line by commas
my @AnimalsData=split(",", $_);
# Assign the text before the first comma to the name
my $AnimalName=@AnimalsData[0];
# Assign the text between the first comma and the second comma
# (if any) to the count
my $AnimalCount=@AnimalsData[1];
# Add an entry to the Animals hash for this animal name and
# count pair:
$Animals{$AnimalName}=$AnimalCount;
}
close(INPUTFILE);
####################################################################
# Connect to the database
my $DB_Database="AnimalDB";
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 count $DBI::err): $DBI::errstr\n");
####################################################################
# Load the data into the database; variables bound to query
my $Query="INSERT INTO Animals (Name, Count) values (?, ?)";
my $sth=$dbh->prepare($Query);
while ((my $Animal, my $Count) = each(%Animals))
{
$sth->execute($Animal, $Count);
}
####################################################################
# Read the data from the database; query results bound to variables
my $Total=0;
print "Pet roll call:\n",
"===========\n";
my $Query="SELECT Name, Count from Animals";
my $sth = $dbh->prepare($Query);
$sth->execute ();
my $Animal_Name;
my $Animal_Count;
# Bind query results to variables
$sth->bind_columns(\$Animal_Name, \$Animal_Count);
while($sth->fetchrow_arrayref())
{
print "$Animal_Name:\t$Animal_Count\n";
$Total+=$Animal_Count;
}
print "===========\n",
"Total:\t$Total\n";
# Free the statement handler and disconnect from the database
$sth->finish();
$dbh->disconnect();
Importing and Exporting Data
From time to time, you may need to transfer data into the database from external sources, or to generate data in a format that other applications can use. A common file format for this is the comma-separated values (CSV) format discussed in Loading Data from Comma-Delimited Files” in Chapter 8. Data import and export is one of the areas in which Perl is very strong, and programs in Perl can read and write data in a large number of formats. For example, you can generate plain text, HTML, XML, or Rich Text Format (RTF) documents. RTF documents are more complex but can contain formatting instructions that most word processors understand. There are even Perl modules to process binary (nontext) formats, such as the Microsoft Excel spreadsheet file format.
Earlier, in Binding Variables to a Query,” we explained how to import data from a CSV file. Let’s now look at an example to export data from our Animals database to a CSV file. All we need to do is to use the print statement to write to the output file, with the data separated by a comma, as shown in Example 17-6.
Example 17-6. Perl script to export data from the Animals database, using binding
#!/usr/bin/perl
use DBI;
use strict;
# If the user hasn't provided any command-line arguments, provide a
# helpful error message.
if(@ARGV!=1)
{
die("Syntax: $0 [Output file]\n");
}
my $DB_Database="AnimalDB";
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 $Query="SELECT Name, SUM(Count) FROM Animals GROUP BY Name";
my $sth = $dbh->prepare($Query);
$sth->execute();
# Bind query results to variables
my $Animal_Name;
my $Animal_Count;
$sth->bind_columns(\$Animal_Name, \$Animal_Count);
# Open the file specified on the command line; if we can't open it,
# print an error message and stop.
open(OUTPUTFILE, ">$ARGV[0]")
or
die("Failed opening $ARGV[0]\n");
# Write header row with column names
print OUTPUTFILE "Name,Count\n";
# Iterate through the results and write them as comma-separated values
# to the output file
while($sth->fetchrow_arrayref())
{
print OUTPUTFILE "$Animal_Name,$Animal_Count\n";
}
$sth->finish();
$dbh->disconnect();
close(OUTPUTFILE);
If the data could contain a comma, the resulting file could be unusable. For example, if we want to export names and telephone numbers in the format:
Name,Number
we’d have difficulty if the data in the MySQL database were allowed to have commas, as it does here:
+-----------------+-----------------+
| Name | Number |
+-----------------+-----------------+
| Hamzeh Abdollah | +61 3 1234 5678 |
| Bloggs, Fred | +61 3 8795 4321 |
...
+-----------------+-----------------+
If we exported this data to a CSV file, we would have:
Hamzeh Abdollah,+61 3 1234 5678
Bloggs, Fred,+61 3 8795 4321
The spreadsheet program would take the second row to have the name “Bloggs” and the telephone number “ Fred”. To avoid this problem, we can enclose the data in double quotes when writing it out:
print OUTPUTFILE "\"$Name\",\"$Count\"\n";
Note that since the text to be written to file is already enclosed in double quotes, we’ve escaped (placed a backslash symbol before) the quotes surrounding the data. The exported data would be:
"Hamzeh Abdollah","+61 3 1234 5678"
"Bloggs, Fred","+61 3 8795 4321"
which is handled correctly by most spreadsheet programs.
Handling NULL Values
MySQL operations return undef for fields that have a NULL value; however, Perl handles these values as empty strings, so if we ask it to print the results, we’ll simply get blanks for NULL values. Example 17-7 checks whether fields are NULL and handles them, perhaps by setting them to the string "NULL".
Example 17-7. Perl script to handle NULL values
#!/usr/bin/perl -w
use DBI;
use strict;
my $DB_Database="AnimalDB";
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 $Query="SELECT Count from Animals";
my $sth=$dbh->prepare($Query);
$sth->execute();
while(my $ref=$sth->fetchrow_hashref("NAME_uc"))
{
my $Count=$ref->{COUNT};
if(!defined($Count))
{
$Count="NULL";
}
print "Count=$Count\n";
}
$sth->finish();
$dbh->disconnect();
Resources
See the MySQL AB page (http://dev.mysql.com/usingmysql/perl) and the DBI module documentation (http://search.cpan.org/~timb/DBI/DBI.pm).
Exercises
1. What does the Perl DBI module do?
2. When would you prefer to use fetchrow_array() over fetchrow_hashref()?
3. What are the advantages of binding variables to a query?
4. How should you handle NULL values in answers returned by MySQL?