Perl Database Access

Perl - Database Access with DBI

Perl provides a powerful and standardized way to interact with databases through the DBI (Database Interface) module. DBI acts as an abstraction layer, allowing your Perl code to remain largely independent of the specific database system (e.g., MySQL, PostgreSQL, Oracle, SQLite) you are using. You’ll also need a database-specific driver (DBD) module, such as DBD::mysql for MySQL.

The DBI module offers a consistent set of methods, variables, and conventions for database operations.

Architecture of a DBI Application

The DBI architecture involves three main components:

• Your Perl Script: Uses the DBI API to send SQL commands and receive results.
• DBI (Database Interface): The generic interface module. It dispatches calls to the appropriate DBD.
• DBD (Database Driver): A specific driver for your chosen database (e.g., DBD::Pg for PostgreSQL, DBD::SQLite). This module handles the actual communication with the database server.

This layered approach means you can often switch databases by changing the connection string and ensuring the relevant DBD module is installed, with minimal changes to your core Perl logic.

Notations and Conventions

Throughout DBI documentation and examples, you’ll often see these variable naming conventions:

$dsn    # Data Source Name (string defining how to connect)
$dbh    # Database Handle (object representing the connection)
$sth    # Statement Handle (object representing a prepared SQL statement)
$rv     # Return Value (often an integer, e.g., number of rows)
$rc     # Return Code (boolean status, true for success)
@row    # Array to hold a fetched row of data
%attr   # Hash reference for attributes/options

Database Connection

To connect to a database, you use the DBI->connect() method. Ensure DBI and the appropriate DBD::* module are installed. For this example, we’ll assume MySQL.

Prerequisites (Example Scenario):

• A MySQL database named TESTDB exists.
• A table named EMPLOYEES exists in TESTDB with columns: ID (INT, PK, AutoIncrement), FIRST_NAME (VARCHAR), LAST_NAME (VARCHAR), AGE (INT), SEX (CHAR(1)), INCOME (DECIMAL).
• A user testuser with password test123 has privileges on TESTDB.

Connecting to MySQL:

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

my $driver   = "mysql";
my $database = "TESTDB";
my $host     = "localhost"; # Or your DB host
my $port     = "3306";    # Or your DB port

my $dsn = "DBI:$driver:database=$database;host=$host;port=$port";
my $userid = "testuser";
my $password = "test123";

# Connect with error handling attributes
my $dbh = DBI->connect($dsn, $userid, $password, {
    RaiseError => 1, # Automatically die on error
    PrintError => 0, # Don't print warnings to STDERR (RaiseError handles it)
    AutoCommit => 1  # Commit each statement automatically (can be turned off for transactions)
}) or die "Database connection not made: $DBI::errstr";

print "Connected to database successfully!\n";

# ... operations ...

$dbh->disconnect();
print "Disconnected from database.\n";

RaiseError => 1 is highly recommended as it makes error handling much simpler by automatically dying when a DBI method fails. $DBI::errstr contains the error message.

INSERT Operation

To insert data, you typically prepare an SQL INSERT statement, then execute it.

Steps:

• Prepare the SQL INSERT statement using prepare(). Use placeholders (?) for values to prevent SQL injection.
• Execute the prepared statement using execute(), passing the values for the placeholders.
• Optionally, call finish() on the statement handle if you won’t reuse it immediately (though often not strictly necessary for simple INSERTs if RaiseError is on).

# Assuming $dbh is a valid, connected database handle

my $sql_insert = q{
    INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
    VALUES (?, ?, ?, ?, ?)
};
my $sth = $dbh->prepare($sql_insert);

# Insert one record
my $first_name = 'John';
my $last_name  = 'Doe';
my $age        = 30;
my $sex        = 'M';
my $income     = 50000;

$sth->execute($first_name, $last_name, $age, $sex, $income);
print "Record inserted (John Doe). Rows affected: ", $sth->rows, "\n";

