How To: Use Perl to Connect to a Database

Note: The article assumes that the reader has a fundamental understanding of at least one other programming language and Perl. This is intended serve as a quick reference for Perl's input/output of data to databases.

Once you've mastered the basics of Perl, you might want to start moving data into or out of a database. I'm going to give the basics of how to read and write from just about any databases. I will try to provide enough information to a point where you can make queries with SQL and execute any SQL statement you may have.

Warnings

• One assumption that I make is that the reader knows how to use SQL or knows how to figure out SQL.
• For this how-to, I'm going to use MySQL. The concepts will be very similar to other databases, but with different modules loaded for the specific database application.

Tips

• Depending on the version of Perl installed on the system, instillation of database modules may need to be done.
• Perls DBI should be installed with most versions of Perl.
• Installation of modules for Oracle (DBD::Oracle) or MySQL (DBD::mysql) or any other database, may still be needed depending on which version of Perl is installed. Check the Comprehensive Perl Archive Network (CPAN) for the specific DBD module needed. Check with your version of Perl for instructions on how to install these modules.

Database Usage

Starting with this table, called myperl in mySQL:

The code that can be used to call up this data and display it would look like:

Code Begin

#!/usr/bin/perluse strict;use warnings;

use DBI;
use DBD::mysql;

#DB variables
my $host = "localhost";
my $db = "test";
my $user = "user";
my $pwd = "password";

#connection to the Database
$db = DBI->connect("DBI:mysql:$db;UID=$user;PWD=$pwd;" ) or die $DBI::errstr;

#Prepare SQL statement to be run aginst database
my $sql = $db->prepare('Select * from myperl');
#Run the SQL query
$sql->execute;

#Grab a row from result set
my @row = $sql->fetchrow_array();

do
{
    #Display First and Last Name
    print "$row[1] $row[2]\n";
    
    #Grabs next row of result set
    @row = $sql->fetchrow_array()
} while(@row);

#Disconnect from database
$db->disconnect;

Code End

This code will display the data as:

This is the basic code for connecting to a database. It should be easy enough to figure out how to run any SQL statement that you may need to run against the database.

Tips

• For connections to MS SQL, it would look something like:

$db = DBI->connect("dbi:ODBC:Driver={SQL Server}:$db;UID=$user;PWD=$pwd;" ) or die  $DBI::errstr;

• For connection to an Access Database, the connection would look something like:

my $dbLocation = 'C:\test.accdb';
my $connectAccess = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$dbLocation ";
my $dbAccess = DBI->connect("dbi:ODBC:$connectAccess")  or die $DBI::errstr;