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?

(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.

MySQL II : MS Access

Using MySQL With Microsoft Access

rpm -q mysql-connector-odbc

If you don’t, the easiest solution is to use the Fedora CDs to install it. (See your instructor.)

Another solution is to download and install the ODBC Connector directly from mysql.com. See this document: Installing MyODBC from a Binary Distribution on Unix, http://dev.mysql.com/doc/refman/5.0/en/myodbc-unix-binary-installation.html, which covers both tarball and RPM binaries.

(If you are using MySQL on a Windows server, be sure to read “Installing MyODBC from a Binary Distribution on Windows” at http://dev.mysql.com/doc/refman/5.0/en/myodbc-windows-binary-installation.html.)

MySQL II : Sample Users

Now we’ll need some sample users for our continuing process. You’ll be setting up these users:

herbie (password ‘hacker’)

carrie (password ‘cracker’)

arnold (password ‘admin’)

yulia (password ‘user’)

fred (password ‘freeloader’)

george (password ‘good’)

yanni (password ‘user’)

Setting them up manually, one at a time, is silly. Use a script. Download this file: users.sql

Put it in a convenient place, then open it in a text editor and take a look. Then, open the mysql client, and run (source) it:

\. users.sql

What results do you get? Why?

Correct any problems in this script until it will run properly. You may need to take a look at this page: http://dev.mysql.com/doc/refman/5.0/en/grant.html.

Finally, implement the privileges (within the mysql client):

FLUSH PRIVILEGES;

MySQL will not enable your changes until you perform this step.

An alternate way to flush privileges is to exit the client and type:

mysqladmin –u root –p reload

MySQL II : Sample Data

It’s time to populate a sample database. We’re going to use some examples and materials from the article “Stored Procedures, Views and Triggers” by Jay Pipes and Michael Kruckenberg, in the December 2005 edition of Linux Magazine. Take it from me: you should subscribe.

1. Get these files and put them in your home directory:

Listing1.sql

Listing2.sql

Listing6.sql

Look at these files in your browser first, to see what’s going on. Later you can view them using a text editor, more, less, cat, or your preference.

2. Enter the mysql client interface as your new mysql user:

mysql -u glenn -p

3. Now run (“Source”) the scripts: (See MMS4 102 ff., esp. 104)

\. Listing1.sql

\. Listing2.sql

\. Listing6.sql

What happens? Why?

4. Exit mysql and log back in as (mysql) root.

Now run the files. What happens this time?

Now you’ve got some data to work with.

MySQL II : Initial Configuration

1. Before starting, install the MySQL servers, clients and development libraries. Run these queries, and if necessary, install the related packages (these commands are case-sensitive!):

rpm -q mysql
rpm -q mysql-server
rpm -q MySQL-python
rpm -q perl-DBD-MySQL
rpm -q php-mysql

All these packages should be installed. Use the Fedora CDs to add any that you need.

When you install from the Fedora CDs or RPMs, the mysql user and group will be created automatically, and directory permissions will be pre-set for you.

2. Set the root user’s password for MySQL: (See MMS4 7)

mysqladmin -u root password “private”

I will be using, as a default, the password “private” for all root connections. See the mysql/mysqladmin commands page as a reference.

4. Create a user configuration file for root: (See MMS4 269 ff.)

vi .my.cnf
or
gedit my.cnf &

Then in vi or gedit:

[client]
password=’private’

5. Create your own (Linux) user:

useradd glenn
passwd glenn
usermod -G mysql glenn

6. Create your own (MySQL) user. (See the users page for a refresher.) Enter the mysql monitor and command:

GRANT ALL ON *.* TO ‘glenn’@’localhost’ IDENTIFIED BY ‘public’;
#Don’t forget that semicolon! And do see the security page.

7. Applying Created Privileges: The Step You Must Not Forget

Use mysqladmin:

mysqladmin -u root -p flush-privileges
or
mysqladmin -u root -p reload

If you omit this step the new permissions will not be applied.

8. Log out as root and log back in as yourself.

9. Create your user configuration file (see 4 above).

10. Modify your PATH:

cd
vi .bash_profile

Find the PATH variable, and to the end add: /usr/bin. It should look like:

PATH=$PATH:/usr/bin

11. Log out again, then log in as yourself again.

12. Test your ability to use the mysql and mysqladmin commands.

MySQL II : Intermediate MySQL

Welcome to Intermediate MySQL at UNM Continuing Education (65252 , Section FFA).

Your Instructor: Glenn Norman
email: glenn at (@) g norman dot (.) org
12/12/2005-12/16/2005
M-W-F
8:00 AM – 12:00 AM

Text: Mastering MySQL 4, Ian Gilfillan, Sybex 2003, ISBN 0-7821-4162-5
Note that throughout the course I will be referring to pages in Mastering MySQL 4 like this:
(See MMS4 x)
where “x” will be a page number.