Perl | Database management using DBI
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:
connect() method is used to connect to the specified database. It takes three arguments:
- 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.
- The next argument to the connect() method is the username. In this case, user is ‘root’.
- The last argument is the password of your local system. In this example, it is ‘password’
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.
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.
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:
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.
Fetching Values from the result:
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
($id, $name, $salary) = $sth->fetchrow();
The values of each column are stored in the three variables.
fetchrow_array() function returns an array that contains the row from the result
my @row = $sth->fetchrow_array( )
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.
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