Beginning Perl Tutorial - Installing and Using MySQL

Connecting to the MySQL database

Now that we have a database to connect to, let's look at how we access the information from our Perl programs. The following is a simple script that connects to the database and runs a short bit of SQL to retrieve all the samples.

#!/usr/bin/perl -w
use DBI;
$dbh = DBI->connect('dbi:mysql:perltest','root','password')
or die "Connection Error: $DBI::errstr\n";
$sql = "select * from samples";
$sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print "@row\n";
}

The first thing we need to do is let the Perl interpreter know that we intend to use the Perl DBI function library, by including it in our program with the use function.

use DBI;

Next we'll need to create a connection to the database itself. For this part of the script, your perltest database will need to be running.

$dbh = DBI->connect('dbi:mysql:perltest','root','password')
or die "Connection Error: $DBI::errstr\n";

The connect function contains the name of the database, and your username and password used to connect. This might be different depending on your setup.

$dbh = DBI->connect('dbi:mysql:DATABASE_NAME', USERNAME, PASSWORD)

The die option provides an alternative to the program simply not working if a connection is not established. Basically, the connect it tried, and if it fails, your script will die and display an error message that should help you debug. Once we've established a connection to the MySQL database, we will need to create a string of SQL and then prepare it to query the database.

$sql = "select * from samples";
$sth = $dbh->prepare($sql);

Next we query the database with our prepared SQL query, or exit the program and display some debugging information if the MySQL query fails to execute.

$sth->execute
or die "SQL Error: $DBI::errstr\n";

Finally we use the fetchrow_array function to fetch each row of the results from the MySQL database and print them one to a line.

while (@row = $sth->fetchrow_array) {
print "@row\n";
}

If the program is successful, you should see the following output:

1 Some Person 555-5555
2 Another Person 222-2222

Previous: Creating a simple database