Perl DBI

In this tutorial, We'll see database access inside our Perl script. From Perl 5, database applications use DBI module to access database.

Database is accessed within a Perl script using DBI module. DBI (Database Independent Interface), provides an abstraction layer between Perl code and database.

The DBI is a database access module which provides variables, set of methods for a consistent database interface, independent of the actual database being used.

Perl DBI Architecture

DBI is independent of database and can be used with Oracle, MySQL, etc. DBI takes all SQL commands through API (Application Programming Interface) and forward them to the appropriate driver for the execution.

Perl DBI Connection

We are working with MySQL database. Start your MySQL server. Now complete the following steps:

• Create a database. Our database name is "perll".
• Create a table inside this database. We have created a table "users" with the fields 'name', 'course' and 'fee'.
• Perl module DBI should be installed properly.

After creating database successfully, connect this database with Perl file with following script.

Look at the above script,

dsn -> Database Source Name

dbh -> Database Handle Object

dsn contains the database being loaded. User is 'root' and we haven't set any password for our database.

dbh stores the database handle object call returns.

Database disconnect command is optional, it will be automatically called when $dbh is out of scope.

Perl INSERT into Database

Through INSERT operation, we will pass records into our database table "users". The following script allows insertion of single record in the database. You can insert multiple records, using same script.

Follow these steps:

• Use API prepare() function with SQL insert statement.
• Use API execute() function to select all results from the database.
• Use API finish() function to close the code.

Perl Inserting Bind values

Bind values are used when required values will be taken at run time. To insert data, we call $dbh->do method. Here we pass (?) as a place holder at place of actual data. This is followed by undef which is followed by the values containing data which will replace place holders.

Output:

Name = John, Course = PHP, Fee = 9000 
Name = nisha, Course = Java, Fee = 10000

Perl READ Database

READ record is used to fetch information from the database. Once we have inserted some records into our database, we can fetch those records using READ query.

Follow these steps:

• Use API prepare() function with SQL SELECT statement.
• Use API execute() function to select all results from the database.
• Use API fetchrow_array() function to fetch and print all results one by one.
• Use API finish() function to close the code.

Note: We have inserted some more values into our table to perform operations.

Output:

Name = John, Course = PHP, Fee = 9000 
Name = nisha, Course = Java, Fee = 10000
Name = Jia, Course = C++, Fee = 8000
Name = Anuj, Course = Java, Fee = 5000
Name = Vishal, Course = PHP, Fee = 8000
Name = Rash, Course = Hadoop, Fee = 4000

Perl UPDATE Database

With the help of UPDATE operation, you can edit the information present in the database table.

Here we'll update "course" column as PHP for all the students who have "Java" in their course.

Follow these steps:

• Use API prepare() function with SQL UPDATE statement with required condition.
• Use API execute() function to select all results from the database.
• Use API finish() function to close the code.

Output:

Name = John, Course = PHP, Fee = 9000 
Name = nisha, Course = PHP, Fee = 10000
Name = Jia, Course = C++, Fee = 8000
Name = Anuj, Course = PHP, Fee = 5000
Name = Vishal, Course = PHP, Fee = 8000
Name = Rash, Course = Hdoop, Fee = 4000

Look at the output, all the Java courses are updated to PHP.

Perl DELETE Database

DE#LETE operation is performed to delete some records from database. We are deleting all records from the table where fee is 9000.

Follow these steps:

• Use API prepare() function with SQL DELETE statement with required condition.
• Use API execute() function to select all results from the database.
• Use API finish() function to close the code.

Output:

Name = nisha, Course = PHP, Fee = 10000
Name = Jia, Course = C++, Fee = 8000
Name = Anuj, Course = PHP, Fee = 5000
Name = Vishal, Course = PHP, Fee = 8000
Name = Rash, Course = Hdoop, Fee = 4000

Look at the output, all the rows which have fee as 9000 are deleted from the SQL table.

Perl DBI Functions

Using do Statement

The do statement is a shortcut to perform CRUD operation in database. If do statement is succeeded it returns TRUE, if failed, it returns FALSE. Following is the example which shows do statement script.

Using COMMIT Operation

Once you have given the commit command, you can't retrieve back the changes you made. You can call commit command in the following way,

Using ROLLBACK Operation

If you want to revert the changes made during the operation, you can call rollback API.

Using AUTOMATIC Error Handling

By specifying RaiseError option, your errors will be handled automatically by DBI. Your program will be aborted on encountering an error rather than running a failure code. The value of RaiseError can be 1 or 0.

Perl Common Methods to All Handles

err

It returns native database engine error code from last driver method called which is typically an integer.

The DBI resets $h->err to undef before all DBI method calls, so this value has a short lifespan.

errstr

It returns native database engine error message from last DBI method called. This has the smae lifespan as err method. The returned value may contain multiple messages separated ny new line character.

To test errors, err method should be used. Because errstr method may return success or warning message for methods that have not failed.

trace

DBI trace method generate runtime tracing information, which can be used to track down the problems in your DBI program. Different values can be set to trace level.

rows

The rows method returns number of affected rows by previous SQL statement.