How to execute a SELECT query with Perl DBI and fetch all rows?

Using the Perl DBI (Database Interface) module to execute a SELECT query and fetch all rows is a common task for database interaction in Perl scripts. DBI provides a database-independent interface, allowing you to run SQL queries and retrieve results easily.

Step-By-Step Explanation

• Connect to the database: Use DBI->connect with the appropriate data source name (DSN), username, and password.
• Prepare the SQL statement: This step compiles your query on the database server for efficient execution.
• Execute the statement: This actually runs the query.
• Fetch the rows: You can fetch rows one by one or fetch all rows at once depending on your needs.
• Clean up: Finish the statement handle and disconnect from the database.

In Perl, the DBI fetchall_arrayref method provides an easy way to fetch all rows returned by a SELECT statement as an array reference of arrays (each inner array being a row). Alternatively, you can use fetchrow_array in a loop for row-by-row fetching.

Perl DBI Concepts to Note

• $dbh is the database handle object returned by DBI->connect.
• $sth is the statement handle returned by $dbh->prepare.
• prepare allows you to compile your SQL statement safely and efficiently, helping avoid SQL injection when used with placeholders.
• execute runs the prepared statement. Parameters can be passed here.
• fetchall_arrayref fetches all rows at once into a Perl data structure.
• Perl's TMTOWTDI principle (“There’s more than one way to do it”) means you can also fetch rows with fetchrow_hashref, fetchrow_array, or even fetchall_hashref.

Common Pitfalls

• Not checking for errors after connect, prepare, or execute. Always check $DBI::errstr or use RaiseError.
• Fetching rows before executing the statement will fail.
• Not calling $sth->finish or $dbh->disconnect may lead to resource leaks, though disconnecting typically cleans up.
• Be aware of context: fetching in list context or scalar context changes return values.

Runnable Example

The following self-contained example uses an in-memory SQLite database (which is part of Perl core DBI/DBD::SQLite) to create a sample table, insert some data, execute a SELECT query, and fetch all rows.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# Connect to an in-memory SQLite database (no files)
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "",
    { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;

# Create a sample table
$dbh->do("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");

# Insert some rows
my $insert_sth = $dbh->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$insert_sth->execute("Alice", "alice\@example.com");
$insert_sth->execute("Bob", "bob\@example.com");
$insert_sth->execute("Carol", "carol\@example.com");

# Prepare the SELECT query
my $select_sth = $dbh->prepare("SELECT id, name, email FROM users ORDER BY id");

# Execute the query
$select_sth->execute();

# Fetch all rows as arrayref of arrays
my $rows = $select_sth->fetchall_arrayref();

print "Users in database:\n";

# Iterate and print each row
for my $row (@$rows) {
    my ($id, $name, $email) = @$row;
    print "ID: $id, Name: $name, Email: $email\n";
}

# Cleanup
$select_sth->finish();
$dbh->disconnect();

# Output:
# Users in database:
# ID: 1, Name: Alice, Email: alice@example.com
# ID: 2, Name: Bob, Email: bob@example.com
# ID: 3, Name: Carol, Email: carol@example.com

This example demonstrates a typical workflow:

• Connecting to a database.
• Creating and populating a table (to be self-contained).
• Preparing and executing a SELECT query.
• Using fetchall_arrayref to retrieve all rows at once.
• Looping over the fetched data to display results.
• Properly finishing the statement and disconnecting.

With this basic pattern, you can adapt the connection details and SQL for your own database system. If you plan to fetch results row-by-row, replace fetchall_arrayref with a while (my @row = $sth->fetchrow_array) loop.