A simple introduction to database interface using perl dbi.

A simple introduction to database interface using perl dbi.

DBI – DataBase Interface module (more precisely a collection of modules) is a feature rich, efficient and yet simple tool to access databases using perl. Almost all Linux distributions have them, if not you can download from cpan.org. The interface to any DBMS requires two sets of tools – one DBI itself which is generic, two the DBD::the_database. DBD is the driver component and you should install drivers for whatever database you are using. DBI drivers are available for almost all standard databases.

Normally database access workflow is like this:

a.Connect to the database (logging) using username,password etc.. Once properly authenticated a database-handle will be given.

b.Create the sql query, use database-handle to send the query to the server and ask it to prepare the query.

c. Server parses the sql, if no errors, returns a statement-handle.

d.Use the statement-handle to execute the query.

f.Use statement-handle to fetch data – single row or multiple rows at a time.

g.Close the statement handle

h. Repeat steps b to g as long as you want, with new queries

i.Finally disconnect from database(logout) using database-handle.

Let us see them by means of a sample code.

Assumptions:

Database Server: mysql running on local host

Database user name: test Password: test123

Database name : testdb

Table : names

Columns in the table: id,name,age

Objective 1:

1. List all records in the table and find out average age

Let us start the code. (codes are give in italics and bold)

Step 1:

Connecting to the database

use DBI;

my $dbh=DBI->connect(‘DBI:mysql:database=testdb;host=localhost’,’test’,’test123′);

Connect requires three arguments : datasource, username,password

First argument -datasource gives information about the database server like type of dbms, location etc.

In our example datasource is specified as DBI:mysql:database=testdb;host=localhost

Here DBI:mysql means use mysql driver.

database=testdb means use the database testdb.

host=localhost means the host in which the database is running.

Other two arguments are username and password which need no explanation.

Step 2

Run the select query on the server.

First store sql in a variable like this

my $query=’select * from name ‘;

Then send the sql to the server for parsing and checking

my $sth=$dbh->prepare($query) or die “could not prepare $query\n”;

In the above statement

$dbh is the database connection handle we got while using DBI->connect earlier.

$sth is the statement handle returned upon successful preparation.

$query refers to the sql statement. The query can be given directly as string also.

Here we do some error checking by using  die. The $sth that is returned will be required for any further opertion on this query.

Now we will run the query on the server

$sth->execute();

Note here, we are simply using $sth to run the query. Once we call execute, the server runs the query and keeps the result set ready for retrieval.

Step 3

Get results from the server one row at a time.

fetchrow_array() is a function that will return one row of data and store result in an array.

We will use a while loop to fetch all rows from the server.

while (($id,$name,$age)=$sth->fetchrow_array()){

print “id=$id name=$name age=$age\n”;

}

$sth->fetchrow will return a  null when there are no more rows. Thus this loop will run until null.

($id,$name,$age)=$sth->fetchrow_array() is used to equate the rows returned to a set of variables.

Step 4

Close the statement handle

$sth->finish();

Step 5

Close the database connection

$dbh->disconnect();

Here is the output of running the script.

id=1 name=RAMAN age=45

id=2 name=RAVI age=35

For the sake convenience I am repeating program listing here.

use DBI;

my $dbh=DBI->connect(‘DBI:mysql:database=testdb;host=localhost’,’test’,’test123′);

my $query=’select * from name ‘;

my $sth=$dbh->prepare($query) or die “could not prepare $query\n”;

$sth->execute();

while (($id,$name,$age)=$sth->fetchrow_array()){

print “id=$id name=$name age=$age\n”;

}

$sth->finish();

$dbh->disconnect()

Objective 2.

Insert a record accepting input from terminal

Here is the code to insert a row into name table.

Step 1:

Establishing connection with database. For explanation see previous example

use DBI;

my $dbh=DBI->connect(‘DBI:mysql:database=testdb;host=localhost’,’test’,’test123′);

Step 2

Accept input from keyboard

print ‘Enter id:’;

$id=<STDIN>;

print ‘Enter Name:’;

$name=<STDIN>;

print ‘Enter age’;

$age=<STDIN>;

use chomp to remove any newlines

chomp $id;

chomp $age;

chomp $name;

Explanation : The print statement is simple – just shows message on the screen.

$id=<STDIN> means accept value from standard input (by default keyboard) and store the value in the variable $id;

Step 3

Create the Sql statement

$query=sprintf “insert into name(id,name,age) values(%d,%s,%d)”,$id,$dbh->quote($name),$age;

Explanation: Here we create a string using sprintf function, by variable substitution.

The sprintf in perl is similar to sprintf in c.

$dbh->quote() function is used on string values, so that quotes are properly taken care of -e.g names like D’Silva. It is a best practice to use this.

At the end of this line the $query variable will have an sql which is an insert statement.

Step 4.

Run the query on the server and insert data.

$dbh->do($query) or die “could not do $query\n”;

The sql execution in the previous example was done in three stages namely prepare, execute,fetchrow_array.  In the case of insert statement no rows are returned. Hence, we can use do(), which combines all three stages into one. It will return number of rows affected.

Step 5.

Disconnect from the server

$dbh->disconnect();

For the sake of convenience the code is repeated here.

use DBI;

my $dbh=DBI->connect(‘DBI:mysql:database=testdb;host=localhost’,’test’,’test123′);

print ‘Enter id:’;

$id=<STDIN>;

print ‘Enter Name:’;

$name=<STDIN>;

print ‘Enter age’;

$age=<STDIN>;

chomp $id;

chomp $age;

chomp $name;

$query=sprintf “insert into name(id,name,age) values(%d,%s,%d)”,$id,$dbh->quote($name),$age;

$dbh->do($query) or die “could not do $query\n”;

$dbh->disconnect();

As you can see from the above examples dbi is pretty simple. Same code can be used for any database. Only change required will be in connection step.

The performance of dbi is very good, and it has lot features like fetching column names, types etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: