Perl-MySQL Program
Configuring Perl to work with MySQL is the last part creating a complete Fedora Linux LAMP stack for my students. Perl is already installed on Fedora Linux.
I’ve also shown how to use PHP, Python, and Ruby languages to query a MySQL database on Linux. After installing this additional Perl DBI library, my students will have the opportunity to choose how they implement their LAMP solution.
You can find the Perl version with the following version.pl program:
1 2 3 4 | #!/usr/bin/perl -w # Print the version. print "Perl ".$]."\n"; |
The first line lets you call the program without prefacing the perl program because it invokes a subshell of perl by default. You just need to ensure the file has read and execute privileges to run. It prints:
Perl 5.018004 |
You need to install the perl-DBD-MySQL library to enable Perl to work with MySQL. The following command loads the library:
yum install -y perl-DBD-MySQL |
It prints the following log file:
Loaded plugins: langpacks, refresh-packagekit Resolving Dependencies --> Running transaction check ---> Package perl-DBD-MySQL.x86_64 0:4.024-1.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: perl-DBD-MySQL x86_64 4.024-1.fc20 fedora 142 k Transaction Summary ================================================================================ Install 1 Package Total download size: 142 k Installed size: 332 k Downloading packages: perl-DBD-MySQL-4.024-1.fc20.x86_64.rpm | 142 kB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : perl-DBD-MySQL-4.024-1.fc20.x86_64 1/1 Verifying : perl-DBD-MySQL-4.024-1.fc20.x86_64 1/1 Installed: perl-DBD-MySQL.x86_64 0:4.024-1.fc20 Complete! |
The following item_query.pl Perl program is consistent with the PHP, Python, and Ruby examples provided in other blog posts. It shows you how to use the Perl DBI library to query and return a data set.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | #!/usr/bin/perl -w # Use the DBI library. use DBI; use strict; use warnings; # Create a connection. my $dbh = DBI->connect("DBI:mysql:database=studentdb;host=localhost:3306","student","student",{'RaiseError' => 1}); # Create SQL statement. my $sql = "SELECT item_title FROM item"; # Prepare SQL statement. my $sth = $dbh->prepare($sql); # Execute statement and read result set. $sth->execute() or die $DBI::errstr; while (my @row = $sth->fetchrow_array()) { my $item_title = $row[0]; print "$item_title\n"; } # Close resources. $sth->finish(); |
You call it like this from the present working directory:
./mysql_query.pl |
It returns:
The Hunt for Red October Star Wars I Star Wars II Star Wars II Star Wars III The Chronicles of Narnia RoboCop Pirates of the Caribbean The Chronicles of Narnia MarioKart Splinter Cell Need for Speed The DaVinci Code Cars Beau Geste I Remember Mama Tora! Tora! Tora! A Man for All Seasons Hook Around the World in 80 Days Harry Potter and the Sorcerer's Stone Camelot |
Alternatively, there’s a different syntax for lines 20 and 21 that you can use when you’re returning multiple columns. It replaces the two statements inside the while loop as follows:
20 21 | my ($item_title, $item_rating) = @row; print "$item_title, $item_rating\n"; |
It returns:
The Hunt for Red October, PG Star Wars I, PG Star Wars II, PG Star Wars II, PG Star Wars III, PG13 The Chronicles of Narnia, PG RoboCop, Mature Pirates of the Caribbean, Teen The Chronicles of Narnia, Everyone MarioKart, Everyone Splinter Cell, Teen Need for Speed, Everyone The DaVinci Code, Teen Cars, Everyone Beau Geste, PG I Remember Mama, NR Tora! Tora! Tora!, G A Man for All Seasons, G Hook, PG Around the World in 80 Days, G Harry Potter and the Sorcerer's Stone, PG Camelot, G |
As always, I hope this helps those learning how to use Perl and Linux against the MySQL Database. If you want a nice tutorial on Perl and MySQL, check The tutorialspoint.com web site.
Subscribe via RSS