Skip navigation.
Home
Your source for Perl tips, howto's, faq and tutorials
( categories: )

The DBI module provides a high level layer to interact with databases. The advantage of using this module is that provides a uniform interface to handle different types of databases.

Using DBI to interact with a database usually consists of 4 steps:

  1. Connect to the database
  2. Execute a query and obtain the result set
  3. Process the result set
  4. Disconnect from the database

1. CONNECT TO THE DATABASE

my $dbh = DBI->connect("DBI:mysql:dbname:hostname:port","username","password");

where dbname is the name of the database, hostname is the server where the database is located, port is the port where the database server is listening, username is the database user and password is the user's password. (hostname and port can be omitted if the database is in localhost and listens in the default port).

Notice that the parameters to this function could be different, depending of the database being used; in this example, a MySQL database is used, PostgreSQL uses the same format, you only have to change 'mysql' with 'Pg'.

On unsuccessful connection, the function returns "undef" and sets both $DBI::err and $DBI::errstr (the example at the end of this article shows a usage example).

2. EXECUTE A QUERY AND OBTAIN A RESULT SET

How to execute a query depends of the type of the query.

If the query only returns a success/failure value (like adding a row to a table, droping a table, etc) then use something like this:

$dbh->do("INSERT INTO table VALUES (column1, column2)");

where $dbh is the database handle object returned from the previous 'connect' statement

If you expect to obtain more information than just a success/failure value (for example getting all the rows of a table) then you need to do the following:

my $sth = $dbh->prepare("SELECT (column1, column2) FROM table");
$sth->execute();

3. PROCESS THE RESULT SET

If in the previous step you executed the 'do' statement, then you only have to check the result code this function returns.

If you executed 'prepare' and 'execute', then you need to fetch all the values returned by the SQL query. This is done with a statement like this:

while ( my $row = $sth->fetchrow_hashref() ) {
   $value1 = $row->{column1};
   $value2 = $row->{column2};
}

'fetchrow_hashref()' returns the row in the form of a hash reference, where each key is the name of the column you selected. Instead of 'fetchrow_hashref()' you can use 'fetchrow_arrayref', which returns the row in the form of a reference to an array.

4. DISCONNECT FROM THE DATABASE

This is done with:

$dbh->disconnect();

- COMPLETE EXAMPLE

#!/usr/bin/perl
use DBI;

my $dbh = DBI->connect("DBI:mysql:test","user","pass")
          or die("Error connecting to DB test:",$DBI::errstr);

my $sth = $dbh->prepare("SELECT (column1, column2) FROM table");
$sth->execute();

while ( my $row = $sth->fetchrow_hashref() ) {
    print $row->{column1} . ", " . $row->{column2} . "\n";    
}

$dbh->disconnect();