A Perl Script that accesses MySQL

Last year I posted a simple Perl script that accesses a PostgreSQL database using the DBI connector. I decided that I should do the same for MySQL. I'm using Active State Perl on Windows XP. Amusingly, this version does not include the MySQL connectors as default, but it's easy enough to add using perl ppm (Perl's Perl Package Manager).

I want a simple script that lists out artists and their CDs in my database.

#!/usr/bin/perl
use strict;
use DBI();
my $dbh1 = DBI->connect("DBI:mysql:database=cddb;host=localhost","uuuu", "xxxxxx", { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
 print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

D:\Documents and Settings\timj\perl>c:\Perl\bin\perl cd.pl
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

That's about as simple as it get, I'm sure that there is far more to it than that, but this will do for now!
Tim

Accessing a database via Perl

Here's a small program I wrote to access a Postgresql database.

#!/usr/bin/perl
use DBI;
$dbh1 = DBI->connect( "dbi:Pg:dbname=testdb@localhost","", "", { RaiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";

my $sql = "select vendor, country, freephone, payphone from numbers ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $vendor, $country, $freephone, $payphone );
$sth->bind_columns( \$vendor, \$country, \$freephone, \$payphone );

print "Vendor\tCountry\tFreephone\tPayphone\n";
while( $sth->fetch() ) {
print "$vendor\t$country\t$freephone\t$payphone\n";
}

my $sql = "select name, vendor, leader, participant from myuser ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $vendor, $leader, $participant );
$sth->bind_columns( \$name, \$vendor, \$leader, \$participant );

print "\nName\tVendor\tLeader\tParticipant\n";
while( $sth->fetch() ) {
print "$name\t$vendor\t$leader\t$participant\n";
}

$dbh1->disconnect() if($dbh1);

The output looks like this:

timj@dell-desktop:~/MySync/db$ ./plsql_phones1.pl
Vendor Country Freephone Payphone
Verizon UK 44-20-7075-3246 0808-238-6025
Verizon France 33-1-70-70-74-20 080-563-9647
Verizon USA 1-203-418-3122 866-692-3163
Verizon India 000-800-852-1266

Name Vendor Leader Participant
Tim Verizon 1234567 1234567
timj@dell-desktop:~/MySync/db$