Introduction

Since I am currently exploring the intricacies of developing an asp.net web app and have nothing terribly interesting to show for it today I will be delivering some information that I found useful on a past project.

Perl DBI

I won’t go into the history of the DBI module for perl, but sufficed to say it is a database interface module that keeps you from having to mess with socket programming. All you really need to know is that when you need to access a database server, DBI is probably the module for you. Today we will be talking specifically about the MySQL database definition for DBI.

Getting started

Getting started we will set up the recommended stuff at the top of the file and import the DBI library. NOTE: You will need to make sure that whatever installation of perl you are running has the MySQL DBD installed. In CentOS this is accomplished with:

yum install perl-DBD-MySQL

[sourcecode language=”perl”] #!/usr/bin/perl -w

perl-DBD-MySQL must be installed

use strict; use warnings; use DBI; [/sourcecode]

Declare some useful variables

I like to keep as many of my variable declarations at the top of the file as possible. This makes it easier to switch database servers easily for instance.

[sourcecode language=”perl”]

variable definition

my $dbuser = “dbuser”; my $dbpass = “dbpass”; my $db = “db”; my $dbhost = ‘localhost’; [/sourcecode]

Connecting to the database

We will now define our database connection. So far this is pretty standard stuff you can find in most guides. The connection is called a database handler commonly abbreviated dbh.

[sourcecode language=”perl”]

connect to the database

my $dbh = DBI->connect(“DBI:mysql:$db:$dbhost”, $dbuser, $dbpass) || die “Could not connect to database: $DBI::errstr”; $dbh->{PrintError} = 1; # do this, or check every call for errors [/sourcecode]

Prepare some queries

This is where we begin to diverge from most of the guides available. This example is good if you need to do some extensive work with database data. It is not the shortest way to write, but it is one of the most efficient. We will prepare some database statements which will save some computing time later on. These prepared statements are called statement handlers and are commonly abbreviated sth. We are working with 3 tables all of which have an id column and a tablename column.

[sourcecode language=”perl”]

prepare some queries we are likely to use

note the use of q{} to protect the quotes in the statement

my $sthSelectRecords = $dbh->prepare(q{ SELECT * FROM table1; });

my $sthInsertRecord = $dbh->prepare(q{ INSERT INTO ? VALUE (?,?); }); [/sourcecode]

Execute query

In this example code our objective is to select some records from the database, process them, and then perform another query based on that information. Here we execute the initial query. Note that for any given database connection we can have an unlimited number of prepared statements and we can execute them in any order or even while another is already running. This is the fact that most guides do not make clear. We are going to take advantage of this in a moment.

[sourcecode language=”perl”] #execute query $sthSelectRecords->execute; [/sourcecode]

Bind the column names to a hash

I just about fell out of my chair when I found this next piece. Ok, so I didn’t really almost fall out of my chair, but I was very impressed. As it turns out the most efficient way to handle large numbers of records is to return an array of references to the location of the row data. What this means is that instead of copying all the rows data to another variable, we just get back the addresses of all the data. What this statement does is take that array of arrays and turn it into an array of hashes with the keys of the hash being the column names of the database. The end result being that it is ridiculously easy to reference data out of the array as we will see in a moment.

[sourcecode language=”perl”] #bind column names to a hash my %Record; $sthSelectRecords->bind_columns( ( @Record{ @{$sthSelectRecords->{NAME_lc} } } )); [/sourcecode]

Loop through the results

Not much to say here.

[sourcecode language=”perl”] while ($sthSelectRecords->fetch) { [/sourcecode]

Check the value and perform some operation based on it

Here is the long-awaited example of what it looks like to run multiple queries.

[sourcecode language=”perl”] if ( $Record{tablename} eq “table2” ) { $sthInsertRecord->execute(“table2”, $Record{id}, $Record{tablename}); $sthInsertRecord->finish; } elsif ( $Record{tablename} eq “table3” ) { $sthInsertRecord->execute(“table3”, $Record{id}, $Record{tablename}); $sthInsertRecord->finish; } } [/sourcecode]

Clean up

Finish off the statement and disconnect from the database.

[sourcecode language=”perl”] $sthSelectRecords->finish; $dbh->disconnect(); [/sourcecode]

Hopefully guide has been useful for you. If not, sorry.