Handling Databases With Perl Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Databases store data in the database files. The DBI module in Perl enables us to work with the database. We can access the data stored in the databases and retrieve valuable information. Structured Query Language is the language used to communicate with the database.

In this chapter we will learn how to connect and disconnect from a database. In addition, we will learn how to work with the data in the database using SQL queries.

Working with Databases

We use a telephone diary to store the telephone numbers of our friends and relatives. The telephone diary enables us to quickly retrieve a telephone number when required. In the same way we use databases to store data. A table stores the related data in the database. The table consists of rows and columns. We can depict the rows and columns of a table using a grid. The intersection of a row and column in the table is called a field. A field in the table contains the stored data value. The row represents the related data about the object known as record. The column is a collection of fields storing similar details. We can represent a table storing student details, as shown in Figure 15.1.

Column

Row

Figure 15.1: Student Table

The database enables us to store and retrieve the required data quickly and efficiently from the database. We use databases such as like MySQL, MSSQL, Oracle, Sybase, etc to store data. We can store the data entered in a form in these databases. We can communicate with database using the DBI module in Perl.

Using DBI Module

We access the data stored in a database using a database driver. A database has its own driver for accessing the data from the database. The driver is the language that is used to communicate with the database. The DBI module understands the language used by these drivers. The DBI module is placed between the Perl script file and the database drivers. It accepts the commands from the script file and forwards the same to the driver. The driver retrieves the required data and returns it to the DBI. The DBI then forwards the retrieved data to the script that displays the required information. Figure 15.2 displays the interaction between the script file, the DBI module, and the database driver. The database driver is also called the database handler.

Figure 15.2: Interacting with the DBI Module

The DBI module is preinstalled in Perl. If it is not already installed required file for installing the module must be downloaded from the CPAN website.

To download the DBI module:

Open the Mozilla browser.

Browse to the http://search.cpan.org Web page.

Enter DBI in the text box.

Select Modules from the drop down list box.

Click the CPAN Search button. The page displaying the DBI modules appears.

Download the DBI module.

To install the DBI module:

Browse to the directory where the file is downloaded.

Enter the following command at the command prompt to extract the compressed file:

gzip -dc downloadedfilename.tar.gz

tar -xof downloadedfilename.tar

Enter the following command at the command prompt to compile the files:

perl Makefile.pl

make

make test

Enter the following command at the command prompt to install the DBI:

make install

Note: Do not install the DBI module if it is already installed.

The DBI module interacts with the database drivers to retrieve data from the database. The required database driver must be installed on the system to communicate with the database. The required database driver can be downloaded and installed form CPAN. Some database drivers available are displayed in Table 15.1:

Driver

Description

DBD::ADO

Driver interface for ADO

DBD::Informix

Driver interface for IBM Informix Database

DBD::ODBC

Driver interface for ODBC

DBD::Oracle

Driver interface for Oracle

DBD::Sybase

Driver interface for Sybase

DBD::mSQL

Driver interface for Msql

DBD::mysql

Driver interface for MySQL

Table 15.1: Database Drivers

For example, to view the list of installed drivers, enter the following:

#!/usr/bin/perl

#Directive to include the module

use DBI;

print ("Installed DBI drivers:\n");

#Retrieving the installed driver list

@mydrivers = DBI->available_drivers();

#Displaying the drivers

foreach $item (@mydrivers)

{

print ("$item\n");

}

The above example displays a list of the installed drivers, as shown in Figure 15.3.

Figure 15.3: Displaying Installed Drivers

Connecting to the Database

We use the DBI module for connecting to the required database. We can interact with the database using queries after a connection is established. The connection creates a link to the database using which the query requests are passed and the data from the tables are retrieved.

The syntax for connecting to the database is:

DBI->connect("dbi:driver:database_name", "username", "password");

Where;

driver - Specifies the database driver name

database_name - Specifies the name of the database

username - Specifies the username for the database

password - Specifies the password to connect to the database

For example to connect to the STUDENT_DET database created using MySQL, enter the following:

#Include the module

use DBI;

$myhandle = new DBI;

#Connecting to the database

$myhandle = DBI->connect("dbi:mysql:STUDENT_DET","martin","");

Disconnecting from the Database

We must terminate the database connection after working with the database. The statement to disconnect the database must be called after all the database transactions are complete. We must reconnect to work with the database after disconnecting.

The syntax for disconnecting from the database is:

disconnect;

For example, to disconnect from the connected database, enter the following:

$myhandle->disconnect;

Where, myhandle specifies the database connection.

Working with MySQL

MySQL is a Database Management System software used to store data in tables. It is Open Source software that uses can freely modify and distributed. We use Structured Query Language to work with the data stored in the tables. MySQL requires the DBD::MySQL database driver for Perl to interact with the database.

Using Queries in MySQL

Structure Query Language also known as SQL is the language that is used to interact with the databases. Using queries we can retrieve, add, delete and modify the records from MySQL databases. We can also modify the structure of the database using SQL.

INSERT Query

The INSERT query is used to add records in the database. It creates a new record entry in the table.

The syntax for the INSERT query is:

INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, ...);

Where;

table_name - Specifies the table that stores the records

value - Specifies the record data that must be added

For example, to add a record using the INSERT query, enter the following:

INSERT INTO STUD_NAME VALUES('NOEL', 'NEW YORK');

SELECT Query

The SELECT query is used to retrieve records from the tables. The SELECT query also makes use of the optional WHERE clause, using which we can retrieve the required records based on some criteria.

The syntax for the SELECT query is:

SELECT FIELDS FROM TABLE_NAME WHERE FIELD_NAME = DATA;

Where;

fields - Specifies the fields that must be retrieved. To retrieve all fields we use the * symbol.

table_name - Specifies the table that stores the records

field_name - Specifies the field to be check for the selected record

data - Specifies the record having the data to be retrieved

For example, to retrieve records using the SELECT query, enter the following:

To retrieve all the records from the table STUD_NAME, enter the following:

SELECT * FROM STUD_NAME;

To retrieve the records with the name specified as Karen, enter the following:

SELECT * FROM STUD_NAME WHERE NAME = 'KAREN';

UPDATE Query

The UPDATE query is used to modify the records from the database. The UPDATE query also makes use of the optional WHERE clause, using which we can modify required records based on some criteria.

The syntax for the UPDATE query is:

UPDATE TABLE_NAME SET COLUMN_NAME = VALUE WHERE FIELD_NAME = DATA;

Where;

table_name - Specifies the table for updating the records

column_name - Specifies the fields that must be modified

value - Specifies the data the field must be updated with

field_name - Specifies the field to be check for the selected record

data - Specifies the record having the data to be updated

For example, to change the city to London where for Karen, enter the following:

UPDATE STUD_NAME SET CITY = 'LONDON' WHERE NAME = 'KAREN';

DELETE Query

The DELETE query is used to delete records from the database. The DELETE query also makes use of the optional WHERE clause, using which we can delete records based on some criteria.

Note: The DELETE query without the WHERE clause deletes all the records from the table.

The syntax for the DELETE query is:

DELETE FROM TABLE_NAME WHERE FIELD_NAME = DATA;

Where;

table_name - Specifies the table that stores the data

field_name - Specifies the field to be check for the deleting the record

data - Specifies the record having this data that must be deleted

For example, to delete the records with the city name as Tokyo, enter the following:

DELETE FROM STUD_NAME WHERE CITY = 'TOKYO';

Using the DBI Module Functions

We can work with the data from the MySQL database using the DBI module and SQL queries. To execute the queries we must connect to the database, prepare the query, execute the query, and close the database.

Preparing the SQL query

The prepare function enables us to prepare the SQL query for execution. We must specify the query that must be executed in the prepare function. We can store the prepared query in a variable for execution.

The syntax for the prepare function is:

$variable = DBI->prepare(sql_query);

Where;

variable - Specifies a variable name to store the prepared query

sql_query - Specifies an SQL query to execute

Some examples of using the prepare function are:

To prepare a query to retrieve all the records from the table, enter the following:

$fch_rec = DBI->prepare("SELECT * FROM STUD_NAME");

To prepare a query to add a record in the table, enter the following:

$add_rec = DBI->prepare("INSERT INTO STUD_NAME VALUES('JEREMY', 'PARIS')");

Executing the SQL query

The execute function enables us to run the prepared SQL query. We must specify the variable having the prepared SQL query in the execute function to run the query. The execute function returns a recordset containing the records retrieved using the Select query. It performs the required modifications in the table specified using the Insert, Update, and Delete queries.

The syntax for the execute function is:

$variable = DBI->execute($prepared_variable);

Where;

variable - Specifies a variable name to store the prepared query

prepared_variable - Specifies the variable having the prepared SQL query

Some examples of using the execute function are:

To execute the query to retrieve all the records from the table, enter the following:

$exec_rec = DBI->execute($fch_rec);

To prepare a query to add a record in the table, enter the following:

DBI->execute($fch_rec);

Viewing Retrieved Records

The fetchrow_array function enables us to access and browse through the rows retrieved in the recordset. We can display the details from the recordset by specifying the index number of the array. The first index number in the array corresponds to the first column in the table.

The syntax to fetch the retrieved rows is:

@rec_name = $execute_var->fetchrow_array;

Where;

rec_name - Specifies a variable name to store the retrieved rows

execute_var - Specifies the variable used to execute the query

For example, to fetch the rows retrieved using the select query, enter the following:

@view_det = $exec_rec->fetchrow_array;

To view the retrieved details we must use a while loop.

For example, to view the retrieved details from a table having three columns, enter the following:

while( @get_row = $sth->fetchrow_array )

{

print ("$get_row[0]");

print ("$get_row[1]");

print ("$get_row[2]");

print ("\n");

}

We will create a Web page that accepts a user name. The Web page returns the details entered to a Perl script file. The Perl script file uses the DBI module to retrieve the users details from the MySQL database having and display it on a Web page.

To create the HTML page for accepting user feedback:

Browse to the following directory in the command prompt.

/var/www/html

Enter the following in the command prompt to create a new file named Details.html:

vi Details.html

Enter the following code in the file:

<html>

<head>

<title>Shopping Kingdom</title>

</head>

<body><h1>User Details</h1><BR>

<form action="process_det.pl" method=get>

Enter you name : <input type="text" name="uname">

<input type="submit" value="Submit">

or

<input type="reset" value="Reset">

</form>

</body>

</html>

The code will display a text box for entering the users name.

Save the file.

We must create a database in MySQL with the name SHOP_DET. The database must have a table named USER_DET. The tables must have three columns that stores the user name, telephone number and city. To create a Perl script that will display the user details from the database:

Browse to the following directory in the command prompt.

/var/www/html

Enter the following in the command prompt to create a new file named disp_det.pl:

vi disp_det.pl

Enter the following code in the file:

#!/usr/bin/perl

use CGI;

use DBI;

$m = new CGI;

my $name1 = $m->param('uname');;

print ("Content-type:text/html\n\n");

print ("<html><head><title>User details");

print ("</title></head><body><h1>");

print ("Details for $name1:</h1><BR>");

$dbh = DBI->connect("dbi:mysql:SHOP_DET")

or die("Couldn't connect");

$sth = $dbh->prepare("select * from USER_DET WHERE USER_NAME = '" . "$name1" ."'")or die("Couldn't prepare");

$sth->execute();

if($sth->rows == 0)

{

print ("Details not found.");

}

else

{

print ("<table>\n");

while( @get_row = $sth->fetchrow_array )

{

print ("<tr><td>" . "$get_row[0]");

print ("</td><td>" . "$get_row[1]");

print ("</td><td>" . "$get_row[2]");

print ("</td></tr>\n");

}

print ("</table>\n");

}

print ("</body></html>\n");

$dbh->disconnect;

Save the file.

Enter the following at the command prompt to assign execute permissions for the file:

chmod 755 disp_det.pl

To view the output of the Perl file:

Open the Mozilla Web browser.

Enter the following address of the HTML file in the address bar of the browser:

http://localhost/Details.html

Enter a name in the text box such as Martin.

Click the Submit button. The data entered is passed to the Perl script, which displays the details retrieved from the MySQL database.

Summary

Databases store data.

A table stores the related data in the database.

We access the data stored in a database using a database driver.

The DBI module understands the language used by the database drivers.

We can interact with the database using queries after a connection is established.

We must terminate the database connection after working with the database.

MySQL requires the DBD::MySQL database driver for Perl to interact with the database.

SQL is the language that is used to interact with the databases.

The INSERT query is used to add records in the database.

The SELECT query is used to retrieve records from the tables.

The UPDATE query is used to modify the records from the database.

The DELETE query is used to delete records from the database.

The prepare function enables us to prepare the SQL query for execution.

The execute function enables us to run the prepared SQL query.

The fetchrow_array function enables us to access and browse through the rows retrieved in the recordset.

Check Your Progress

A ___________in the table contains the stored data value.

row

column

field

database

We access the data stored in a database using a database ________________.

driver

function

table

file

The ______________ driver is used to access the Msql database.

DBD::mSQL

DBI::mSQL

mSQL::DBD

mSQL::DBI

The _____________function retrieves the list of installed modules.

driver_list

installed_drivers

drivers

available_drivers

The ______________________ function is used to terminate the database connection.

terminate

end

disconnect

destroy

The ________________ query inserts data in the table.

INSERT

ADD

NEW

ADDNEW

The DELETE query without the _________________clause deletes all the records from the table.

LIMIT

WHERE

SELECT

CURRENT

The _____________function is used to run the query.

process

run

end

execute

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.