Perl – DBI(Database Independent) Module | Set – 2

Perl allows the handling of Databases with the help of Perl Scripts. These scripts run with the help of a module known as DBI(Database Independent Interface) module. DBI module provides an API to interact with many databases such as MySQL, Oracle, etc. This module provides a set of variables and methods that provide interaction with a database interface and need not access the original database.

Creating a Table

To create a table in Perl, first we have to create a database in SQL. To do so, we will execute the following statement in SQL prompt:

CREATE DATABASE EMP_DB;

Now, a database by the name EMP_DB will web created. We will use this database to store record of employees in our database.

Now, we will connect to this database by using the following command –

my $dbh = DBI->connect("dbi:$driver:$dsn", $username, $passwd, { AutoCommit => 1 })

Now, our connection to the database will be established and we will use the following statement to create a table, EMP_DEATAILS, to hold the record of employee’s details.  



my $sth = $dbh->prepare(“CREATE TABLE EMP_DETAILS ( ID INT PRIMARY KEY, EMP_NAME VARCHAR(50), AGE INT, SALARY INT)”);
$sth->execute();

The full program for reference is given below :

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl -w
use strict;
use DBI;
my $driver = "mysql";
my $dsn = "database=EMP_DB";
my $username = "root";
my $passwd = "";
my $dbh = DBI->connect("dbi:$driver:$dsn"
               $username, $passwd,
               { AutoCommit => 1 }) or 
               die "Failed to connect to database : $DBI::errstr";
my $sth = $dbh->prepare("CREATE TABLE EMP_DETAILS ( 
                           ID INT PRIMARY KEY, 
                           EMP_NAME VARCHAR(50),
                           AGE INT, SALARY INT)");
  
$sth->execute();

chevron_right


Output:

Create1

Inserting in Database

Now that we have created EMP_DETAILS table in EMP_DB database, lets insert some values into the table by using the following statements :

filter_none

edit
close

play_arrow

link
brightness_4
code

#$dbh->do("INSERT INTO EMP_DETAILS (ID, EMP_NAME, AGE, SALARY)  
VALUES (71, 'CHINMAY', 23, 10000)")  
or die "Failed to insert :  $DBI::errstr";
  
#$dbh->do("INSERT INTO EMP_DETAILS (ID, EMP_NAME, AGE, SALARY)  
VALUES (73, 'SANSKRUTI', 23, 15000)")  
or die "Failed to insert :  $DBI::errstr";
  
$dbh->do("INSERT INTO EMP_DETAILS (ID, EMP_NAME, AGE, SALARY)  
VALUES (150, 'DEREK', 37, 20000)")  
or die "Failed to insert :  $DBI::errstr";
  
$dbh->do("INSERT INTO EMP_DETAILS (ID, EMP_NAME, AGE, SALARY)  
VALUES (200, 'ROSS', 30, 24250)")  
or die "Failed to insert :  $DBI::errstr";

chevron_right


Output:

Insert1

Inserting Bind Values

Consider the below given program. In the program, we have used question marks(?) at some places. These question marks are known as placeholders. By using placeholders, the database driver can pre-process the query before executing it to the SQL prompt. This process is known as binding of data. Binding helps us in increasing the code security, which helps to prevent SQL injection attacks. 



filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl -w
use strict;
use DBI;
my $driver = "mysql";
my $dsn = "database=EMP_DB";
my $username = "root";
my $passwd = "";
my $dbh = DBI->connect("dbi:$driver:$dsn"
               $username, $passwd
               { AutoCommit => 1 }) or 
               die "Failed to connect to database : $DBI::errstr";
   
# Inserting Bind Values
print "Enter Employee ID - ";
my $id = <>;
chomp($id);
   
print "Enter Employee Name - ";
my $name = <>;
chomp($name);
   
print "Enter Employee Age - ";
my $age = <>;
chomp($age);
   
print "Enter Employee Salary - ";
my $salary = <>;
chomp($salary);
   
my $sth = $dbh->prepare("INSERT INTO EMP_DETAILS (
                            ID, EMP_NAME, AGE, SALARY)
                            VALUES (?, ?, ?, ?)");
                              
$sth->execute($id, $name, $age, $salary) or 
      die "Failed to insert : $DBI::errstr";

chevron_right


Output:


Bind2

Reading Database values

Our database is now populated with some values. Now we need to read them back to the user. To do so we will use while loop to read back data to the user.

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl -w
use strict;
use DBI;
my $driver = "mysql";
my $dsn = "database=EMP_DB";
my $username = "root";
my $passwd = "";
my $dbh = DBI->connect("dbi:$driver:$dsn"
               $username, $passwd
               { AutoCommit => 1 }) or 
               die "Failed to connect to database : $DBI::errstr";
   
my $sth = $dbh->prepare("SELECT EMP_NAME, ID FROM EMP_DETAILS WHERE ID<100");
   
$sth->execute() or die "Failed to select rows : $DBI::errstr";
   
my $rows = $sth->rows;
print "Number of rows updated : $rows \n";
   
while (my @emp_data = $sth->fetchrow_array()) 
{
    my ($name, $id ) = @emp_data;
    print "Employee Name = $name, Employee ID = $id \n";
}
$sth->finish();

chevron_right


Output:

Reading2pref

Updating database values

To update any value of the table, we can use SQL UPDATE command in the following way :

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl -w
use strict;
use DBI;
my $driver = "mysql";
my $dsn = "database=EMP_DB";
my $username = "root";
my $passwd = "";
my $dbh = DBI->connect("dbi:$driver:$dsn"
               $username, $passwd
               { AutoCommit => 1 }) or 
               die "Failed to connect to database : $DBI::errstr";
   
$dbh->do("UPDATE EMP_DETAILS SET ID=250 WHERE ID=150")
      or die "Failed to update : $DBI::errstr";

chevron_right


Output:

Original Table:
Bind2

Table after Data Updation:
Update1

Deleting database values

To delete a particular row from the table, we can use SQL DELETE statement in the following way :

filter_none

edit
close

play_arrow

link
brightness_4
code

#!/usr/bin/perl -w
use strict;
use DBI;
my $driver = "mysql";
my $dsn = "database=EMP_DB";
my $username = "root";
my $passwd = "";
my $dbh = DBI->connect("dbi:$driver:$dsn"
            $username, $passwd, { AutoCommit => 1 }) or 
            die "Failed to connect to database : $DBI::errstr";
  
$dbh->do("DELETE FROM EMP_DETAILS WHERE ID=250") or 
     die "Failed to delete : $DBI::errstr";
   
$dbh->do("DELETE FROM EMP_DETAILS WHERE EMP_NAME='ROSS'") or 
     die "Failed to delete : $DBI::errstr";

chevron_right


Output:

Delete1




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.