[Home]  [Edit this page]  [Recent Changes]  [Special Pages]  [Help] 

---

PerlDBITutorial

---

Back to Perl FAQ Main Page.

=== Perl Database Interactivity Using DBI === This tutorial will teach you the basics of using the DBI module and DBD::* modules to connect and manipulate databases. This tutorial assumes you have at least basic SQL skills and that you know how to import modules and have moderate perl syntax knowledge.. All code in this tutorial was tested using a Win32 and Unix machine, both running Apache as the webserving package and MySQL as the database package.

=== Installing DBI === Installing a module is a very simple thing to do on most systems, but each system uses a different process. I'm going to explain this process the long way so that everyone will be able to do it the same way. If you already have DBI then you can skip this section. Note: Macintosh systems have a seperate section.

For *nix and Windows First we need to d/l the makefile and classes for DBI. So go to search.cpan.org and search for DBI. Find DBI::BUNDLE in the list of results. Download this bundled module. Unzip it and put it in a folder. Get into the command prompt of your os, a shell for unix, dos prompt for windows. Navigate to the folder in which you unzipped the files. Execute makefile.pl. This will prepare the makefile. Now type make or nmake for windows. Now, type make install, or nmake install for windows. Now, type make test, or nmake test for windows. Your installation of DBI should be complete, but to be sure type the following into your prompt

perl -MDBI -e "1;"

If this prints out anything, it will be an error message which means that something is broken. If it just executes quietly without any error messages, you've got yourself a working DBI installation! :)

For Macintosh Systems I will not cover installation on MacOS before OS X. For MacOS X, simply install the developer tools that came with your distribution of MacOS. Then follow the *nix instructions to install the module.

=== Get the Database Library ===

Now it's time to get the needed library of code for the database you're going to interact with. These modules all hold the prefix DBD:: If you already have the appropriate module for your database backend, you can skip this section. The first step to get the library, is to go back to search.cpan.org and search for DBD:: When the list of modules comes up, look through it until you find the appropriate module. Download this module's package and follow the instructions for installing DBI.

You've setup the database library.

=== Confirm Settings ===

Now it's time to make sure that the database is working correctly. Go into a graphical client or a prompt and check that everything is working ok, and then create a database called test. Create a user to access this database and grant them the appropriate priviliges.

=== Connecting and Disconnecting ===

At the top of the script be sure to 'use' the DBI module. From this point on, in every script you create you will be expected to already have the shebang line and 'use DBI;' in place.

To connect to a database, you need the following details:-

• The name of the database you want to connect to
• The server the database is hosted on
• A username and password for accessing the database server.

Once you have these, you call the Connect function in the DBI module, which returns a database connection object. We often call this $dbh, standing for "DataBase Handle". The syntax to do this is:-

$dbh = DBI->connect("DBI:mysql:db_name:dbserver", "dbuser", "dbpass");

The first parameter is called the DSN. Note that if the database sever is on a different port to the norm, you have to tag that on the end, e.g. if it was on port 2134 you'd put:-

$dbh = DBI->connect("DBI:mysql:db_name:dbserver:2134", "dbuser", "dbpass");

Want to test if it has worked? One simple check is just to do this:-

print $dbh;

If it has connected, something will be printed that has the word HASH at the start and then a series of numbers in brackets.

To disconnect, you simply call the disconnect function in the database connection object you have created.

$dbh->disconnect;

=== Running Resultless Queries === Simply query the database object as follows

$dbh->do("INSERT INTO $database VALUES ('$value1',$value2)");

You can use other queries such as drop, insert, create, or any other query that does not return a result. Be sure to use proper escaping of any quotes, I find this is my most common error when writting DB code.

However, when using input taken from users that are not trusted, it is suggested you use the following technique:

my $sth = $dbh->prepare("DROP TABLE ?");
$sth->execute($blah);
$sth->finish();

This will insert the first variable into first question mark, second variable into second question mark, etc. This is preferable to variable substituion performed by perl because DBI will escape the variables for anything that could influence the query.

This can also work with multiple question marks. The following example is extracted from a real program I worked on several days ago.

my $sth = $dbh->prepare("UPDATE users SET name=?,pass=?,email=?,avatar=?,profile=?,superadmin=?,post_without_approval=?,edit_all_posts=? WHERE uid=?");
$sth->execute($name,$pass,$email,$avatar,$profile,$supera_f,$postwa_f,$editap_f,$uid);

Each argument given to execute corresponds to it's mate in the prepare statement. Once again, DBI will escape this. Some people prefer to make this faster by writting their own complex auto-escape systems. This can be very worthwhile by not only increasing speed and enabling customizability, but also teaching you may new aspects of pattern matching and perl in general.

=== Running Queries That Return Results ===

Running the queries that return results is significantly different than running the resultless queries. Obviousely, you want your results back :)

So, how would one get to pick up results from a SELECT type query?

First of all, you need to prepare the statement. It means that you will tell DBI that you are about to run a complex statement and that DBI should examine it. This process also enables you to use bind values, but more on that later.

To prepare a statement, you would do something like this:

$sth = $dbh->prepare("SELECT * FROM some_table");

In this case ''$sth'' stands for statement handle, and it's an object that contains your statement. Once you have prepared your statement, you can execute it multiple times. To execute it, you would do this:

$sth->execute();

=== Getting your results from an executed query ===

When taking data out from an executed statement, you always fetch it row by row. There are three ways to get a data for a row:

1. Data as array
2. Data as an array reference
3. Data as a hash reference

This tutorial will only cover the first method, as it is the most commonly used and the most useful in most cases.

The first method, returning the data as an array, can be done very simply. Simply say

@results=$sth->fetchrow_array();

this will take the first row it found and put the results in the array specified. If you have more than one row you can use a while statement to parse through them, since each subsequent call to fetchrow_array() will bring back the next row it found. Here is an example of this:

while (@results=$sth->fetchrow_array()) {
print @results."\n";
}

You may now continue to process this data as you will.

=== Conclusion ===

In this tutorial you have learned how to use the DBI module and it's child DBD modules to create a database interactive perl application. In the future, I intend to write an Advanced DBI article which will include the other methods of fetching data, several examples of complex data processing systems, and a number of other helpful hints for optimizing as well as implementing database interactivity within perl.


[Edit this page]  [Page history]  [What links here]  [Discuss this topic]  [Printer Friendly]