The Perl DBI offers many different ways of fetching the results of a SELECT statement. This tutorial is only going to demonstrate the fastest, most generally applicable, method.
- 1. $dbh->prepare() the SQL statement handle
- 2. $sth->execute() the prepared statement
- 3. $sth->bind_col() the columns to local Perl variables
- 4. $sth->fetch() each row until it returns undef
# Declare the SELECT statement
my $sql = q{SELECT name, description
FROM menu.food
WHERE name LIKE ?};
# Prepare the SELECT statement
# Apache Derby will return an error if the prepare fails.
my $sth = $conn->prepare($sql);
# Pass in the value for the query placeholder
# For multiple values in a single statement, you can pass an array
my $result = $sth->execute($search);
# Declare the variables to which the columns will be bound
my ($name, $description);
# Bind the variables to the returned columns
$result = $sth->bind_columns(\$name, \$description);
# Loop through each row
while ($sth->fetch) {
print "<p>Name: $name <br />Description: $description</p>\n";
}