XClose

Information Services Division

Home
Menu

Calling MySQL from Perl

MySQL databases can be accessed by Perl programs running on the Web server. They can therefore be used as CGI scripts. The MySQL Perl API is currently not available on any of our Sun Solaris systems. The MySQL Perl API is mainly provided for those fluent in the Perl language and familiar with the use of CGI scripts, or if you have an off-the-peg application that needs Perl/MySQL. If you are not familiar with Perl or PHP, we recommend using PHP, which gives much better error diagnostics. Note that, for security reasons, all CGI scripts have to be approved by Information Systems before they can be installed.

The API is documented in the on-line MySQL manual. Documentation is also available in the Unix man pages man Mysql and man DBD::mysql.

This example script displays the second and third fields (numbered 1 and 2) of each row of table people in database ucabwww where field age is greater than 30:

#!/usr/local/rbin/perl
use DBI;

print <<END;
Content-type: text/html

<html>
<head>
<title>Example of Perl calling MySQL</title>
</head>

<body bgcolor="white">

END

# database information
$db="ucabwww";
$host="mysql-server.ucl.ac.uk";
$userid="ucabwww";
$passwd="secret";
$connectionInfo="dbi:mysql:$db;$host";

# make connection to database
$dbh = DBI->connect($connectionInfo,$userid,$passwd);

# prepare and execute query
$query = "SELECT * FROM people WHERE Age > 30 ORDER BY Name";
$sth = $dbh->prepare($query);
$sth->execute();

# assign fields to variables
$sth->bind_columns(\$ID, \$Name, \$Age);

# output name list to the browser
print "Names in the people database:<p>\n";
print "<table>\n";
while($sth->fetch()) {
   print "<tr><td>$Name<td>$Age\n";
}
print "</table>\n";
print "</body>\n";
print "</html>\n";

$sth->finish();

# disconnect from database
$dbh->disconnect;