Perl DBI Tutorial (The Basics)

Having just finished a huge MySQL backed intranet project, various people have emailed asking for help using the Perl DBI, and some even suggested that I write a tutorial, which as I finally had some free time sounded like a good idea. This page is the result.

Covered here are the very basic steps needed to, connect to the database server, test the connection, create a new table, add data to it, edit data stored and finally perform simple searches. Now whats not covered in any more detail than is needed in order to understand the examples, is the inner workings of Perl or things like SQL syntax. Sorry but both of those are such huge subjects that its just not practical to cover them here, without writing what amounts to a book. All is not lost, here are some books from my recommended reading list that should help you with both.

• Programming Perl, Third Edition
• Programming the Perl DBI
• Managing and Using MySQL, Second Edition
• SQL in a Nutshell

Each of the steps covered includes examples of both the Perl code and also where needed any SQL, together with a basic run down of their functions, plus any possible problems you need to be aware of.

Requirements

As this is a Perl tutorial, the main requirement is Perl (no surprise right!), then comes the actual DBI modules, which you can get from cpan.org. Which while on the subject the installation can be a little complex for those who are not that familiar with Perl, so you are strongly recommended to install it via its Bundle which makes life easier by installing the dependencies for you.

Once you have installed the DBI you need to have access to some form of database server to talk to, which in my case is MySQL although you can use more or less anything that has a DBI driver available. Which reminds me, before moving onto the interesting stuff, you need to maker sure you have all that you need to connect to the database server, which usually includes the following. Anyway for all the examples on this page, I assume you are using MySQL.

• Database Server Hostname
• Username
• Password
• Database name

TIP: As I mentioned above, there are DBI drivers available for more or less any database server in use. For more info on whats around, have a look at this page on cpan.org

Some final words about compatibility, I have developed this tutorial using a Linux machine, talking to a MySQL database server also running under Linux. That said if you are using some flavor of UNIX together with MySQL the examples should still work ok.

From this point on, I assume that you have all the requirements setup correctly and all is functional. Sorry but configuration of database servers and perl (DBI) installations is way out of the scope of this document.

Step 1. (Connecting to the Server)

The first thing you need to do is connect to the database server using the DBI, with the connection details for your database server, then once connected create a database handle which we will be using in all the following steps.

Use DBI;
				
