1. Install Perl http://www.activestate.com/

If you had already installed Perl, please add DBI and DBD-mysql packages.

C:\>ppm

and select the two packages to install.

2. Install Apache and configure httpd.conf

2.1 Install

http://httpd.apache.org/download.cgi

2.2 Configure httpd.conf

C:\Program Files\Apache Software Foundation\Apache2.2\conf\httpd.conf

To use CGI outside of ScriptAliased directories, you need to do:

Options Indexes FollowSymLinks ExecCGI

AddHandler cgi-script .pl

2.3 Test the server: http://localhost

Your default document root is C:\Program Files\Apache Software Foundation\Apache2.2\htdocs

3. Install mysql and create DB

3.1 Install

http://dev.mysql.com/downloads/

3.2. Create DB

C:\> mysql -u root -p

mysql> show databases;

mysql> create database example;

mysql> source C:\example.sql

example.sql:

CREATE TABLE `member` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`ui_locale` varchar(5) NOT NULL default ‘en’,
`country` varchar(30) NOT NULL default ‘US’,
`points` int(11) NOT NULL default ‘0′,
`installtime` int(11) unsigned NOT NULL default ‘0′,
`lastaccessed` int(11) unsigned NOT NULL default ‘0′,
`status` smallint(1) NOT NULL default ‘1′,
PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Accessing DB with Perl

4.1 Getting a DB handle

sub get_db_handle {
my $driver = “mysql”;
my $server = “localhost”;
my $database = “example”;
my $url = “DBI:$driver:$database:$server”;
my $user = “root”;
my $password = “”;
my $dbh = DBI->connect( $url, $user, $password ) or die “Could not connect to db ” . DBI->errstr;
return $dbh;
}

4.2 Useful DBI functions

my $dbh = &get_db_handle;

//? will be later substituted by 1 => $sth->execute(”1″);

my $query = “select * from $table where ?”;

//prepare

my $sth= $dbh->prepare_cached($query) or die “could not prepare - $query” . $dbh->errstr;

$sth->execute(”1″);

//# of record sets returned

if($sth->rows > 0) {

//fetching a row at a time as hash ref

while (my $result = $sth->fetchrow_hashref()){

// print ‘id’
print “ID: $result->{id} <br/>”;
//Different ways to fetch rows

@result = $sth->fetchrow_array();

$result = $sth->fetchrow_arrayref();

//Enabling Transaction

- Create your tables with ENGINE=InnoDB

$dbh->{’AutoCommit’} = 0;

Do some transactions;

If all succeeded, do $sth->commit();

If no, do $sth->rollback();

Or,

$dbh->do(”START TRANSACTION”);

$dbh->do(”COMMIT”);

//More commands

$sth->finish();

$dbh->disconnect();

eval { $dbh->do(”DROP TABLE foo”) };
print “Dropping foo failed: $@\n” if $@;

$dbh->do(”INSERT INTO foo VALUES (1, ” . $dbh->quote(”Tim”) . “)”);

//Column names

my $names = $sth->{’NAME’};

my $numFields = $sth->{’NUM_OF_FIELDS’};

More info:

http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm

http://search.cpan.org/~timb/DBI/DBI.pm

http://dev.mysql.com/doc/