Perl DBI Tutorial

 

The perl DBI module allows us to work with data in a database.  It is an object oriented class, which carries with it a tad more complexity than other modules available from CPAN.org.

 

To create the dbi object, use this syntax:

 

use DBI;

my $dbh = DBI->connect(‘database_server_address’, ‘username’, ‘password’);

 

Once you have what’s called a “database handle object”, you can use it’s methods (which are similar to functions, except related to a specific object you have already created) to prepare and execute SQL statements.   Here are a few examples:

 

my $sth = $dbh->prepare(‘select * from orders’);

$sth->execute();

 

This will select all columns from the orders database table.  The asterisk literally means “all columns”.

 

Then, to print out the data you queried, you use a while loop, reading from the fetchrow_hashref method.  It’s kind of weird, but you’ll get used to it.

 

while (my $row_href = $sth->fetchrow_hashref)

{

  print $row_href->{‘id’}, br;

}

 

The above code will print out the id of all rows in the orders table, followed by an html line break using CGI.pm’s “br” function.

 

You can use a cool feature of DBI called placeholders to make things easier when feeding values to SQL statements.  You use question marks to define where to put the data you pass to the execute method, like this:

 

my $sth = $dbh->prepare(‘insert into orders (account_id, product_id) values (?, ?)’);

$sth->execute(‘34’, ‘5439’);