Databases Reference
In-Depth Information
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 the
INSERT
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
";
