Select a single row from a database table using Perl DBI

Objective

To select a single row from a database table using the Perl DBI module

Scenario

Suppose that there is a database table called employees with the following schema:

CREATE TABLE employees (
  employee_number INTEGER PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  dob DATE NOT NULL);

You wish to extract the name and date of birth of the employee with a given employee number.

Prerequisites

These instructions assume that:

• You have already made a connection to the database using the DBI module,
• The connection handle has been recorded in the variable $dbh, and
• The RaiseError option is enabled (therefore there is no need to explicitly check for errors after every DBI function call).

Method

For calling a SQL statement that is expected to return exactly one row, the most convenient DBI function is selectrow_array:

my $sql = "SELECT name,dob FROM employees WHERE employee_number=?";
my @row = $dbh->selectrow_array($sql,undef,$employee_number);
unless (@row) { die "employee not found in database"; }
my ($name,$dob) = @row;

The first argument to selectrow_array is the SQL statement to be executed. A terminating semicolon is not needed.

The second argument is for passing options by means of a hash ref. If it is undefined then the default options are used, which are acceptable for most purposes.

Subsequent arguments are values to be bound to placeholders within the SQL statement. Placeholders are represented by question marks. They can be used in place of any literal value within the statement (but not normally in place of other elements such as keywords or column names). Unlike interpolation, binding ensures that any unsafe characters are correctly escaped. You are strongly encouraged to make use of this mechanism in order to protect yourself from SQL injection attacks.

The unless statement is needed because, although a single row of data is the desired result, there is a possibility that no rows will be returned. This could happen because:

• the SQL query failed to execute, or
• it executed to completion but did not match any rows.

The assumption has been made here that the RaiseError option is enabled. This allows the first possibility to be excluded because an exception would have been thrown. The unless statement above can therefore conclude that if @row is empty then the query was executed to completion but nothing was found.

If more than one row is returned then selectrow_array silently discards all but the first. If this behaviour is inappropriate then you should consider using one of the functions that can accept an arbitrary number of rows. The example above relies on the database schema (specifically, the fact that employeee_number is a primary key) to ensure that no more than one row can match the query.

Note that the required columns are named explicitly in the SELECT statement. This is to avoid selecting more data than necessay, and to avoid making assumptions about the order of the columns in the table.

Troubleshooting

See Troubleshooting Perl DBI.

• Conventions
• Privacy
• Legal