Just somewhere to keep my notes while I'm playing.

Thursday, July 10, 2008

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$

No comments: