While in most fields Perl adheres to the concept of TMTOWTDI, in accessing relational databases Perl has a de-facto standard library called DBI or Database independent interface for Perl.

Architecture

The Perl scripts use DBI, which in turn uses the appropriate Database Driver (e.g. DBD::Oracle for Oracle, DBD::Pg for PostgreSQL and DBD::SQLite to access SQLite).

Those drivers are compiled together with the C client libraries of the respective database engines. In case of SQLite, of course all the database engine gets embedded in the perl application.

It is very hard to improve on the lovely ASCII-art that comes with the documentation of DBI so let me reproduce it here:

|| .-. .--------------. .-------------. .-------. | |---| XYZ Driver |---| XYZ Engine | | Perl | | | `--------------' `-------------' | script| |A| |D| .--------------. .-------------. | using |--|P|--|B|---|Oracle Driver |---|Oracle Engine| | DBI | |I| |I| `--------------' `-------------' | API | | |... |methods| | |... Other drivers `-------' | |... `-'

Simple example

I'll use SQLite to demonstrate the examples. That will make it very easy for you to try them on your computer.

#!/usr/bin/perl use strict; use warnings; use DBI; my $dbfile = "sample.db"; my $dsn = "dbi:SQLite:dbname=$dbfile"; my $user = ""; my $password = ""; my $dbh = DBI->connect($dsn, $user, $password, { PrintError => 0, RaiseError => 1, AutoCommit => 1, FetchHashKeyName => 'NAME_lc', }); # ... $dbh->disconnect;

We load DBI but we do not explicitly load the database driver. That will be done by DBI.

The DSN (Data Source Name) (in the $dsn variable) is very straight forward. It contains the type of the database. That will be the clue to DBI which DBD to load. In case of SQLite, the only thing we really need is the path to the database file.

The username and password fields were left empty. I think they are not relevant at all for SQLite.

The last parameter of the connect call is a reference to a hash containing some attributes I like to set.

The DBI->connect call returns a database handle object that usually we store in a variable called $dbh.

The call to disconnect from the database is optional as it will automatically be called when the variable $dbh goes out of scope. Having it might be a clear indication for the next programmer dealing with this code, that you are done with the database.

CREATE TABLE

Of course having a connection is not enough. We need to be able to fetch data from the database, or insert data into the database, but for our example to work first we actually need to create the tables of the database.

In this case we can do this with a single command:

my $sql = <<'END_SQL'; CREATE TABLE people ( id INTEGER PRIMARY KEY, fname VARCHAR(100), lname VARCHAR(100), email VARCHAR(100) UNIQUE NOT NULL, password VARCHAR(20) ) END_SQL $dbh->do($sql);

The first statement is just a here document of an SQL statement to CREATE TABLE. Then we call the do method of the database handle which will send the SQL statement to the database.

INSERT

Now let's see the real thing, inserting data:

my $fname = 'Foo'; my $lname = 'Bar', my $email = 'foo@bar.com'; $dbh->do('INSERT INTO people (fname, lname, email) VALUES (?, ?, ?)', undef, $fname, $lname, $email);

To insert a row we call the $dbh->do method again, but instead of passing the actual data, we put question-marks ? as place-holders.

The SQL statement is followed by the word undef. That's actually the place of a hash-reference providing parameters to this specific call, similar to the attributes passed to the connect method, but I think it is rarely used in these statements.

The undef is followed by the actual values that go in place of the the place-holders.

As you can see we did not have to put the place-holders in any kind of quotes nor did we have to somehow convert the values. DBI did it for us.

This helps us avoid SQL injection attacks. Even if you meet someone called Bobby Tables.

UPDATE

For updating some data in the database we also use the do method.

my $password = 'hush hush'; my $id = 1; $dbh->do('UPDATE people SET password = ? WHERE id = ?', undef, $password, $id);

Nothing special here. An SQL statement with some place-holders. undef instead of the extra attributes, and the parameters to be used in place of the place-holders.

SELECT

This is by far the most interesting part of the database access. As the SELECT statement can return a lot of rows and a lot of values in each row we cannot use a simple call to the do method.

Instead, there are several ways to fetch the data. I'll show here two. For both we have 3 steps: prepare the SQL statement, execute the statement with specific data, and fetch the rows.

From these, the prepare statement can be shared by - assuming the queries only differ in the data we pass to them. We create an SQL statement using question marks (?) as place-holders instead of actual values.

This call returns a statement handle object that we usually save in a variable called $sth.

Then we call the execute method of the statement handle passing to it values that should be placed instead of the place-holders.

The third step is the really interesting one. In a while loop we fetch the results, row-by row. For this we can use several methods:

The fetchrow_array method will return the values of the next row in the result set as a list, that we can assign to an array. The order of the elements is as the order of the fields in the query. (fname, lname in our case).

The fetchrow_hashref method will return a reference to a hash. In the hash the keys will be the names of the fields in the database. As different databases might return these names of the fields in different cases we configured our database handler to make sure they - the names of the fields - will always converted to lower case. (That's what the FetchHashKeyName parameter did, when connecting to the database.)

my $sql = 'SELECT fname, lname FROM people WHERE id >= ? AND id < ?'; my $sth = $dbh->prepare($sql); $sth->execute(1, 10); while (my @row = $sth->fetchrow_array) { print "fname: $row[0] lname: $row[1]

"; } $sth->execute(12, 17); while (my $row = $sth->fetchrow_hashref) { print "fname: $row->{fname} lname: $row->{lname}

"; }

Exercise

Take the above snippets of code. Use the first one to set up the database and create a table. Then use the second one to insert a few people in the table.

Finally use the last example to extract some data from the database and print it out.

If you have any question, feel free to ask below.

Thanks

to sigzero for correcting a bug in the examples!