Learning MySQL

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]; ...