General workflow

A general DBI workflow might look like this:

• Connect to database
• Prepare query
• Execute query
• Fetch data
• Disconnect from database

Let's try to select all the users from database and print their first and last names.

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

my $sth = $dbh->prepare('SELECT firstname, lastname FROM users');
$sth->execute;

while (my @row = $sth->fetchrow_array) {
    my ($firstname, $lastname) = @row;

    say "$firstname $lastname";
}

Besides fetchrow_array there is also fetchrow_arrayref which returns a reference instead of an array.

Sometimes you might want to fetch all the data at once instead of getting row by row, you can use fetchall_arrayref method for that:

my $dbh =
  DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});

my $sth = $dbh->prepare('SELECT firstname, lastname FROM users');
$sth->execute;

my $rows = $sth->fetchall_arrayref;

foreach my $row (@$rows) {
    my ($firstname, $lastname) = @$row;

    say "$firstname $lastname";
}