Perl - Socket Programming

Perl - Socket Programming

What is a socket?

Socket is a Berkeley UNIX mechanism of creating a virtual duplex connection between different processes. This was later ported on to every known OS enabling communication between systems across geographical location running on different OS software. If not for the socket, most of the network communication between systems would never ever have happened.

Taking a closer look; a typical computer system on a network receives and sends information as desired by the various applications running on it. This information is routed to the system, since a unique IP address is designated to it. On the system, this information is given to the relevant applications which listen on different ports. For example an internet browser listens on port 80 for information received from the web server. Also we can write our custom applications which may listen and send/receive information on a specific port number.

For now, let's sum up that a socket is an IP address and a port, enabling connection to send and recieve data over a network.

To explain above mentioned socket concept we will take an example of Client - Server Programming using Perl. To complete a client server architecture we would have to go through the following steps:

To create a server

·        Create a socket using socket call.

·        Bind the socket to a port address using bind call.

·        Listen to the socket at the port address using listen call.

·        Accept client connections using accept call.

To create a client

·        Create a socket with socket call.

·        Connect (the socket) to the server using connect call.

Following diagram shows complete sequence of the calls used by Client and Server to communicate with each other:

Server Side Socket Calls

The socket() call

The socket() call is the first call in establishing a network connection is creating a socket. This call has following syntax:

socket( SOCKET, DOMAIN, TYPE, PROTOCOL );

Above call creates a SOCKET and other three arguments are integers which should have the following values for TCP/IP connections.

·        DOMAIN should be PF_INET. It's probable 2 on your computer.

·        TYPE should be SOCK_STREAM for TCP/IP connection.

·        PROTOCOL should be (getprotobyname('tcp'))[2]. It is the particular protocol such as TCP to be spoken over the socket.

So socket function call issued by the server will be something like this:

use Socket     # This defines PF_INET and SOCK_STREAM

socket(SOCKET,PF_INET,SOCK_STREAM,(getprotobyname('tcp'))[2]);

The bind() call

The sockets created by socket() call are useless until they are bound to a hostname and a port number. Server uses following bind() function to specify the port at which they will be accepting connections from the clients.

bind( SOCKET, ADDRESS );

Here SOCKET is the descriptor returned by socket() call and ADDRESS is a socket address ( for TCP/IP ) containing three elements:

