Database management using DBI in Perl

The DBI (Database Independent Interface) module in Perl provides a consistent interface for interacting with various databases. This tutorial will walk you through some basics of using DBI with a SQLite database, but you can adapt these principles to other databases as well (like MySQL, PostgreSQL, etc.) by simply changing the driver and connection details.

1. Installing necessary modules

You need to install the DBI module and the DBD::SQLite driver for SQLite:

cpan DBI DBD::SQLite

2. Connecting to a SQLite database

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=mydatabase.db","","") or die $DBI::errstr;

Here, we've connected to a SQLite database named mydatabase.db. If this file doesn't exist, SQLite will create it.

3. Creating a table

my $stmt = qq(CREATE TABLE COMPANY
             (ID INT PRIMARY KEY NOT NULL,
             NAME TEXT NOT NULL,
             AGE INT NOT NULL,
             ADDRESS CHAR(50),
             SALARY REAL););

my $rv = $dbh->do($stmt) or die $DBI::errstr;

4. Inserting data

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
           VALUES (1, 'Paul', 32, 'California', 20000.00));

$rv = $dbh->do($stmt) or die $DBI::errstr;

5. Selecting data

$stmt = qq(SELECT id, name, age, address, salary from COMPANY;);

my $sth = $dbh->prepare($stmt);
$sth->execute() or die $DBI::errstr;

while(my @row = $sth->fetchrow_array()) {
   print "ID = ". $row[0] . "\n";
   print "NAME = ". $row[1] ."\n";
   print "AGE =  ". $row[2] ."\n";
   print "ADDRESS =  ". $row[3] ."\n";
   print "SALARY =  ". $row[4] ."\n\n";
}

6. Updating data

$stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
$rv = $dbh->do($stmt) or die $DBI::errstr;

7. Deleting data

$stmt = qq(DELETE from COMPANY where ID=1;);
$rv = $dbh->do($stmt) or die $DBI::errstr;

8. Disconnecting

After all your operations are done, you should disconnect from the database:

$dbh->disconnect();

Some Best Practices:

Prepared Statements: Always use prepared statements when inserting data to avoid SQL injection attacks.
Error Checking: Always check for errors after performing database operations.
Disconnect: Always disconnect when done.

This is just a basic introduction to DBI in Perl. There's much more you can do, like transaction management, binding parameters, and more. If you're planning on working with databases frequently, be sure to dive deeper into the DBI documentation and relevant DBD driver documentation.

Connecting to databases with DBI in Perl:
- Description: DBI (Database Interface) is a Perl module that provides a database-independent interface to Perl scripts. It allows you to connect to various databases using a unified API.
- Example Code:
```
use DBI;

my $dsn = "DBI:Driver:database:hostname";
my $username = "your_username";
my $password = "your_password";

my $dbh = DBI->connect($dsn, $username, $password) or die "Unable to connect: $DBI::errstr";
```
Executing SQL queries with Perl DBI:
- Description: Once connected, you can execute SQL queries using the prepare and execute methods of the DBI module.
- Example Code:
```
my $sql = "SELECT * FROM your_table";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "Unable to execute query: $DBI::errstr";
```
Fetching and handling results in Perl DBI:
- Description: After executing a query, you can fetch and handle the results using methods like fetchrow_array, fetchrow_hashref, etc.
- Example Code:
```
while (my $row = $sth->fetchrow_arrayref) {
    print join(", ", @$row), "\n";
}
```

Parameterized queries in Perl DBI:

Description: Parameterized queries help prevent SQL injection by using placeholders for dynamic values.

Example Code:

my $name = "John";
my $age = 25;

my $sql = "SELECT * FROM your_table WHERE name = ? AND age = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($name, $age);

Error handling in Perl DBI:
- Description: DBI provides error handling through the $DBI::err and $DBI::errstr variables.
- Example Code:
```
$sth->execute() or die "Unable to execute query: $DBI::errstr";
```

Transaction management in Perl DBI:

Description: Transactions can be managed using commit and rollback methods to ensure data consistency.

Example Code:

eval {
    $dbh->begin_work;
    # Perform multiple queries here
    $dbh->commit;
};
if ($@) {
    $dbh->rollback;
    die "Transaction failed: $@";
}

Working with multiple databases in Perl:
- Description: You can connect to multiple databases by creating separate DBI handles for each database.
- Example Code:
```
my $dbh1 = DBI->connect($dsn1, $username1, $password1);
my $dbh2 = DBI->connect($dsn2, $username2, $password2);
```

Perl DBI and prepared statements:

Description: Prepared statements can be used for repeated queries with different parameters, improving performance.

Example Code:

my $sql = "INSERT INTO your_table (name, age) VALUES (?, ?)";
my $sth = $dbh->prepare($sql);

foreach my $data (@data_to_insert) {
    $sth->execute($data->{name}, $data->{age});
}

Database connection pooling with Perl DBI:
- Description: Connection pooling helps manage and reuse database connections efficiently.
- Example Code:
```
use DBI;
use DBIx::Connector;

my $connector = DBIx::Connector->new($dsn, $username, $password);
my $dbh = $connector->dbh;
```