PerlDBIIntro - SH/SC Wiki

Basic Intro to Perl's DBI [Database Interface] module

What you'll need:

perl or if your on windows, ActivePerl
DBI
A Database. MySQL is quick and easy to install, free, and works well.

The quick, hand-waving version:

use strict; use DBI; # Connect to the database my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_password') or die "Couldn't open database: $DBI::errstr; stopped"; # Prepare the SQL query for execution my $sth = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement: $DBI::errstr; stopped"; SELECT field_1, field_2, field_3 FROM my_table WHERE field_1 = 'my_condition' End_SQL # Execute the query $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; # Fetch each row and print it while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) { print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n"; } # Disconnect from the database $dbh->disconnect();

The In-depth explanation:

DBI is perl's DataBase Interface. It works transparently with almost every known DB, from Oracle to MS Access, even flat text files can be treated as a DB using DBI.

To read from a DB, the following steps are usually required:

Connect to DB
Prepare a SQL request
Send the request
Read the results

How DBI works

Due to the many different Databases available, there are many different ways of communicating with them. For this reason DBI also requires a Database Driver module. The DBI modules' interface stays the same, no matter what DB you are using. This is particularly handy if you start your project on MS Access, and it grows to say, Oracle. Your existing Access based code will require a single line to be changed, and it will talk to Oracle instead :)

The Database Drivers are seperate modules, usually called

DBD::Oracle
DBD::MySQL
DBD::File
etc...

Connecting to the DB

my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_password') or die "Couldn't open database: '$DBI::errstr'; stopped";

The first part of the connect string (DBI:mysql:my_database) follows this general pattern:
DBI:driver:database

Read the Docs for the particular driver you are using for details. For example, the DBD::File driver, which just connects to a text file and lets you run SQL queries on it, will require a filename, and no authentication.

Obviously this step can fail for many reasons, so always die on failure, and print out $DBI::errstr for the reason why.

Preparing and Executing the Query

Prepare:

my $sth = prepare(q{SELECT first_name FROM names WHERE age < 18}) or die "Couldn't prepare statement: $DBI::errstr; stopped";

Again, always die on failure, and find out what failed.
$sth is our statement handle, which we will use to refer to the results of this query later.

Execute:

$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

This step is pretty simple, it sends the prepared SQL statement to the DB for execution.

Reading the results

Now that you've sent the query, your database will grind a bit and spit out the rows that satisfy it. To read the data from those rows, you need to fetch them, one row at a time. There are three methods that you can use to fetch the data: fetchrow_array(), fetchrow_arrayref(), and fetchrow_hashref().

Each time that you call them, they'll return the data for a row returned by the query. When there are no more rows left, they'll return a false value. fetchrow_array() returns each row as an array of scalar values; it returns an empty list when it's done.

It's generally used like so:

while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) { print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n"; }

Note that we continue to cycle through the results using the while command, until eventually fetchrow_array() returns no results, causing the while loop to exit.

fetchrow_arrayref() is similar. It returns each row as a reference to an array. This method is slightly more efficient than calling the fetchrow_array() method, since we don't need to spend time, memory and clock cycles copying the array values. Here's how it's used:

while ( my $fields = $sth->fetchrow_arrayref() ) { print STDOUT "Field 1: $fields->[0] Field 2: $fields->[1] Field 3: $fields->[2]\n"; }

fetchrow_hashref() is one of the most flexible of these methods. It returns each row as a reference to a hash. The keys of the hash are the names of the columns, and the values are the values of that column for this particular row. Use it like so:

while ( my $field_hash = $sth->fetchrow_hashref() ) { print STDOUT "Field 1: $field_hash->{'field1'} Field 2: $field_hash->{'field2'} Field 3: $field_hash->{'field3'}\n"; }

Some words on Efficiency & Robustness

Everything I said above is true enough when you know your SQL statements in advance, but what if they are generated dynamically? Say you prompt your user for a customers' surname, store that in the variable $surname, and want to return that customers' age. You could:

my $sth = $dbh->prepare("SELECT age FROM customer_table WHERE surname='$surname' ")

note the apostrophes around the variable $surname, causing it to be interpolated by perl

This would work, but what if suddenly the user searches for the surname D'Arcy? The apostrophe in D'Arcy would get read as an SQL end quote, and cause all kinds of errors.

Fortunately, DBI offers placeholders. These are kindof like SQL variables, which tell the DB "at execution time, we will replace these placeholders with real values". Then, during the execute command, you pass the values to the DB, like so:

my $sth = $dbh->prepare("SELECT age FROM customer_table WHERE surname=?") ... $sth->execute($surname);

Now you can keep executing the same $sth with different age values, having only prepared the statement handle once. And that is a good thing :)

And finally...

Disconnect!

$sth->disconnect();

Gotta close that connection before you exit!

Questions / Comments? sexcopter@gmail.com