Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

perl

How to access a database using Perl

Khizar Hayat Saani

Perl is a dynamic programming language. It is often used to code the back-end for applications that use a database to store information.

To connect to a database, Perl uses the DBI (Database Independent Interface) module. The DBI module provides a layer of abstraction between our Perl code and the database.

To communicate with the database, DBI uses Structured Query Language otherwise knows as SQL.

Steps in Perl database access

Connecting

To connect to a database, the connect() method is invoked.

The function takes the following input parameters:

  1. Data source name: This is a string that contains the name of our module, the database engine, and the name of the database that we wish to connect to.

  2. Username: This string represents the username specific to the database we are trying to connect to

  3. Password: The password for the database.

my $db_engine = "mysql"; 
my $database = "SAMPLEDB";
my $dsn = "DBI:$db_engine:$database";
my $user = "sampleuser";
my $pass = "sample123";

my $dbcon = DBI->connect($dsn, $user, $pass) or die $DBI::errstr();

If the connection is successful, a Database Handle is returned. In case of an error $dbcon is set to undef and $DBI::errstr returns an error string.

Using queries

To prepare queries, the prepare() method is invoked. This is invoked via the connection variable.

An SQL query embedded within a string is passed as the input parameter to the prepare() method.

my $query = $dbcon->prepare("CREATE TABLE Employees(id INT PRIMARY KEY, name VARCHAR(10))");

$query->execute()

The above query is used to create a table in our database.

The execute() method executes the query in our database. The result table returned (if any) is then associated with our query variable.

The following functions can be used to access the row data of the result table returned.

  • fetchrow(): returns the next row of the table or undef in case of error.

  • fetchrow_array(): returns an array that contains the row data of the current row.

We can disconnect from the database using the disconnect() method of the query variable.

Code

The following code shows how a database is connected to and accessed in Perl.

# import the DBI module
use DBI;

# connecting
my $db_engine = "mysql"; 
my $database = "SAMPLEDB";
my $dsn = "DBI:$db_engine:database=$database";
my $userid = "sampleuser";
my $password = "sample123";

my $dbcon = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr();

# using queries

#--Creating table
my $query_ = $dbcon->prepare("CREATE TABLE Employees(id INT PRIMARY KEY, name VARCHAR(10)");
$query->execute()
#--Inserting values
my $query = $dbcon->prepare("INSERT INTO Employees VALUES(?, ?)");
my $id = 1;
my $name = "Alice";  
$query->execute($id, $name);
$query->execute($id + 1, "Bob");
#--Selecting all rows in Employees table
my $resultTable = $dbcon->prepare("SELECT * FROM Employees");
$resultTable->execute();
#--printing result
while (my @row = $resultTable->fetchrow_array()) 
{
    print "@row\n";
}
  
warn "Error", $resultTable->errstr( ), "\n"
if $resultTable->err();

# disconneting
$dbcon->disconnect()
  
exit;

Note: The above code assumes that a database by the name SAMPLEDB has been created on the database server.

RELATED TAGS

perl

CONTRIBUTOR

Khizar Hayat Saani
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring