Perl Database Connection

In this section, we will introduce how to connect to a database in Perl.

In Perl 5, we can use the DBI module to connect to a database.

DBI stands for Database Independent Interface, which is known as a database-independent interface in Chinese.

As the standard interface for communication with databases in Perl, DBI defines a series of methods, variables, and constants, providing a database-agnostic persistence layer.

DBI Structure

DBI is independent of specific database platforms and can be applied to databases such as Oracle, MySQL, or Informix.

In the diagram, DBI receives all SQL data sent from APIs (Application Programming Interface) and then distributes it to the corresponding drivers for execution, finally retrieving the data to return.

Variable Naming Conventions

The following sets up commonly used variable naming methods:

$dsn    Driver program object handle
$dbh    Database object handle
$sth    Statement or query object handle
$h      Generic handle ($dbh, $sth, or $drh), context-dependent
$rc     Boolean value (true or false) returned by the operation code
$rv     Integer value returned by the operation code
@ary    Array (list) of row values returned by the query
$rows   Number of rows returned by the operation code
$fh     File handle
undef   NULL value indicating undefined
\%attr  Reference to a hash of attributes passed to the method

Database Connection

Next, we will demonstrate how Perl operates on a database using MySQL as an example.

Here, we create a database named tutorialpro in MySQL, with a table named Websites. The table structure and data are shown in the following image:

Download the data table: https://static.tutorialpro.org/download/websites_perl.sql

Next, we use the following code to connect to the database:

Example

#!/usr/bin/perl -w

use strict;
use DBI;

my $host = "localhost";         # Host address
my $driver = "mysql";           # Interface type, default is localhost
my $database = "tutorialpro";   # Database
# Driver program object handle
my $dsn = "DBI:$driver:database=$database:$host";  
my $userid = "root";            # Database username
my $password = "123456";        # Database password

# Connect to the database
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM Websites");   # Prepare SQL statement
$sth->execute();    # Execute SQL operation

# Commented out section uses bind value operation
# $alexa = 20;
# my $sth = $dbh->prepare("SELECT name, url
#                        FROM Websites
#                        WHERE alexa > ?");
# $sth->execute( $alexa ) or die $DBI::errstr;

# Loop to output all data
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}

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

Insert Operation

Execution steps:

• Use the prepare() API to preprocess the SQL statement.
• Use the execute() API to execute the SQL statement.
• Use the finish() API to release the statement handle.
• Finally, if everything goes smoothly, commit the above operations.

  my $sth = $dbh->prepare("INSERT INTO Websites
                         (name, url, alexa, country )
                          values
  

  This is a Chinese to English translation, please provide the English translation for this text. Do not provide any explanations or text apart from the translation. Chinese: ('Twitter', 'https://twitter.com/', 10, 'USA')"); $sth->execute() or die $DBI::errstr; $sth->finish(); $dbh->commit or die $DBI::errstr;

The application can also bind output and input parameters. The following example executes an insert query by replacing the ? placeholders with variables:

my $name = "Twitter";
my $url = "https://twitter.com/";
my $alexa = 10;
my $country = "USA";
my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, country )
                        values
                       (?,?,?,?)");
$sth->execute($name,$url,$alexa, $country) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Update Operation

Execution steps:

• Use the prepare() API to preprocess the SQL statement.
• Use the execute() API to execute the SQL statement.
• Use the finish() API to release the statement handle.
• Finally, if everything goes smoothly, commit the above execution.

  my $sth = $dbh->prepare("UPDATE Websites
                          SET   alexa = alexa + 1 
                          WHERE country = 'CN'");
  $sth->execute() or die $DBI::errstr;
  print "Number of records updated: " + $sth->rows;
  $sth->finish();
  $dbh->commit or die $DBI::errstr;
  

The application can also bind output and input parameters. The following example executes an update query by replacing the ? placeholders with variables:

$name = 'tutorialpro.org';

my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = alexa + 1 
                        WHERE name = ?");
$sth->execute('$name') or die $DBI::errstr;
print "Number of records updated: " + $sth->rows;
$sth->finish();

Of course, we can also bind the values to be set. For example, modify the alexa for country CN to 1000:

$country = 'CN';
$alexa = 1000;
my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = ?
                        WHERE country = ?");
$sth->execute( $alexa, '$country') or die $DBI::errstr;
print "Number of records updated: " + $sth->rows;
$sth->finish();

Delete Data

Execution steps:

• Use the prepare() API to preprocess the SQL statement.
• Use the execute() API to execute the SQL statement.
• Use the finish() API to release the statement handle.
• Finally, if everything goes smoothly, commit the above execution.

The following data will delete all records from Websites where alexa is greater than 1000:

$alexa = 1000;
my $sth = $dbh->prepare("DELETE FROM Websites
                        WHERE alexa = ?");
$sth->execute( $alexa ) or die $DBI::errstr;
print "Number of records deleted: " + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Using the do Statement

The do statement can execute UPDATE, INSERT, or DELETE operations, and it is concise. It returns true if the execution is successful, and false if it fails. Here is an example:

$dbh->do('DELETE FROM Websites WHERE alexa>1000');

COMMIT Operation

The commit operation submits the transaction, completing the database operation:

$dbh->commit or die $dbh->errstr;

ROLLBACK Operation

If an error occurs during SQL execution, you can roll back the data, making no changes:

$dbh->rollback or die $dbh->errstr;

Like other languages, Perl DBI supports transaction processing for database operations, and it has two implementation methods:

1. Starting a Transaction Upon Connecting to the Database

$dbh = DBI->connect($dsn, $userid, $password, {AutoCommit => 0}) or die $DBI::errstr;

The above code sets AutoCommit to false upon connection, meaning that updates to the database will not be automatically written to the database. Instead, the program must use $dbh->commit to write the data to the database or $dbh->rollback to roll back the operations.

2. Starting a Transaction with $dbh->begin_work()

This method does not require setting AutoCommit to 0 upon connecting to the database.

You can perform multiple transaction operations with a single database connection without reconnecting for each transaction.

$rc  = $dbh->begin_work  or die $dbh->errstr;

#####################
## Perform some SQL operations here
#####################

$dbh->commit;    # Commit after successful operations
-----------------------------
$dbh->rollback;  # Roll back after failed operations

Disconnecting from the Database

To disconnect from the database, you can use the disconnect API:

$rc = $dbh->disconnect  or warn $dbh->errstr;