Database programming in Perl shows how to connect to a mysql or oracle database or any
other database and execute any sql statement in the connected database through a Perl
program.
The following example shows connecting to a database and executing insert, update,
select statement on a table of the connected database through the DBI module.
Following is the table structure with a record set, on which the following example
is operating:
---------------------------------------------------------------
|Tablename: employee| |
|-------------------------------------------------------------|
|emp_id(varchar(255))|employee_name(varchar(255))|age(integer)|
|--------------------|---------------------------|------------|
|001 |James Dcosta | 35 |
---------------------------------------------------------------
#!/usr/bin/perl
use DBI; # the module required for performing the database programming operations
$dbh=DBI->connect("dbi:mysql:db_name:server_name","db_username","db_password"); # establishing a data base connection by specifying the database driver (here dbi:mysql is used for connecting to a mysql database), database name, server ip or name of the server where the database is residing and then the database username and the database password respectively
$sth=$dbh->prepare("insert into employee(emp_id,employee_name,age) value(?,?,?)"); # preparing the insert statement with place holders (?), for emp_id, employee_name, age and storing it in a statement handle
$sth->execute("002","Michael Jonson",40); # executing the statement by passing respective values for the place holders, here employee id, employee name and age
print "Record Inserted\n";
$sth=$dbh->prepare("select emp_id,employee_name,age from employee where emp_id=?"); # preparing the select statement with place holders (?) for emp_id, employee_name, age and storing it in a statement handle
$sth->execute("002"); # executing the statement by passing respective values for the place holders, here employee id
while(@data=$sth->fetchrow_array()) # fetching one row at a time from the table and storing it in an array
{
print "$data[0] -- $data[1] -- $data[2]\n"; # displaying the fetched records on the screen, the first element of the array holds the first column data and then the second element of the array holds the second column data from the row respectively, etc
}
$sth->finish(); # indicates that no more data will be fetched from this statement handle
$sth=$dbh->prepare("update employee set age=? where emp_id=?"); # preparing the update statement with place holders (?), for updating age for a given employee id
$sth->execute(45,"002"); # executing the statement by passing respective values for the place holders, here new value for age and employee id of the employee whose age is to be modified
print "Record Modified\n";
# displaying all rows from the table
$sth=$dbh->prepare("select emp_id,employee_name,age from employee");
$sth->execute();
while(@data=$sth->fetchrow_array())
{
print "$data[0] -- $data[1] -- $data[2]\n";
}
$sth->finish();
Output:
-------
002 -- Michael Jonson -- 40
001 -- James Dcosta -- 35
002 -- Michael Jonson -- 45
