MySQL II : Perl

Using MySQL With Perl

(See MMS4 595)

Once upon a time you had to jump through some hoops to set up Perl for MySQL. Now you can simply install the perl-DBD-MySQL package with Fedora 4 or later.

Before we start, let’s be clear about a couple of things, namely single-quotes and double-quotes.

->Single-quotes are strong quotes or literal quotes. Code inside them won’t run. Variables inside them won’t be expanded (interpreted).

->Double-quotes are weak quotes. They have only one purpose in life: to preserve spaces. That’s it. So if you want a fragment of code to run, or if you want a variable’s value to be inserted, use weak quotes.

Got it?

Learn By Example: A Perl Script

(See MMS4 617)

This initial example is adapted from Larry Ullman’s MySQL.

#!/usr/bin/perl
#The preceding line is always necessary, in exactly this format.
#There must be NO SPACE between the “#!”
#and the “/usr/bin/perl”.
#There must be NO SLASH after the “/usr/bin/perl”
# – a trailing slash would
#indicate that perl is a directory, but it’s not
# – it’s a binary file,
#namely the perl interpreter.
#Also note that trailing spaces on this line
#will break your script,
#and that every line must end with a semicolon ” ; ” .

use strict;
#Yes, you’re always going to use this.

use DBI;
#You just added database support to this script.

my @drivers = DBI->available_drivers();
#This declares an array ( @ ) called “drivers”,
#(a non-array variable would start with “$”)
#and populates it by running the available_drivers()
#function.

foreach (@drivers) {
print $_.”\n”;
}
#Note this structure carefully:
#for each element in the array “drivers” do the actions
#within the braces:
#print out that element (referred to by the special
#variable $_, which always holds whatever you’re
#currently working with), concatenated using the dot
#character, followed by a new-line character, \n

Now save this as a plain-text file called test.pl . In a command window, in the directory containing this file, set its permissions:

chmod 700 test.pl

Then run it:

./test.pl

If all things are right, you’ll get output listing all the available DBI drivers on your system.

Now let’s connect to a database:

#!/usr/bin/perl
use strict;
use DBI;

#Make the user give us a user name:
print “User name: “;
my $username=<STDIN>;
#Take the user name from Standard Input
#i.e. the keyboard.

#Password, please:
print “Password: “;
my $password=<STDIN>;

$dsn=”DBI:mysql:stock_app:localhost”;
#This puts the “data source name” string into a single variable,
#(denoted by “$”) $dsn, to make the next line simpler. The
#syntax is: use the DBI module: mysql_driver:
#using the database_name:database_host_name.

$dbh=DBI->connect(“$dsn”, “$username”, “$password”, {RaiseError=>1});
#The syntax is this: Create a variable $dbh (“database handle”)
#and populate it by running the DBI connect() function,
#our $dsn value, $username value, and $password value,
#with error reporting if the returned value is greater that 0,
#which indicates success.

#Did it work?
if ($dbh) {
print “We got a connection! \n”;
} else {
die “Sorry, the connection failed. \n”;
#If the database handle exists (“if ($dbh)”), then
#print the success message.
#Otherwise, kill the script.

#Ask the user what new stock symbol to enter:
print “New stock symbol: “;
my $symbol=<STDIN>;

#And get a corresponding company name:
print “New company name: “;
my $name=<STDIN>;

#Now let’s create some SQL:
my $sql=qq{INSERT INTO Stock (stock_symbol, name)
VALUES ($symbol, $name)};
#Use QueryQuotes! qq{} takes care of
#properly single- and double-quoting.

#And run an insert query:
my $query=$dbh->do($sql);

#This should return the number of rows
#affected, namely 1. If it doesn’t, let us know:
if ($query==1) {
print “$name inserted. \n”;
} else {
print “$name was not inserted. \n”;
}

#That was easy; now we’ll do a more complex
#query, a Select. First we define the SQL:
$sql=qq{SELECT name FROM Stock WHERE stock_symbol = $symbol};

#Next, we run the Prepare step:
#(Yes, this is new.)
$query=$dbh->prepare(“$sql”);

#And check that it works:
if (defined($query)) {
$query->execute();
} else {
print “The darn thing didn’t work. \n”;
}

#Now we look at the returned results:
while (@row=$query->fetchrow_array()) {
print “$row[1]\n”;
}
#Which is, while there are rows in the @row array,
#fetch each one, then print the value in column 1.
#To print all columns, use:
#print “$_ \n”;

#Finish up nicely:
$query->finish();

#Once we’re done, disconnect:
$dbh->disconnect();

Save this as queryscript.pl,

chmod 700 queryscript.pl

and run it:

./queryscript.pl.