Step1.
Download & install MySQL.
mysql-essential-5.1.36-win32.zip
Download Link: http://download.cnet.com
Step2.
Setting a root password for MySQL
1. Start your command line by going to the Start Menu > Run and typing cmd (or type command if you are using an older version of windows)
2. Change directory to where you installed mysql to:
C:\> cd C:\mysql\bin
3. Switch to mysql command line:
C:\mysql\bin> mysql -u root mysql
4. Then set a default password:
mysql> SET PASSWORD FOR root@localhost=PASSWORD(‘newpass’);
where “newpass” is the password you want to use
Adding more users
Start your command line by going to the Start Menu > Run and typing cmd (or type command if you are using an older version of windows)
Change directory to where you installed mysql to:
C:\> cd C:\mysql\bin
Switch to mysql command line (if you have not set a root password remove the -p switch when you type it in):
C:\mysql\bin> mysql -u root -p mysql
Then then add your new user:
mysql> GRANT ALL PRIVILEGES ON *.* TO rachel@localhost IDENTIFIED BY ’summer’;
where “rachel” is the username and “summer” is the password you want to use. You can also limit users to specific database, allow only certain remote hosts to connect all using the GRANT statement. However, that is outside the scope of this tutorial so search for more info on using GRANT if you are interested in those features.
- from www.ricocheting.com
Step3.
let’s create a database called perltest and in that database, we will create a simple table called samples and populate it with some data. Here is the SQL you’ll need to create the table and fill in a few records, just connect to your MySQL database and run them.
CREATE DATABASE perltest; USE perltest; CREATE TABLE samples ( id int(10) unsigned NOT NULL auto_increment, name varchar(128) NOT NULL default '', phone varchar(128) NOT NULL default '', PRIMARY KEY (id) ); INSERT INTO samples VALUES (1, 'Some Person', '555-5555'); INSERT INTO samples VALUES (2, 'Another Person', '222-2222');
Step4.
Sample Script
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:mysql:perltest','root','myroot')
or die "Connection Error: $DBI::errstr\n";
my $sql = "select * from samples";
my $sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
while (my @row = $sth->fetchrow_array) {
print "@row\n";
}
Dissection.
$dbh = DBI->connect('dbi:mysql:DATABASE_NAME', USERNAME, PASSWORD)</blockquote>
The die option provides an alternative to the program simply not working if a connection is not established. Basically, the connect it tried, and if it fails, your script will die and display an error message that should help you debug. Once we’ve established a connection to the MySQL database, we will need to create a string of SQL and then prepare it to query the database.
$sql = "select * from samples"; $sth = $dbh->prepare($sql);
Next we query the database with our prepared SQL query, or exit the program and display some debugging information if the MySQL query fails to execute.
$sth->execute or die "SQL Error: $DBI::errstr\n";
Finally we use the fetchrow_array function to fetch each row of the results from the MySQL database and print them one to a line.
while (@row = $sth->fetchrow_array) {
print "@row\n";
}
If the program is successful, you should see the following output:
1 Some Person 555-5555 2 Another Person 222-2222
- from perl.about.com
[Tips]
DBD-mysql
I run the script using ActivePerl 5.10.1 on Winxp.
The DBD-mysql package didn’t installed by default. You need install it via Perl Package Manager.
More about DBD-mysql
A MySQL driver for the Perl5 Database Interface (DBI)
Version: 4.011
Released: 2009-04-14
Author: Patrick Galbraith