\documentclass{article}
\usepackage{hyperref}

\author{Brad Marshall {\tt <brad.marshall@member.sage-au.org.au>}}
\title{Introduction to Using Databases with Perl}
 
\begin{document}

\maketitle
 
\section{Introduction}

Perl provides an excellent set of modules for talking to
databases called DBI (for Database Interface).  These modules
allow you to connect to a wide variety of databases, and
switch between them almost transparently - assuming the SQL
used isn't database specific.

DBI works by providing a front end API to database access,
and uses backend modules called DBD to provide the actual
communication to the databases.  There are DBD modules for
most common (and not so common) databases, ranging from MySQL,
PostgreSQL, Oracle and ODBC to Excel, Google, LDAP and flat
files - see the DBI website for a full list.

This article will cover the basics of connecting, inserting
and reading data from a database, using PostgreSQL as the
example backend database.

\section{Perl Scripting}

\subsection{Modules}

The first thing that's required for using DBI is the following
line in your perl script:

\begin{verbatim}
use DBI;
\end{verbatim}

This will load and initialise the DBI module for your script
- note you don't need to import any of the DBD modules, they
will be done as required.

\subsection{Connecting}

The next step is to connect to the database, via the following:

\begin{verbatim}
my $dbh = DBI->connect("dbi:Pg:dbname=$db;host=$hostname", 
    "$user", "$password") 
    or die "Can't connect to postgres db: $!\n";
\end{verbatim}

This creates a database handle for the connection to the
database, using the given hostname, database, username and
password.  To change the type of database you are talking
to, you simply have to change the connection string to the
appropriate new database backend - for example, for MySQL the
connection string would be:

\begin{verbatim}
my $dbh = DBI->connect("dbi:mysql:database=$db;host=$hostname", 
    "$user", "$password") 
\end{verbatim}

\subsection{Preparing Queries}

Next you need to create a statement that you wish to run
against the database, such as:

\begin{verbatim}
my $fullquery = "SELECT * FROM table";
my $sth = $dbh->prepare($fullquery)
   or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";
$sth->execute() 
  or die "Can't execute SQL statement: ", $sth->errstr(), "\n";
\end{verbatim}

This prepares and executes the statement handler for a simple
SQL query that returns all the data in the table.  Preparing the
statement allows the database to parse the statement and check
that it is valid SQL, and the tables and columns that you
are referring to exist and you have permission to read them,
among other things.

Another option with preparing a statement is to use
placeholders, or bind values.  To prepare the statement handler
with placeholders, you can do:

\begin{verbatim}
my $sth = $dbh->prepare(SELECT ?,? FROM table") 
  or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";
\end{verbatim}

You can then fill in the placeholders either using bind values,
or in the execute statement.  To use bind values, you specify
the values via the following:

\begin{verbatim}
$sth->bind_param(1, "user");
$sth->bind_param(2, "name");
\end{verbatim}

Alternatively, to fill out the values in the execute statement,
you do the following:

\begin{verbatim}
$sth->execute("user", "name");
\end{verbatim}

\subsection{Returning Data}

Obviously, the next step will be getting the data that the
query provided.  The simplest way is:

\begin{verbatim}
while( my @ary = $sth->fetchrow_array) {
     # Do something with @ary
}
\end{verbatim}

Another option to simply view the results is:

\begin{verbatim}
$rows = $sth->dump_results();
\end{verbatim}

\subsection{Closing Handlers}

To close a statement handler - even if you haven't read all
the data from it is simple - just do the following:

\begin{verbatim}
$sth->finish;
\end{verbatim}

\subsection{Disconnecting}

Once you've finished with a database connection it is good form
to close it off - even though it will automatically close once
you reach the end of the script.

\begin{verbatim}
$dbh->disconnect or warn "Disconnection failed: $!\n";
\end{verbatim}

\section{Conclusion}

As you can see, this was a very quick run through of the
basics of using DBI which shows the easy and power of the
modules.  You can do far, far more with it than covered here
- for more information see ``Programming the Perl DBI'' by
Alligator Descartes and Tim Bunce, and the Perl DBI website
at http://dbi.perl.org/.

\end{document}