How to Use a MySQL Database in a Perl Program

A Brief Introduction to Accessing MySQL from Perl Applications

Jul 19, 2009

How to Use a MySQL Database in a Perl Program, Mark Alexander Bain

A MySQL database will always form a suitable backend for any application, and the Perl programmer will find that this can be achieved very easily.

When it comes to accessing a MySQL from a Perl application the surprise is not that it can be done, the surprise is just how easily it can be done. There are no additional modules required. All that's needed is:

Perl
MySQL

And, if this is to be a web based application, then the programmer will need a web server as well.

Getting the Perl Script Ready for Use

If this script is to be used on a web server (such as Apache) then there are two essential lines that the script will require. These are:

the shebang line (this gives the location of the Perl executable)
the HTTP header line

This may sound complicated, but the lines are very simple, for example:

#!c:/strawberry/perl/bin/perl

print "Content-type: text/html\n\n";

Any results can now be shown in a web browser.

Setting the Perl Parameters for the MySQL Database Access

The application will require the details of a suitable user account. This, of course, should not be the root account:

$user = "db_user";

$password = "db_password";

It will also need to know the name of the database to be used:

$db = "business_rules";

In this example the Perl code will be using the MySQL stored function described in How to Use Business Rules in a MySQL Database.

Locating the MySQL executable

This technique involves the Perl code calling the MySQL executable from the command line. If that is include in the system path variable then the actual location is not required:

$mysql = "mysql";

If not then then full path should be included:

$mysql = "\"C:/Program Files/MySQL/MySQL Server 5.1/bin/mysql\"";

In this case there are spaces in the path and so literal quotation marks (denoted by \") have been used to avoid any possible confusion.

Creating a SQL Statement with Perl

Perl will send a SQL statement to the database and then be required to deal with any results, and so the SQL statement is stored in a Perl variable:

$sql = "select business_rule('new') 'New Status'";

In this example stored function will be run on the database, but any suitable SQL statement can be use (and it's always a good idea to test the statement on the database before hard coding it into the application).

Running a SQL Statement with Perl

The command that is to be run is built from the variables that have been set so far:

$command = "$mysql -H -e \"$sql\" -u$user -p$password $db";

It's worth considering the flags used in this command string:

-H - output the results in a HTML table
-e - the SQL statement to be run on the database
-u - the user's id
-p - the user's password

The programmer then uses the Perl qx operator to run the command, and the results are loaded into a final Perl variable:

$results = qx/ $command /;

print $results;

At the end of this short process the results can then be displayed in the web browser (as shown in Figure 1), showing just how easy it is for a Perl programmer to create a script that can work with the contents of a MySQL database.

The copyright of the article How to Use a MySQL Database in a Perl Program in Database Programming is owned by Mark Alexander Bain. Permission to republish How to Use a MySQL Database in a Perl Program in print or online must be granted by the author in writing.

How to Use a MySQL Database in a Perl Program

Figure 1: The Output from a MySQL Query

Print Article

View All Articles

How to subscribe to feeds