How to fetch a single row as array in Perl DBI?

When using DBI in Perl to fetch a single row as an array, the fetchrow_array method is ideal. It returns the next row as a list of column values in the order requested by your SQL statement. This method must be called in a list context (for example, assigning to an array) to retrieve all columns. In scalar context, it returns only the first column's value.

Key Points

• fetchrow_array returns the next row’s columns as a list in list context.
• Always call execute on the statement handle before fetching rows.
• Check if rows were returned by testing if the array contains elements.
• Be cautious with strings containing @ signs; use double or single quotes properly to avoid interpolation errors.

Fixed Example: Fetch One Row as Array with DBI and SQLite

This example uses an in-memory SQLite database (no file access) and core DBI and DBD::SQLite. It creates a table, inserts a row, then fetches that row using fetchrow_array. The data is printed to STDOUT confirming execution.

use strict;
use warnings;
use DBI;

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

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

# Insert a row (use single quotes or escape '@' to avoid interpolation issues)
$dbh->do("INSERT INTO users (name, email) VALUES (?, ?)", undef, 'Alice', 'alice@example.com');

# Prepare and execute a SELECT statement
my $sth = $dbh->prepare("SELECT id, name, email FROM users WHERE name = ?");
$sth->execute('Alice');

# Fetch a single row as an array
my @row = $sth->fetchrow_array;

if (@row) {
    print "Fetched row as array:\n";
    print "ID: $row[0]\n";
    print "Name: $row[1]\n";
    print "Email: $row[2]\n";
} else {
    print "No matching row found.\n";
}

$sth->finish;
$dbh->disconnect;

Explanation

Assigning @row = $sth->fetchrow_array forces list context, so all columns are retrieved. Each element corresponds to a column in the order you specified in the SELECT. If you use scalar context, like $row = $sth->fetchrow_array, only the first column is returned. The example also shows the importance of quoting string literals correctly to avoid Perl interpreting @example as an array.

Common Pitfalls

• Using double quotes around strings with @ (such as email addresses) can cause Perl to misinterpret it as an array variable. Using single quotes or escaping solves this.
• Forgetting to call execute before fetching.
• Mixing scalar and list context for fetchrow_array unintentionally.
• Not checking for an empty row result (fetch returns empty list or undef at end).

Version Notes

fetchrow_array has been consistent since early DBI versions. The example requires DBI and DBD::SQLite installed (both core/common modules). This code works on Perl 5.10+ and later.

With these tips, you can reliably fetch and use single rows of data as arrays from your Perl DBI database queries!