Database Management in PERL - DBI

This session will teach you how to access Oracle Database and other databases using PERL.

• Oraperl Module to access Oracle. Check Oraperl Mnual

• DBI module to access databases in generic way. We will discuss this module in this chapter.

Starting from Perl 5 it has become very easy to write database applications using DBI. DBI stands for Database independent interface for Perl which means DBI provides an abstraction layer between the Perl code and the underlying database, allowing you to switch database implementations really easily.

The DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

Architecture of a DBI Application

DBI is independent of any database available in backend. You can use DBI whether you are working with Oracle, MySQL or Informix etc. This is clear from the following architure diagram.

Here DBI is responsible of taking all SQL commands through the API, or Application Programming Interface, and to dispatch them to the appropriate driver for actual execution. And finally DBI is responsible of taking results from the driver and giving back it to the calling scritp.

Notation and Conventions

Throughout this chapter following notations will be used and it is recommended that you should also follow the same convention.

$dsn Database source name $dbh Database handle object $sth Statement handle object $h Any of the handle types above ($dbh, $sth, or $drh) $rc General Return Code (boolean: true=ok, false=error) $rv General Return Value (typically an integer) @ary List of values returned from the database. $rows Number of rows processed (if available, else -1) $fh A filehandle undef NULL values are represented by undefined values in Perl /%attr Reference to a hash of attribute values passed to methods

Database Connection

Assuming we are going to work with MySQL database. Before connecting to a database make sure followings:

• You have created a database TESTDB.

• You have created TEST_TABLE in TESTDB.

• This table is having fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

• User ID "testuser" and password "test123" are set to access TESTDB

• Perl Module DBI is installed properly on your machine.

• You have gone through MySQL tutorial to understand MySQL Basics.

Following is the example of connecting with MySQL database "TESTDB"

#!/usr/bin/perl use DBI use strict; my $driver = "mysql"; my $database = "TESTDB"; my $dsn = "DBI:$driver:database=$database"; my $userid = "testuser"; my $password = "test123"; my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;

If a connection is established with the datasource then a Database Handle is returned and saved into $dbh for further use otherwise $dbh is set to undef value and $DBI::errstr returns an error string.

INSERT Operation

INSERT operation is required when you want to create your records into TEST_TABLE. So once our database connection is established, we are ready to create records into TEST_TABLE. Following is the procedure to create single record into TEST_TABLE. You can create many records in similar fashion.

Record creation takes following steps

• Prearing SQL statement with INSERT statement. This will be done using prepare() API.

• Executing SQL query to select all the results from the database. This will be done using execute() API.

• Releasing Stattement handle. This will be done using finish() API

• If everything goes fine then commit this operation otherwise you can rollback complete transaction. Commit and Rollback are explained in next sections.

my $sth = $dbh->prepare("INSERT INTO TEST_TABLE