Connecting to a Database

To read or write a plain file in Perl, you need to open a file handle. In the same way, to access a DBI database in Perl, you need to create a database handle. A database handle is what you use to maintain a connection to a database and is how you reference a particular connection to a database when you need to call functions or make SQL queries. It is possible to have more than one database handle in a program; this means you can connect to more than one database or even to more than one database server. The servers you connect to don't even need to be on the same continent! However, due to current connectivity restrictions, the database servers do need to be on the same planet. Database handles are normally referenced by a variable named $dbh or by something similar such as $dbh_accounting.

Let's take a look at a program that connects to some data sources, grabs a bit of data, prints it, and disconnects. Not all data bases require disconnecting, but to make truly portable code, you should always call disconnect.

01: #!/usr/bin/perl -w 02: # 03: # connect_multi.pl 04: # Chapter 2 05: # Listing 2

Line 1 tells the system where to find Perl and turns warnings on with the -w switch. Warnings provide some extra diagnostic messages that are helpful when writing applications.

Lines 2-5 are simply comments about this program.

06: use strict; 07: use DBI;

Line 6 loads the strict module. This module enforces more stringent programming rules than coding without using strict. Using strict is highly recommended because it can prevent many simple programming mistakes that are hard to debug-such as misspelling a variable name.

Line 7 loads the DBI module; we need this because it provides us with an interface for database connectivity.

08: my $dbh_m =         DBI->connect("DBI:mysql:quizzer:dbserver.perlguy.net",        "bookuser",        "password")  09:     or die("Cannot connect: $DBI::errstr"); 

Line 8 is split up a bit so that it looks nicer and is easier to explain.

The first part of line 8 declares a new variable named $dbh_m. This is going to stand for DataBase Handle to MySQL.

The next part of line 8 calls the connect method of the DBI. The connect method needs a few things passed to it-the Data Source Name, username, and password.

Line 9 is a continuation of line 8. This line causes the program to die (abort) if there was an error connecting to the database.

Below are two sample connect statements. The first connect statement contains all of the options/attributes you could pass. The second connect statement contains a minimal amount of options/attributes while still being a valid connect statement.

Containing everything: my %attr => ( RaiseError => 0 ); $dbh =  DBI->connect("dbi:mysql:dbname=BibleBook:host.hair.net:1023",     "username", "password", \%attr)      or die("Error: $DBI::errstr"); Containing the minimum: $dbh->connect("dbi:mysql:BibleBook", "username", "password")     or die("Error: $DBI::errstr");