my $DataBaseName    =   "test";
my $DataBaseHost    =   "mars";
my $DataBaseUser    =   "tutorial";
my $DataBasePass    =   "password";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { RaiseError => 1,
                                       AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";

Looking at the above code, you will see that I first declare 4 variables which hold the connection details for the server, then move onto calling "DBI->connect" using the values stored in those variables.

moving further down, in order to actually implement some error checking (always a good idea), I have set the "RaiseError" and "AutoCommit" attributes, plus use the perl "die" function to actually report the error, after raising an exception.

Now comes the use of the "die" function, not much to say on that one, its 100% basic perl. When used in this way, its effectively saying "connect ok OR die if there is a error", and when a error occurs, will cause the DBI to raise an exception (exit), writing the error to STDERR, or if you are running via CGI, to the apache error_log.

TIP: As a general rule of thumb connecting to a database server is expensive in terms of resources used, so I usually recommend that you only do this once, at the beginning of your program, then disconnect on exit from your code.

Step 2. (Testing the connection)

Now you have a connection to your database server, its a good move to actually run some tests to confirm that all is well, and that you don't have any permissions or other issues with the server.

Yes, as you can expect there are a more or less unlimited number of ways to test, and to try and cover them all here would be confusing at best. So rather than bore you, the connection test in this case will be simply reading several database handle attributes, for example "mysql_serverinfo" and "mysql_stat". There is not much to this as you can see by the following example, which will first connect to the server, then read both attributes, and finally disconnect.

Use DBI;
				
my $DataBaseName    =   "test";
my $DataBaseHost    =   "mars";
my $DataBaseUser    =   "tutorial";
my $DataBasePass    =   "password";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { RaiseError => 1,
                                       AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";

##################################################################################################
# Read the mysql_serverinfo and mysql_stat database handle attributes
##################################################################################################
my $ServerInfo  =  $DataHandle->{'mysql_serverinfo'};
my $ServerStat  =  $DataHandle->{'mysql_stat'};

print "Server Info: $ServerInfo\n";
print "Server Stat: $ServerStat\n";

##################################################################################################
# Disconnect from the database
##################################################################################################
$DataHandle->disconnect();

Looking at this code, the only parts that need any discussion are those that read the database handle attributes, and finally the disconnection from the database. Starting with the attributes, these are simply read by referencing the database handle "$DataHandle", with the attribute name. Now comes the disconnection, which just like everything else, uses the database handle, but this time calling the "disconnect" method, rather than reading attributes.

Running this example on my system gives the following output to the console. And yes yours may be a little different, the important thing being the lack of reported errors, which means you are at the very least talking to the server ok.

Server Info: 4.0.17-log
Server Stat: Uptime: 661057  Threads: 1  Questions: 862  Slow queries: 0  Opens: 14  Flush tables: 1  Open tables: 8  Queries per second avg: 0.001

Step 3. (Creating your first table)

At this point you have connected to, and verified your connection to the database server, now its time to create your first table, which we will be using for most of the following examples.

Rather than just create the usual boring single field table common to tutorials, we are going to create one that can be used for a very very simple address book, which at a later stage we will be accessing via CGI. Anyway the table is called "table1" has the following structure.

Now there are many ways to create this table, you could use one of the 1001 graphical tools, the MySQL command line interface or use SQL commands from your Perl code. This third option is the way we are going here, as it also serves to show you how to pass SQL commands to the server.

The SQL command that creates this table is shown below. And yes its been spread out over several lines to make it easier to read.

CREATE TABLE table1 (
  id int(5) NOT NULL auto_increment,
  name varchar(40) default NULL,
  phone varchar(40) default NULL,
  email varchar(40) default NULL,
  KEY id (id)
)

This may look a little complex to those not up on SQL syntax, so its worth going through line by line. So from the top.

The first line issues a "CREATE TABLE" command, with the table name set to "table1", the next line creates the first field "id" of data type "INTEGER", length 5, that cannot be blank, and has auto increment enabled. While on the subject the use of auto increment for this field in effect creates a unique identifier for every record, without you having to write code to do this, which believe me makes life much less painful.

Moving onto the next three lines, these create the remaining fields, each with datatype "VARCHAR, length 40, that have no default value, and can be left blank. Now comes the final line, which creates an index on the "id" field which is needed for the auto increment field to function.

Thats the SQL out of the way, now its time to insert this into the previous examples code to actually do the deed and create the table. The code is shown below.

Use DBI;
				
my $DataBaseName    =   "test";
my $DataBaseHost    =   "mars";
my $DataBaseUser    =   "tutorial";
my $DataBasePass    =   "password";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { RaiseError => 1,
                                       AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";
                                       
##################################################################################################
# Create the table using the SQL CREATE command, with the above structure
##################################################################################################                                       
$DataHandle->do("CREATE TABLE table1 (id INTEGER(5) NOT NULL auto_increment,
                                      name VARCHAR(40) default NULL,
                                      phone VARCHAR(40) default NULL,
                                      email VARCHAR(40) default NULL,
                                      KEY id (id)
                                      )");
                                       
##################################################################################################
# Disconnect from the database
##################################################################################################
$DataHandle->disconnect();

Looking at the above code, you can see where the SQL is implemented, called via the "do" method against the database handle created when we connected to the database server. This call to the "do" method, executes the SQL on the server, which in turn creates the table and index. Yes its as simple as that to execute simple SQL via the DBI.

Step 4. (Adding Data)

Time to add some data to your shinny new, and very empty table you just created in the previous step, which is just what we are going to do in this step (no surprise right!), so starting with the needed SQL, here goes.

The very simple SQL needed to add a single record to this currently empty table is shown below.

INSERT INTO table1 values(id,'Fred Bloggs','001 61 2 1234 1234','spam_trap@spam.spam')

Looking at this SQL, it first issues a "INSERT INTO" command against the table "table1", and then inserts the data defined in values, which are listed in the order which the fields where created (id, name, phone and email in this case)

Now before moving onto the Perl code, you will see that the "id" field is named in values, and is not quoted. What this actually does is cause the "id" to auto increment, without you having to worry about manually setting it.

Moving onto the Perl code, here is the code needed to connect to the server, write the record, then disconnect.

Use DBI;
				
my $DataBaseName    =   "test";
my $DataBaseHost    =   "mars";
my $DataBaseUser    =   "tutorial";
my $DataBasePass    =   "password";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { RaiseError => 1,
                                       AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";
                                       
##################################################################################################
# Write a record to the table
##################################################################################################                                       
$DataHandle->do("INSERT INTO table1 values(id,'Fred Bloggs','001 61 2 1234 1234','spam_trap@spam.spam')");
                                   
##################################################################################################
# Disconnect from the database
##################################################################################################
$DataHandle->disconnect();

Looking at the above code, you will see that just like in the previous example, the SQL is implemented using the "do" method referenced against the database handle created when we connected to the server.

TIP: To add more than this single record, just call run the same SQL command multiple times, and no you don't need to connect/disconnect each time. Actually its a good idea to only connect/disconnect once, as its going to save you resources and code complexity.

Step 5. (Editing Data)

Ok, so up to this point, you have connected to the server, verified all is well, created a new table and now written data to this new table, which is all well and good, but what about changing data thats already stored in this table. In this step, we will go through the SQL and Perl needed to update the data written in the previous example.

As always here's the SQL to update the record written in the previous example.

UPDATE table1 SET name = 'New Name' WHERE name = 'Fred Bloggs'

This is about as simple as it gets, it actually does what it says in plain english. ie; update the table named "table1", setting field named "name" to 'New Name' where the existing data in the field "name" is 'Fred Bloggs'

TIP: You can use any valid field inplace of the "name" field in the "WHERE" condition, you do not have to use the same one that you are updating.

Moving onto the Perl code, here is the code needed to connect to the server, write the record, then disconnect.

Use DBI;
				
my $DataBaseName    =   "test";
my $DataBaseHost    =   "mars";
my $DataBaseUser    =   "tutorial";
my $DataBasePass    =   "password";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { RaiseError => 1,
                                       AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";
                                       
##################################################################################################
# Write a record to the table
##################################################################################################                                       
$DataHandle->do("UPDATE table1 SET name = 'New Name' WHERE name = 'Fred Bloggs'");
                                       
##################################################################################################
# Disconnect from the database
##################################################################################################
$DataHandle->disconnect();

Not much to say about the above example, the new SQL is implemented in just the same way as the previous example.

Step 6. (Simple Searching)

yes simple searching, and by simple I mean we are going to go through the SQL and Perl needed to search our table for a specific record and retrieve that record only. With the more complex searches, with "JOINs" and multiple record matches coming in Part 2. So as always starting with the SQL, here goes.

The very simple SQL needed for this type of search is shown below.

SELECT * FROM table1 WHERE name = 'New Name'

Again this does just what it sounds like in plain english. ie; select record from table "table1" when the existing data in the field "name" is 'New Name'.

Now for the Perl code, which this time is slightly more complex than the previous examples.

Use DBI;
				
my $DataBaseName    =   "test";
my $DataBaseHost    =   "mars";
my $DataBaseUser    =   "tutorial";
my $DataBasePass    =   "password";

my $DataHandle      =   DBI->connect("DBI:mysql:database=$DataBaseName;host=$DataBaseHost",
                                     "$DataBaseUser", 
                                     "$DataBasePass",
                                     { RaiseError => 1,
                                       AutoCommit => 0 }) || die "Unable to connect to $DataBaseHost because $DBI::errstr";
                                       
##################################################################################################
# Retrieve the record, when field name = 'New Name'
##################################################################################################                                       
$StatementHandle = $DataHandle->prepare("SELECT * FROM table1 WHERE name = 'New Name'");
$StatementHandle->execute();

$RecordData = $StatementHandle->fetchrow_hashref();

print "NAME:  $RecordData->{'name'}\n";
print "PHONE: $RecordData->{'phone'}\n";
print "EMAIL: $RecordData->{'email'}\n";

$StatementHandle->finish;
                                       
##################################################################################################
# Disconnect from the database
##################################################################################################
$DataHandle->disconnect();

Now you will see that this time the "do" method is not used, this time the "prepare" method is used to prepare the query (no surprise right!) and create the statement handle, which has the "execute" method called against it, to actually run the query on the server.

Yes I did say statement handle, which can be confusing at first site, but all becomes clearer if you thing of this as a "Handle for the query" which is used for all data retrieval, and when finished with, needs to be finished with by calling the "finish method.

Thats covered running the query on the server, now to actually retrieve the record it matches. This is done by calling the "fetchrow_hasref" method against the statement handle. What this does is return a hash containing the record, which can be referenced by field name, which is just what the three "print" statements do, to display the data from the matched record.

TIP: Its important that when you have finished with any statement handle, that you call the "finish" method against the handle. If you don't a exception will be raised. Just think of it as good housekeeping, you use it, so put it away when finished.

Closing Words

yes thats it the end of the first part of this tutorial, so just to recap, to get this far you have, connected to the database server, tested the connection, created a new empty table, written data to it, edited existing data, and finally searched for single records.

Links and Related Pages

• Projects Index
• Sending email from Perl
• Home of Perl DBI
• Home of Cpan (Perl archive)