Perl and PostgreSQL Introduction

PostgreSQL is a database that can be used in conjunction with Perl to create desktop, web and console based applications. It can be used to store data, as well as configuration information. Perl, together with SQL, can be used to query the data stored within a PostgreSQL database, as well as insert, update and delete the data.

Connecting to a PostgreSQL Database

In order to access data within a PostgreSQL database using Perl, a connection to the database must first be established. To facilitate this, the 'DBI' module needs to be utilised. This is included with Strawberry Perl, but may need to be installed for other distributions. Once available, a 'use' statement will need to be include in order to utilise the module.

use strict;
use warnings;
use DBI;

# Database variables.
my $driver = "Pg";
my $database = "demo";
my $dsn = "DBI:$driver:dbname = $database; host = localhost; port = 5432";
my $userid = "DemoUN";
my $password = "DemoPW";

# database connection variable.
my $connect;

eval
{

    # Connect to database.
    $connect = DBI->connect($dsn, $userid, $password, 
                            {RaiseError => 1});

    # Message confirming successful database connection.
    print "Database connection successful.\n";

} 
or do 
{

    # Message confirming unsuccessful database connection.
    print "Database connection unsuccessful.\n";

    # Stop program execution.
    exit(1);

};

In this example, an ‘eval-or-do’ block is used to catch any exceptions that may arise in connecting to the database. An ‘eval-or-do’ block can be used to handle any exceptions in a user friendly manner. The ‘exit(1)’ statement, after the confirmation of an unsuccessful database connection, stops execution of the program completely, so no further statements are executed.

It should be noted that it isn’t necessary to have the database information in separate variables before using it in the database connection. It much depends on personal preference and whether the variables are going to be re-used elsewhere in the program. The database connection variable can be re-written as follows.

use strict;
use warnings;
use DBI;

# database connection variable.
my $connect;

eval 
{

    # Connect to database.
    $connect = DBI->connect("DBI:Pg:dbname = demo; host = localhost; port = 5432", 
                            "DemoUN", "DemoPW", {RaiseError => 1});

    # Message confirming successful database connection.
    print "Database connection successful.\n";

} 
or do 
{

    # Message confirming unsuccessful database connection.
    print "Database connection unsuccessful.\n";

    # Stop program execution.
    exit(1);

};

Perl and PostgreSQL Introduction

Connecting to a PostgreSQL Database

More