Sunday, September 30, 2012

Connecting to PostgreSQL via Perl on Debian Linux


I installed libdbd-pg-perl which was the Perl DBI driver for the PostgreSQL database server. 
Then, I connected to PostgreSQL via Perl using the Perl script below:

#!/usr/bin/perl -w

# load module
use DBI;
use strict;


# connect
my $d = DBI->connect("DBI:Pg:dbname=d;host=/tmp", "postgres","passwordYouLike", {'RaiseError' => 1});

my $rows = $d->do("CREATE TABLE a (id SERIAL, a TEXT, PRIMARY KEY(id))");
print "$rows row(s) affected\n";

# clean up
$d->disconnect();



I stopped the remote access to the local PostgreSQL database. The connection was made via a socket, not TCP/IP. To do this, the host was /tmp, instead of 127.0.0.1.

I wrote the SQL statements in the Perl script to create a table in a database. After that, I inserted the data:

my $a = 'Do you think that PostgreSQL works?';
my $rows = $d->do("INSERT INTO a (a) VALUES ('$a')");
print "$rows row(s) affected\n";

Next, I displayed the data:

my $sth =  $d->prepare("SELECT * FROM a");
$sth->execute();


Using Perl to create the table at the beginning
I noticed that if I did not use Perl in this way, and I created the table via command line, I would not be able to use the table via Perl. That meant that I should create the table and insert the data via Perl at the beginning, rather than via the command line.

The version of the PostgreSQL database I used was 9.2.1.

No comments:

RSS