Perl - Database Connectivity
In a Nutshell - CIW Course Section 2, Part B3, Chapter 5
Overview of Database Connectivity
There are three components that make up the database connectivity of Perl. The Database Management Library (DBM) is the top level and is generally provided in the standard Perl build. Below this is the Database Interface Module (DBI) and this is the basic abstraction layer for working with databases in Perl. Finally, we have the Database Driver Module (DBD) which provides the low-level interoperability with a specific database.
Both the DBI and DBD tend to be required for a specific database type. The most common database for Web site use is likely to be MySQL as this is open-source and, often, free. The course notes use MySQL in the example code. In order to run the sample scripts you will need to install the appropriate DBI and DBD modules onto your Perl installation.
Unfortunately, I was unable to manage a successful installation of these modules so cannot offer any advice on how best to achieve it. The first method I attempted, involved using the CPAN module with the following command line:
c:\> perl -MCPAN -e shell
which start the CPAN interface. This can be very long winded, when first run, as it goes through endless settings and options. Once started you enter the following commands:
cpan> install Bundle::DBI
cpan> install Bundle::DBD::mysql
This all looks fine and rosy but, it didn't work for me. So, not to be outdone I started looking around and found a promising link on the MySQL site. This offered to download all the required modules in a TAR archive. It didn't however include instructions. I took my best guess, and failed again. At this point I gave up. PHP is much easier to use anyway.
Getting at the Data
The first thing to do is to connect to the database. The example in the course notes uses the following syntax:
$dbh = DBI->connect("dbi:mysql:databasename");
I immediately asked myself the question: "How do you identify the server?". It seems that I was being unobservant, as a closer reading of the notes explained that the last parameter is actually the Datasource Name (DSN) which will point to the appropriate server. A fuller version pf this syntax would be:
$dbh = DBI->connect("dbi:mysql:dsn", "Username", "Password");
Having successfully connected to the database, we now want to return some data. It is most likely that the database will use some form of Structured Query Language (SQL) as MySQL certainly does.
$sth = $dbh->prepare("SELECT * FROM MyTable");
$sth->execute();
The prepare method parses and validates the SQL statement, but does not return any data. The execute method is invoked from the statement handle that the prepare method returned to actually perform the query.
The data is retrieved into a hash or an array structure depending on the method employed.
@rowArray = $sth->fetchrow_array();
$rowArrayRef = $sth->fetchrow_arrayref();
$rowHash_Ref = $sth->fetchrow_hashref();
Each of these methods will return one row of records from the database. Invoking the method again will return the next row. Building the method into a loop will allow all records to be returned.
print @rowArray[0];
print $rowHashRef->{fieldname};
How to print or use the records will depend on the method used to retrieve them.