·        The address family (For TCP/IP, that's AF_INET, probably 2 on your system )

·        The port number ( for example 21 )

·        The internet address of the computer ( for example 10.12.12.168 )

As the bind() is used by a server which does not need to know its own address so the argument list looks like this:

use Socket        # This defines PF_INET and SOCK_STREAM

$port = 12345;    # The unique port used by the sever to listen requests

$server_ip_address = "10.12.12.168";

bind( SOCKET, pack_sockaddr_in($port, inet_aton($server_ip_address)))

   or die "Can't bind to port $port! \n";

The or die clause is very important because if a server dies without outstanding connections the port won't be immediately reusable unless you use the option SO_REUSEADDR using setsockopt() function. Here pack_sockaddr_in() function is being used to pack the Port and IP address into binary format.

The listen() call

If this is a server program then it is required to issue a call to listen() on the specified port to listen ie wait for the incoming requests. This call has following syntax:

listen( SOCKET, QUEUESIZE );

The above call uses SOCKET descriptor returned by socket() call and QUEUESIZE is the maximum number of outstanding connection request allowed simultaneously.

The accept() call

If this is a server program then it is required to issue a call to access() function to accept the incoming connections. This call has following syntax:

accept( NEW_SOCKET, SOCKET );

The accept call receive SOCKET descriptor returned by socket() function and upon successful completion, a new socket descriptor NEW_SOCKET is returned for all future communication between client and server. If access() call fails then it returns FLASE which is defined in Socket module which we have used initially.

Generally, accept() is used in an infinite loop. As soon as one connection arrives the server either creates a child process to deal with it or serves it himself and then goes back to listen for more connections.

while(1) {

   accept( NEW_SOCKET, SOCKT );

   .......

}

Now all the calls related to server are over and let us see a call which will be required by the client

Client Side Socket Calls

The connect() call

If you are going to prepare client program then first you will use socket() call to create a socket and then you would have to use connect() call to connect to the server. You already have seen socket() call syntax and it will remain similar to server socket() call, but here is the syntax for connect() call:

connect( SOCKET, ADDRESS );

Here SCOKET is the socket descriptor returned by socket() call issued by the client and ADDRESS is a socket address similar to bind call, except that it contains the IP address of the remote server.

$port = 21;    # For example, the ftp port

$server_ip_address = "10.12.12.168";

connect( SOCKET, pack_sockaddr_in($port, inet_aton($server_ip_address)))

   or die "Can't connect to port $port! \n";

If you connect to the server successfully then you can start sending your commands to the server using SOCKET descriptor, otherwise your client will come out by giving an error message.

Client - Server Example

Following is a Perl code to implement a simple client-server program using Perl socket. Here server listens for incoming requests and once connection is established, it simply replies Smile from the server. The client reads that message and print on the screen. Let's see how it has been done, assuming we have our server and client on the same machine.

Script to Create a Server

#!/usr/bin/perl -w

# Filename : server.pl

use strict;

use Socket;

# use port 7890 as default

my $port = shift || 7890;

my $proto = getprotobyname('tcp');

my $server = "localhost";  # Host IP running the server

# create a socket, make it reusable

socket(SOCKET, PF_INET, SOCK_STREAM, $proto)

   or die "Can't open socket $!\n";

setsockopt(SOCKET, SOL_SOCKET, SO_REUSEADDR, 1)

   or die "Can't set socket option to SO_REUSEADDR $!\n";

# bind to a port, then listen

bind( SOCKET, pack_sockaddr_in($port, inet_aton($server)))

   or die "Can't bind to port $port! \n";

listen(SOCKET, 5) or die "listen: $!";

print "SERVER started on port $port\n";

# accepting a connection

my $client_addr;

while ($client_addr = accept(NEW_SOCKET, SOCKET)) {

   # send them a message, close connection

   my $name = gethostbyaddr($client_addr, AF_INET );

   print NEW_SOCKET "Smile from the server";

   print "Connection recieved from $name\n";

   close NEW_SOCKET;

}

To run the server in background mode issue the following command on Unix prompt:

$perl sever.pl&

Script to Create a Client

!/usr/bin/perl -w

# Filename : client.pl

use strict;

use Socket;

# initialize host and port

my $host = shift || 'localhost';

my $port = shift || 7890;

my $server = "localhost";  # Host IP running the server

# create the socket, connect to the port

socket(SOCKET,PF_INET,SOCK_STREAM,(getprotobyname('tcp'))[2])

   or die "Can't create a socket $!\n";

connect( SOCKET, pack_sockaddr_in($port, inet_aton($server)))

   or die "Can't connect to port $port! \n";

my $line;

while ($line = <SOCKET>) {

        print "$line\n";

}

close SOCKET or die "close: $!";

Now let's start our client at the command prompt which will connect to the server and read message sent by the server and displays the same on the screen as follows:

$perl client.pl

Smile from the server

NOTE: If you are giving actual IP address in dot notation then it is recommended to provide IP address in the same format in both client as well as server to avoid any confusion.

Object Oriented Programming in Perl

We have already studied references in Perl and Perl anonymous arrays and hashes. Object Oriented concept in Perl is very much based on references and anonymous array and hashes. Let's start learning basic concepts of Object Oriented Perl.

Object Basics

There are three main terms, explained from the point of view of how Perl handles objects. The terms are object, class, and method.

·        An object within Perl is merely a reference to a data type that knows what class it belongs to. The object is stored as a reference in a scalar variable. Because a scalar only contains a reference to the object, the same scalar can hold different objects in different classes.

·        A class within Perl is a package that contains the corresponding methods required to create and manipulate objects.

·        A method within Perl is a subroutine, defined with the package. The first argument to the method is an object reference or a package name, depending on whether the method affects the current object or the class.

Perl provides a bless() function which is used to return a reference which ultimately becomes an object.

Defining a Class

It is very simple to define a class in Perl. A class is corresponding to a Perl Package in its simplest form. To create a class in Perl, we first build a package.

A package is a self-contained unit of user-defined variables and subroutines, which can be re-used over and over again.

Perl Packages provide a separate namespace within a Perl program which keeps subroutines and variables independent from conflicting with those in other packages.

To declare a class named Person in Perl we do:

package Person;

The scope of the package definition extends to the end of the file, or until another package keyword is encountered.

Creating and Using Objects

To create an instance of a class (an object) we need an object constructor. This constructor is a method defined within the package. Most programmers choose to name this object constructor method new, but in Perl you can use any name.

You can use any kind of Perl variable as an object in Perl. Most Perl programmers choose either references to arrays or hashes.

Let's create our constructor for our Person class using a Perl hash reference. When creating an object, you need to supply a constructor, which is a subroutine within a package that returns an object reference. The object reference is created by blessing a reference to the package's class. For example:

package Person;

sub new

{

    my $class = shift;

    my $self = {

        _firstName => shift,

        _lastName  => shift,

        _ssn       => shift,

    };

    # Print all the values just for clarification.

    print "First Name is $self->{_firstName}\n";

    print "Last Name is $self->{_lastName}\n";

    print "SSN is $self->{_ssn}\n";

    bless $self, $class;

    return $self;

}

Now Let us see how to create an Object

$object = new Person( "Mohammad", "Saleem", 23234345);

You can use simple hash in your consturctor if you don't want to assign any value to any class variable. For example

package Person;

sub new

{

    my $class = shift;

    my $self = {};

    bless $self, $class;

    return $self;

}

Defining Methods

Other object-oriented languages have the concept of security of data to prevent a programmer from changing an object data directly and they provide accessor methods to modify object data. Perl does not have private variables but we can still use the concept of helper methods to manipulate object data.

Lets define a helper method to get person first name:

sub getFirstName {

    return $self->{_firstName};

}

Another helper function to set person first name:

sub setFirstName {

    my ( $self, $firstName ) = @_;

    $self->{_firstName} = $firstName if defined($firstName);

    return $self->{_firstName};

}

Now lets have a look into complete example: Keep Person package and helper functions into Person.pm file

#!/usr/bin/perl

package Person;

sub new

{

    my $class = shift;

    my $self = {

        _firstName => shift,

        _lastName  => shift,

        _ssn       => shift,

    };

    # Print all the values just for clarification.

    print "First Name is $self->{_firstName}\n";

    print "Last Name is $self->{_lastName}\n";

    print "SSN is $self->{_ssn}\n";

    bless $self, $class;

    return $self;

}

sub setFirstName {

    my ( $self, $firstName ) = @_;

    $self->{_firstName} = $firstName if defined($firstName);

    return $self->{_firstName};

}

sub getFirstName {

    my( $self ) = @_;

    return $self->{_firstName};

}

1;

Now let's make use of Person object in employee.pl file as follows:

#!/usr/bin/perl

use Person;

$object = new Person( "Mohammad", "Saleem", 23234345);

# Get first name which is set using constructor.

$firstName = $object->getFirstName();

print "Before Setting First Name is : $firstName\n";

# Now Set first name using helper function.

$object->setFirstName( "Mohd." );

# Now get first name set by helper function.

$firstName = $object->getFirstName();

print "Before Setting First Name is : $firstName\n";

When we execute above program, it produces following result:

First Name is Mohammad

Last Name is Saleem

SSN is 23234345

Before Setting First Name is : Mohammad

Before Setting First Name is : Mohd.

Inheritance

Object-oriented programming has very good and useful concept called inheritance. Inheritance simply means that properties and methods of a parent class will be available to child classes. So you don't have to write the same code again and again, you can just inherit a parent class.

For example, we can have a class Employee which inherits from Person. This is referred to as an "isa" relationship because an employee is a person. Perl has a special variable, @ISA, to help with this. @ISA governs (method) inheritance.

Following are important points to be considered while using inheritance:

·        Perl searches the class of the specified object for the given method or attribute ie. variable.

·        Perl searches the classes defined in the object class's @ISA array.

·        If no method is found in steps 1 or 2, then Perl uses an AUTOLOAD subroutine, if one is found in the @ISA tree.

·        If a matching method still cannot be found, then Perl searches for the method within the UNIVERSAL class (package) that comes as part of the standard Perl library.

·        If the method still has not found, then Perl gives up and raises a runtime exception.

So to create a new Employee class that will inherit methods and attributes from our Person class, we simply code as follows: Keep this code into Employee.pm

#!/usr/bin/perl

package Employee;

use Person;

use strict;

our @ISA = qw(Person);    # inherits from Person

Now Employee Class has all the methods and attributes inherited from Person class and you can use them as follows: Use main.pl file to test it

#!/usr/bin/perl

use Employee;

$object = new Employee( "Mohammad", "Saleem", 23234345);

# Get first name which is set using constructor.

$firstName = $object->getFirstName();

print "Before Setting First Name is : $firstName\n";

# Now Set first name using helper function.

$object->setFirstName( "Mohd." );

# Now get first name set by helper function.

$firstName = $object->getFirstName();

print "After Setting First Name is : $firstName\n";

When we execute above program, it produces following result:

First Name is Mohammad

Last Name is Saleem

SSN is 23234345

Before Setting First Name is : Mohammad

Before Setting First Name is : Mohd.

Method Overriding

The child class Employee inherits all the methods from parent class Person. But if you would like to override those methods in your child class then you can do it by givig your own implementation. You can add your additional functions in child class or you can add or modify the functionality of an existing methods in its parent class. It can done as follows: modify Employee.pm file

#!/usr/bin/perl

package Employee;

use Person;

use strict;

our @ISA = qw(Person);    # inherits from Person

# Override constructor

sub new {

    my ($class) = @_;

    # Call the constructor of the parent class, Person.

    my $self = $class->SUPER::new( $_[1], $_[2], $_[3] );

    # Add few more attributes

    $self->{_id}   = undef;

    $self->{_title} = undef;

    bless $self, $class;

    return $self;

}

# Override helper function

sub getFirstName {

    my( $self ) = @_;

    # This is child class function.

    print "This is child class helper function\n";

    return $self->{_firstName};

}

# Add more methods

sub setLastName{

    my ( $self, $lastName ) = @_;

    $self->{_lastName} = $lastName if defined($lastName);

    return $self->{_lastName};

}

sub getLastName {

    my( $self ) = @_;

    return $self->{_lastName};

}

1;

Now let's again try to use Employee object in our main.pl file and execute it.

#!/usr/bin/perl

use Employee;

$object = new Employee( "Mohammad", "Saleem", 23234345);

# Get first name which is set using constructor.

$firstName = $object->getFirstName();

print "Before Setting First Name is : $firstName\n";

# Now Set first name using helper function.

$object->setFirstName( "Mohd." );

# Now get first name set by helper function.

$firstName = $object->getFirstName();

print "After Setting First Name is : $firstName\n";

When we execute above program, it produces following result:

First Name is Mohammad

Last Name is Saleem

SSN is 23234345

This is child class helper function

Before Setting First Name is : Mohammad

This is child class helper function

After Setting First Name is : Mohd.

Default Autoloading

Perl offers a feature which you would not find any many other programming languages: a default subroutine. Which means, if you define a function called AUTOLOAD() then any calls to undefined subroutines will call AUTOLOAD() function automatically. The name of the missing subroutine is accessible within this subroutine as $AUTOLOAD.

Default autoloading functionality is very useful for error handling. Here is an example to implement AUTOLOAD, you can implement this function in your own way.

sub AUTOLOAD

{

   my $self = shift;

   my $type = ref ($self) || croak "$self is not an object";

   my $field = $AUTOLOAD;

   $field =~ s/.*://;

   unless (exists $self->{$field})

   {

      croak "$field does not exist in object/class $type";

   }

   if (@_)

   {

      return $self->($name) = shift;

   }

   else

   {

      return $self->($name);

   }

}

Destructors and Garbage Collection

If you have programmed using object oriented programming before, then you will be aware of the need to create a destructor to free the memory allocated to the object when you have finished using it. Perl does this automatically for you as soon as the object goes out of scope.

In case you want to implement your destructore which should take care of closing files or doing some extra processing then you need to define a special method called DESTROY. This method will be called on the object just before Perl frees the memory allocated to it. In all other respects, the DESTROY method is just like any other method, and you can do implement whatever logic you want inside this method.

A destructor method is simply a member function (subroutine) named DESTROY, which will be called automatically in following cases:

·        When the object reference's variable goes out of scope.

·        When the object reference's variable is undef-ed.

·        When the script terminates

·        When the perl interpreter terminates

For Example you can simply put following method DESTROY in your class:

package MyClass;

...

sub DESTROY

{

    print "MyClass::DESTROY called\n";

}

Object Oriented Perl Example

Here is another nice example which will help you to understand Object Oriented Concepts of Perl. Put this source code into any perl file and execute it.

#!/usr/bin/perl

# Following is the implementation of simple Class.

package MyClass;

sub new

{

   print "MyClass::new called\n";

   my $type = shift;            # The package/type name

   my $self = {};               # Reference to empty hash

   return bless $self, $type;  

}

sub DESTROY

{

   print "MyClass::DESTROY called\n";

}

sub MyMethod

{

   print "MyClass::MyMethod called!\n";

}

# Following is the implemnetation of Inheritance.

package MySubClass;

@ISA = qw( MyClass );

sub new

{

   print "MySubClass::new called\n";

   my $type = shift;            # The package/type name

   my $self = MyClass->new;     # Reference to empty hash

   return bless $self, $type; 

}

sub DESTROY

{

   print "MySubClass::DESTROY called\n";

}

sub MyMethod

{

   my $self = shift;

   $self->SUPER::MyMethod();

   print "   MySubClass::MyMethod called!\n";

}

# Here is the main program using above classes.

package main;

print "Invoke MyClass method\n";

$myObject = MyClass->new();

$myObject->MyMethod();

print "Invoke MySubClass method\n";

$myObject2 = MySubClass->new();

$myObject2->MyMethod();

print "Create a scoped object\n";

{

         my $myObject2 = MyClass->new();

}

# Destructor is called automatically here

print "Create and undef an object\n";

$myObject3 = MyClass->new();

undef $myObject3;

print "Fall off the end of the script...\n";

# Remaining destructors are called automatically here

When we execute above program, it produces following result:

Invoke MyClass method

MyClass::new called

MyClass::MyMethod called!

Invoke MySubClass method

MySubClass::new called

MyClass::new called

MyClass::MyMethod called!

   MySubClass::MyMethod called!

Create a scoped object

MyClass::new called

MyClass::DESTROY called

Create and undef an object

MyClass::new called

MyClass::DESTROY called

Fall off the end of the script...

MyClass::DESTROY called

MySubClass::DESTROY called

Perl - Database Access

This tutorial will teach you how to access a database inside your Perl script. Starting from Perl 5 it has become very easy to write database applications using DBI module. 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 provides 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, (ie. 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 can take help of our MySQL tutorial in case you are not aware about how to create database and tables in MySQL database.

·        You have created a database with a name TESTDB.

·        You have created a table with a name 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 some records into a table. Here we are using table TEST_TABLE to create our records. 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 as many as records you like using the same concept.

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 rollbackcomplete transaction. Commit and Rollback are explained in next sections.

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

                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )

                        values

                       ('john', 'poul', 'M', 30, 13000)");

$sth->execute() or die $DBI::errstr;

$sth->finish();

$dbh->commit or die $DBI::errstr;

Using Bind Values

There may be a case when values to be entered is not given in advance. So you can use bind variables which will take required values at run time. Perl DBI modules makes use of a question mark in place of actual value and then actual values are passed through execute() API at the run time. Following is the example:

my $first_name = "john";

my $last_name = "poul";

my $sex = "M";

my $income = 13000;

my $age = 30;

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

                       (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )

                        values

                       (?,?,?,?)");

$sth->execute($first_name,$last_name,$sex, $age, $income)

          or die $DBI::errstr;

$sth->finish();

$dbh->commit or die $DBI::errstr;

READ Operation

READ Operation on any databasse means to fetch some useful information from the database ie one or more records from one or more tables. So once our database connection is established, we are ready to make a query into this database. Following is the procedure to query all the records having AGE greater than 20. This will take four steps

·        Prearing SQL SELECT query based on required conditions. This will be done usingprepare() API.

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

·        Fetching all the results one by one and printing those results.This will be done using fetchrow_array() API.

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

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME

                        FROM TEST_TABLE

                        WHERE AGE > 20");

$sth->execute() or die $DBI::errstr;

print "Number of rows found :" + $sth->rows;

while (my @row = $sth->fetchrow_array()) {

   my ($first_name, $last_name ) = @row;

   print "First Name = $first_name, Last Name = $last_name\n";

}

$sth->finish();

Using Bind Values

There may be a case when condition is not given in advance. So you can use bind variables which will take required values at run time. Perl DBI modules makes use of a question mark in place of actual value and then actual values are passed through execute() API at the run time. Following is the example:

$age = 20;

my $sth = $dbh->prepare("SELECT FIRST_NAME, LAST_NAME

                        FROM TEST_TABLE

                        WHERE AGE > ?");

$sth->execute( $age ) or die $DBI::errstr;

print "Number of rows found :" + $sth->rows;

while (my @row = $sth->fetchrow_array()) {

   my ($first_name, $last_name ) = @row;

   print "First Name = $first_name, Last Name = $last_name\n";

}

$sth->finish();

UPDATE Operation

UPDATE Operation on any databasse means to update one or more records already available in the database tables. Following is the procedure to update all the records having SEX as 'M'. Here we will increase AGE of all the males by one year. This will take three steps

·        Prearing SQL query based on required conditions. This will be done usingprepare() 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 rollbackcomplete transaction. See next section for commit and rollback APIs.

my $sth = $dbh->prepare("UPDATE TEST_TABLE

                        SET   AGE = AGE + 1

                        WHERE SEX = 'M'");

$sth->execute() or die $DBI::errstr;

print "Number of rows updated :" + $sth->rows;

$sth->finish();

$dbh->commit or die $DBI::errstr;

Using Bind Values

There may be a case when condition is not given in advance. So you can use bind variables which will take required values at run time. Perl DBI modules makes use of a question mark in place of actual value and then actual values are passed through execute() API at the run time. Following is the example:

$sex = 'M';

my $sth = $dbh->prepare("UPDATE TEST_TABLE

                        SET   AGE = AGE + 1

                        WHERE SEX = ?");

$sth->execute('$sex') or die $DBI::errstr;

print "Number of rows updated :" + $sth->rows;

$sth->finish();

$dbh->commit or die $DBI::errstr;

In some case you would like to set a value which is not given in advance so you can use binding value as follows. In this example income of all males will be set to 10000.

$sex = 'M';

$income = 10000;

my $sth = $dbh->prepare("UPDATE TEST_TABLE

                        SET   INCOME = ?

                        WHERE SEX = ?");

$sth->execute( $income, '$sex') or die $DBI::errstr;

print "Number of rows updated :" + $sth->rows;

$sth->finish();

DELETE Operation

DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from TEST_TABLE where AGE is equal to 30. This operation will take following steps.

·        Prearing SQL query based on required conditions. This will be done usingprepare() API.

·        Executing SQL query to delete required records 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 rollbackcomplete transaction.

$age = 30;

my $sth = $dbh->prepare("DELETE FROM TEST_TABLE

                        WHERE AGE = ?");

$sth->execute( $age ) or die $DBI::errstr;

print "Number of rows deleted :" + $sth->rows;

$sth->finish();

$dbh->commit or die $DBI::errstr;

Using do Statement

If you're doing an UPDATE, INSERT, or DELETE there is no data that comes back from the database, so there is a short cut to perform this operation. You can use do statement to execute any of the command as follows.

$dbh->do('DELETE FROM TEST_TABLE WHERE age =30');

do returns a true value if it succeeded, and a false value if it failed. Actually, if it succeeds it returns the number of affected rows. In the example it would return the number of rows that were actually deleted.

COMMIT Operation

Commit is the operation which gives a green signal to database to finalize the changes and after this operation no change can be reverted to its orignal position.

Here is a simple example to call commit API.

$dbh->commit or die $dbh->errstr;

ROLLBACK Operation

If you are not satisfied with all the changes or you encounter an error in between of any operation , you can revert those changes to use rollback API.

Here is a simple example to call rollback API.

$dbh->rollback or die $dbh->errstr;

Begin Transaction

Many databases support transactions. This means that you can make a whole bunch of queries which would modify the databases, but none of the changes are actually made. Then at the end you issue the special SQL query COMMIT, and all the changes are made simultaneously. Alternatively, you can issue the query ROLLBACK, in which case all the changes are thrown away and database remains unchanged.

Perl DBI module provided begin_work API, which enables transactions (by turning AutoCommit off) until the next call to commit or rollback. After the next commit or rollback, AutoCommit will automatically be turned on again.

$rc  = $dbh->begin_work  or die $dbh->errstr;

AutoCommit Option

If your transactions are simple, you can save yourself the trouble of having to issue a lot of commits. When you make the connect call, you can specify an AutoCommit option which will perform an automatic commit operation after every successful query. Here's what it looks like:

my $dbh = DBI->connect($dsn, $userid, $password,

              {AutoCommit => 1})

              or die $DBI::errstr;

Here AutoCommit can take value 1 or 0, where 1 means AutoCommit is on and 0 means AutoCommit is off.

Automatic Error Handling

When you make the connect call, you can specify a RaiseErrors option that handles errors for you automatically. When an error occurs, DBI will abort your program instead of returning a failure code. If all you want is to abort the program on an error, this can be convenient. Here's what it looks like:

my $dbh = DBI->connect($dsn, $userid, $password,

              {RaiseError => 1})

              or die $DBI::errstr;

Here RaiseError can take value 1 or 0.

Disconnecting Database

To disconnect Database connection, use disconnect API as follows:

$rc = $dbh->disconnect  or warn $dbh->errstr;

The transaction behaviour of the disconnect method is, sadly, undefined. Some database systems (such as Oracle and Ingres) will automatically commit any outstanding changes, but others (such as Informix) will rollback any outstanding changes. Applications not using AutoCommit should explicitly call commit or rollback before calling disconnect.

Using NULL values

Undefined values, or undef, are used to indicate NULL values. You can insert and update columns with a NULL value as you would a non-NULL value. These examples insert and update the column age with a NULL value:

$sth = $dbh->prepare(qq{

       INSERT INTO TEST_TABLE (FIRST_NAME, AGE) VALUES (?, ?)

       });

 $sth->execute("Joe", undef);

Here qq{} is used to return a quoted string to prepare API. However, care must be taken when trying to use NULL values in a WHERE clause. Consider:

SELECT FIRST_NAME FROM TEST_TABLE WHERE age = ?

Binding an undef (NULL) to the placeholder will not select rows which have a NULL age! At least for database engines that conform to the SQL standard. Refer to the SQL manual for your database engine or any SQL book for the reasons for this. To explicitly select NULLs you have to say "WHERE age IS NULL".

A common issue is to have a code fragment handle a value that could be either defined or undef (non-NULL or NULL) at runtime. A simple technique is to prepare the appropriate statement as needed, and substitute the placeholder for non-NULL cases:

$sql_clause = defined $age? "age = ?" : "age IS NULL";

$sth = $dbh->prepare(qq{

       SELECT FIRST_NAME FROM TEST_TABLE WHERE $sql_clause

       });

$sth->execute(defined $age ? $age : ());

Some other DBI functions

available_drivers

@ary = DBI->available_drivers;

@ary = DBI->available_drivers($quiet);

Returns a list of all available drivers by searching for DBD::* modules through the directories in @INC. By default, a warning is given if some drivers are hidden by others of the same name in earlier directories. Passing a true value for $quiet will inhibit the warning.

installed_drivers

%drivers = DBI->installed_drivers();

Returns a list of driver name and driver handle pairs for all drivers 'installed' (loaded) into the current process. The driver name does not include the 'DBD::' prefix.

data_sources

@ary = DBI->data_sources($driver);

Returns a list of data sources (databases) available via the named driver. If $driver is empty or undef, then the value of the DBI_DRIVER environment variable is used.

quote

$sql = $dbh->quote($value);

$sql = $dbh->quote($value, $data_type);

Quote a string literal for use as a literal value in an SQL statement, by escaping any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.

$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",

                $dbh->quote("Don't");

For most database types, quote would return 'Don''t' (including the outer quotation marks). It is valid for the quote() method to return an SQL expression that evaluates to the desired string. For example:

$quoted = $dbh->quote("one\ntwo\0three")

may produce results which will be equivalent to

CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')

Methods Common to all Handles

err

$rv = $h->err;

or

$rv = $DBI::err

or

$rv = $h->err

Returns the native database engine error code from the last driver method called. The code is typically an integer but you should not assume that. This is equivalent to $DBI::err or $h->err.

errstr

$str = $h->errstr;

or

$str = $DBI::errstr

or

$str = $h->errstr

Returns the native database engine error message from the last DBI method called. This has the same lifespan issues as the "err" method described above. This is equivalent to $DBI::errstr or $h->errstr.

rows

$rv = $h->rows;

or

$rv = $DBI::rows

This returns the number of rows effected by previous SQL statement and equivalent to $DBI::rows.

trace

$h->trace($trace_settings);

DBI sports an extremely useful ability to generate runtime tracing information of what it's doing, which can be a huge time-saver when trying to track down strange problems in your DBI programs. You can use different values to set trace level. These values varies from 0 to 4. The value 0 means disable trace and 4 means generate complete trace.

Interpolated Statements are Prohebited

It is highly recommended not to use interpolated statements as follows:

while ($first_name = <>) {

   my $sth = $dbh->prepare("SELECT *

                            FROM TEST_TABLE

                            WHERE FIRST_NAME = '$first_name'");

   $sth->execute();

   # and so on ...

}

Thus don't use interpolated statement instead use bind value to prepare dynamic SQL statement.