By Michael Marr
Expert Author
Article Date: 2010-07-13
Perl is often a strong counterpart to databases because of its ability to parse and manipulate large blocks of text efficiently. When databases are storing this type of data, utilizing Perl can alleviate some of the heaving lifting that SQL or another language might have to do in order to make certain operations on that data.
Ideally, we'll want to connect to the database in way that is friendly with multiple databases. Luckily, Perl's Database Interfance (DBI) handles that for us. Learning the basic operations of how to connect, prepare, execute, and retrieve using DBI will allow us to work on any DBI supported database.Let's start with connecting to a mySQL database:
use DBI;
my $dbh = DBI->connect("dbi:mysql:generic_db_name",
"username", "password");
if ( defined $dbh )
{
# do database manipulation here
} else {
# database not connected - display/handle errors here
}
The $dbh variable will now store the database handler, from which all the future operations will be performed. You can call your database handler variable whatever you please, and should certainly use something more descriptive when working with multiple databases in the same application. If there are any issues with connecting to the database, $dbh will be set to undef.
After a successful connection to the database, the next step is to prepare the SQL statement:
my $sth = $dbh->prepare("SELECT * FROM table1");
For more complex SQL queries, it is best to use a HEREDOC format for preparing SQL statements, as it improves readability:
my $sth = $dbh->prepare(< 100
ORDER BY field2 DESC
SQL
The $sth variable holds the prepared SQL, on which the execute method is ran:
$sth->execute;
Once executed, the returned results will be stored and can be accessed with the fetchrow_array method:
while (@row = $sth->fetchrow_array()) {
# @row = row of results
}
