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 at http://www.mysql.com/doc/en/Perl.html.
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;
