Perl | Database management using DBI

Prerequisites:

• Introduction to Perl
• Database Systems

Creating database programs is one of the most common uses of Perl. Using Perl, we can create robust web applications along with a database to manage all the data. It provides excellent support for interfacing and a broad range of database formats. For connecting to and querying a database, Perl provides a module called DBI. DBI is a database interface for communicating with database servers that use Structured Query Language (SQL) to get data. Accessing a Database in Perl generally takes two steps. The DBI module provides an API for database access. A program uses the functions of DBI to manipulate the database. The second stage of database access from Perl is a database driver (DBD) module. Each different database system requires its own driver. This approach allows a Perl database application program to be relatively independent of the particular database it will access. Installation: To Install DBI module, open terminal and type the following command and press Enter:

perl -MCPAN -e 'install Bundle::DBI'

This will automatically download and Install the driver for the DBI module to provide database connectivity with Perl. As the name suggests, DBI provides an independent interface for Perl programs. This means that the Perl code doesn't depend on the database running in the backend. DBI module provides abstraction, i.e, we can write our code without worrying about the database that runs in the back-end. To import the functions of the Database Independent Interface module, we need to import or include the module with the help of "use" pragma. The use DBI pragma allows us to use DBI module to manipulate the database that we are connecting to.

Syntax: use DBI;

Connecting to the database: The connect() method is used to connect to the specified database. It takes three arguments:

1. A string of three values separated by a ':' in this example, it is "DBI:mysql:test". The first value specifies that we are using DBI. the second value specifies the database engine, which, in this case, is MySQL. the third value specifies the name of the database that you want to connect to.
2. The next argument to the connect() method is the username. In this case, user is 'root'.
3. The last argument is the password of your local system. In this example, it is 'password'

Syntax:

my $dbh = DBI->connect ("DBI:mysql:test", "root", "password") or die "Can't connect: " . DBI->errstr();

The "or die" statement terminates the program if it was unable to establish a connection with the database, with an error message. The errstr() method returns a string that contains any errors encountered when connecting to the database. Preparing Queries: The prepare() method takes in one parameter, the SQL query to be executed. The SQL query is taken in the form of a string that contains the SQL statement. This SQL statement is the same as the SQL statements that you would execute in MySQL. It returns an object called a statement handle that can be used to execute queries. Syntax:

my $sth = $dbh->prepare( " CREATE TABLE emp( id INT PRIMARY KEY, name VARCHAR(10), salary INT, ");

Now, the query is prepared for execution. Note that in the above query, we are creating a table with id, name and salary columns. Executing the queries: The execute() method executes the query written in the prepare() method. It does not take any arguments. It is called using the statement handle object created when the 'prepare' statement is executed. Syntax:

$sth->execute();

Fetching Values from the result: The fetchrow() method is used to retrieve the next row of data from the result of the executed query. If a select query is executed, then the fetchrow() method fetches the next row from the result. It returns one row from the result which can be assigned to variables. When used in a while loop, we can fetch and display all the rows in the database using the fetchrow() method. Syntax:

($id, $name, $salary) = $sth->fetchrow();

The values of each column are stored in the three variables. The fetchrow_array() function returns an array that contains the row from the result Syntax:

my @row = $sth->fetchrow_array( )

Disconnecting: Once all the queries are executed, we need to disconnect the connection. This is done by the use of disconnect() function. Doing so allows the Perl script to properly terminate the connection. Not disconnecting from the database will not generate any errors. It is generally a good practice to do so. Syntax:

$dbh->disconnect();

Creating the database in MySQL: MySQL must be installed in your system and basic knowledge of MySQL is required.

• Log in to your MySql server
• Create a Database called "test". We will connect to this database so make sure that the name is "test"
• Make sure that this database has no tables as we will be creating a table called "emp" and insert values into this table

Putting it all together: Once you have created the database in MySQL, we can access that database in Perl. We first create an emp table in the database called test with the schema: (id INTEGER PRIMARY KEY, name VARCHAR(10), salary INT, dept INT). Once the table is created without any errors, we insert values into the table. Once the values are inserted, we can query the table to select all the rows and display them to the user using the fetchrow() function. Example: PERL

#!/usr/bin/perl -w
use DBI;

# definition of variables

# name of the database. In this case,
# the name of the database in my local
# system is test.

# user in this case is root
$user = "root";
# this is the password for root
$password = "password"; 

# connect to MySQL database
my $dbh = DBI->connect ("DBI:mysql:test",
                        $user,
                        $password) 
                        or die "Can't connect to database: $DBI::errstr\n";

print "connected to the database\n";

# the test database contains a table called emp 
# the schema : (id INTEGER PRIMARY KEY, 
#               name VARCHAR(10), salary INT, dept INT)
# let us first insert some values

# prepare the query to 
# create the emp table
my $sth = $dbh->prepare("CREATE TABLE emp(id INT PRIMARY KEY, 
                                         name VARCHAR(10), 
                                         salary INT, dept INT)");

# execute the query
# now, the table is created
$sth->execute();

# prepare the query
my $sth = $dbh->prepare("INSERT INTO emp
                        VALUES(?, ?, ?, ?)");

# define the variables to be inserted
# into the table
my $id = 1;
my $name = "adith";
my $salary = 1000;
my $dept = 2;

# insert these values into the emp table.
$sth->execute($id, $name, $salary, $dept);

# insert some more rows into the table.
$sth->execute($id + 1, $name, 
              $salary + 100, $dept - 1);

# insert more rows
$sth->execute($id + 2, "Tyrion",
              $salary + 1000, $dept + 1);

print "Successfully inserted values into the table\n";

# now, select all the rows from the table.
my $sth = $dbh->prepare("SELECT * FROM emp");

# execute the query
$sth->execute();

# Retrieve the results of a row of data and print
print "\tQuery results:\n================================================\n";

# fetch the contents of the table 
# row by row using fetchrow_array() function
while (my @row = $sth->fetchrow_array()) 
{
    print "@row\n";
}

# if the function cannot be execute, show a warning.
warn "Problem in retrieving results", $sth->errstr( ), "\n"
if $sth->err();

print "\n";

# select particular columns. 

# prepare the query
my $sth = $dbh->prepare("SELECT name, salary FROM emp");

# execute the query
$sth->execute( );

# Retrieve the results of a row of data and print
print "\tQuery results:\n================================================\n";

while(($name, $sal) = $sth->fetchrow_array()) 
{
    print "Name: $name, salary: $sal\n";
}
warn "Problem in retrieving results", $sth->errstr( ), "\n"
if $sth->err( );

# end of program
exit;

Output :