# Insert another record
$sth->execute('Jane', 'Smith', 28, 'F', 60000);
print "Record inserted (Jane Smith). Rows affected: ", $sth->rows, "\n";

# $sth->finish(); # Good practice if done with $sth for now

$sth->rows returns the number of rows affected by the last execute() for non-SELECT statements (like INSERT, UPDATE, DELETE).

READ (SELECT) Operation

To retrieve data, you prepare a SELECT statement, execute it, and then fetch the results.

Steps:

• Prepare the SQL SELECT query using prepare().
• Execute the query using execute() (passing bind values if any).
• Fetch results row by row using methods like fetchrow_array(), fetchrow_hashref(), or fetchall_arrayref().
• Call finish() on the statement handle when done fetching.

# Assuming $dbh is connected

my $min_age = 25;
my $sql_select = q{
    SELECT FIRST_NAME, LAST_NAME, AGE
    FROM EMPLOYEES WHERE AGE > ? ORDER BY LAST_NAME
};
my $sth = $dbh->prepare($sql_select);

$sth->execute($min_age);

print "Employees older than $min_age:\n";
# Fetching as an array of values for each row
while (my @row = $sth->fetchrow_array()) {
    my ($fname, $lname, $age_val) = @row;
    print " - $fname $lname, Age: $age_val\n";
}
# Alternative: Fetching as a hash reference for each row
# $sth->execute($min_age); # Re-execute if you want to fetch again
# while (my $row_hashref = $sth->fetchrow_hashref()) {
#    print " - $row_hashref->{FIRST_NAME} $row_hashref->{LAST_NAME}, Age: $row_hashref->{AGE}\n";
# }

$sth->finish();

UPDATE Operation

Updating records follows a similar pattern to INSERT.

# Assuming $dbh is connected

my $new_income_for_males = 55000;
my $target_sex = 'M';

my $sql_update = q{
    UPDATE EMPLOYEES SET INCOME = ? WHERE SEX = ?
};
my $sth = $dbh->prepare($sql_update);

$sth->execute($new_income_for_males, $target_sex);
print "Updated income for sex '$target_sex'. Rows affected: ", $sth->rows, "\n";

$sth->finish();

DELETE Operation

Deleting records also uses prepare and execute.

# Assuming $dbh is connected

my $age_to_delete = 30;

my $sql_delete = q{
    DELETE FROM EMPLOYEES WHERE AGE = ?
};
my $sth = $dbh->prepare($sql_delete);

$sth->execute($age_to_delete);
print "Deleted employees of age $age_to_delete. Rows affected: ", $sth->rows, "\n";

$sth->finish();

Using $dbh->do() for non-SELECT Statements

For simple INSERT, UPDATE, or DELETE statements that don’t require reusing a prepared statement or complex bind values (though it supports simple bind values), you can use $dbh->do() as a shortcut. It prepares and executes in one call.

# Assuming $dbh is connected
my $target_id_to_delete = 100; # Example ID

my $rows_affected = $dbh->do('DELETE FROM EMPLOYEES WHERE ID = ?', undef, $target_id_to_delete);
if (defined $rows_affected) {
    print "Delete using do(). Rows affected: $rows_affected\n";
} else {
    warn "Delete using do() failed: ", $dbh->errstr, "\n";
}

# Without placeholders (less safe if values are dynamic and not sanitized):
# $rows_affected = $dbh->do("DELETE FROM EMPLOYEES WHERE AGE = 30");

$dbh->do() returns the number of affected rows, or undef on error (if RaiseError is off). If RaiseError is on, it will die on error.

Transactions (COMMIT and ROLLBACK)

Transactions allow you to group multiple SQL statements. Either all succeed (COMMIT), or all fail and changes are undone (ROLLBACK). To use transactions, set AutoCommit => 0 when connecting or via $dbh->{AutoCommit} = 0;.

# Assuming $dbh connected with AutoCommit => 0, or set $dbh->{AutoCommit} = 0;

# If not set during connect, disable AutoCommit
# $dbh->{AutoCommit} = 0; # Or use $dbh->begin_work;

try {
    # Start transaction implicitly, or explicitly with $dbh->begin_work (some DBs)
    my $sth1 = $dbh->prepare('UPDATE EMPLOYEES SET INCOME = INCOME * 1.10 WHERE SEX = ?');
    $sth1->execute('F');

    my $sth2 = $dbh->prepare('INSERT INTO AUDIT_LOG (MESSAGE) VALUES (?)');
    $sth2->execute('Salary updated for females');

    $dbh->commit; # Finalize changes
    print "Transaction committed.\n";
} catch {
    warn "Transaction failed: $_\n  Rolling back changes...\n";
    $dbh->rollback; # Undo changes
    print "Transaction rolled back.\n";
};

# Re-enable AutoCommit if needed for subsequent non-transactional work
# $dbh->{AutoCommit} = 1;

The try/catch block requires Try::Tiny or a similar module for robust exception handling in older Perls. In modern Perl (5.34+), experimental try/catch syntax is available (use feature 'try'). Without it, use eval { ... }; if ($@) { ... }.

$dbh->begin_work is a more explicit way to start a transaction if supported by the driver and database, equivalent to turning AutoCommit off.

Disconnecting from the Database

Use $dbh->disconnect() to close the database connection.

$rc = $dbh->disconnect();
# warn $dbh->errstr if !$rc and $dbh->errstr; # Not usually needed with RaiseError

If AutoCommit is off and there are uncommitted changes, disconnect behavior is driver-dependent (some commit, some rollback). It’s best to explicitly commit or rollback before disconnecting.

Handling NULL Values

Perl’s undef is used to represent SQL NULL values when inserting or updating. When querying, undef is returned for NULL columns.

$sth->execute('New Employee', undef, 42, 'M', undef); # LAST_NAME and INCOME are NULL

Important: WHERE column = ? with undef bound to ? will NOT match rows where column IS NULL. Use WHERE column IS NULL or WHERE column IS NOT NULL explicitly.

my $age_filter = undef;
my $sql;
if (defined $age_filter) {
    $sql = 'SELECT * FROM EMPLOYEES WHERE AGE = ?';
    $sth = $dbh->prepare($sql);
    $sth->execute($age_filter);
} else {
    $sql = 'SELECT * FROM EMPLOYEES WHERE AGE IS NULL';
    $sth = $dbh->prepare($sql);
    $sth->execute(); # No bind value needed
}

DBI Utility Methods

DBI->available_drivers(): Returns a list of available DBD driver names.

my @drivers = DBI->available_drivers();
print "Available drivers: @drivers\n";

DBI->data_sources($driver): Returns a list of data sources (databases) available for a given driver.

# my @sources = DBI->data_sources('mysql'); # Might require driver-specific setup

$dbh->quote($value): Quotes a string literal for safe inclusion in an SQL statement. However, using placeholders ? is strongly preferred to prevent SQL injection.

# my $unsafe_name = "O'Malley";
# my $quoted_name = $dbh->quote($unsafe_name);
# my $sql = "SELECT * FROM users WHERE name = $quoted_name"; # Generally AVOID this pattern
# INSTEAD USE: $dbh->prepare('SELECT * FROM users WHERE name = ?')->execute($unsafe_name);

Error Handling Methods (on handles $h like $dbh or $sth)

$h->err(): Returns the native database error code. (Less needed if RaiseError is on).

$h->errstr(): Returns the native database error message. (Less needed if RaiseError is on).

$h->state(): Returns the SQLSTATE error code.

Tracing DBI Activity

DBI can generate detailed trace information for debugging. Call the trace method on DBI class, driver handles, or database/statement handles.

DBI->trace(2); # Set trace level 2 (SQL and connection info) for all handles
# $dbh->trace(1); # Trace level 1 for this specific database handle
# Levels typically range from 0 (off) to 4 or higher (very verbose).

Always use placeholders (?) instead of interpolating variables directly into SQL strings to prevent SQL injection vulnerabilities.