Perl Database Connection

We make the database connection with the Perl module DBI: The connection requires the data source, the user name and password. We read the connection setings from a global configuration file containing the database hash:

$DB= {
        driver       => "Pg",             
        host         => "localhost",
        port          => "5432",
        database => "db",
        user         => "my",
        password => "myself"
};

This must be readable for the process owner - so be careful with it!

To include the source of configuration file into a Perl module, we have to bypass the scope check for the configuration variables with the keyword use vars qw(...), because we all ways use the Perl strict. This can be done with:

#\!/ust/bin/perl -w

use strict;

BEGIN {
        push(@INC, "../etc/");
        require DBI;
        # some more require staff
}

# We load the configuration file
# 
do 'main.cfg';
require 'main.cfg';
die "FATAL ERROR: Can't read configuration file $!!\n" if $@;

# We connect to the database 
#
use vars qw($DB);
my $ds= "dbi:$DB->{driver}:dbname=$DB->{database};host=$DB->{host}";
my $user= $DB->{user};
my $password= $DB->{password};

my $db= DBI->connect($ds, $user, $password,
        {
                AutoCommit => 0
        }
        )
        or die "FATAL ERROR: Can't connect to database!\n" . DBI->errstr;

# some database staff

$db->disconnect();

1
;

To keep the database handling source code readable we recommand to list all SQL statement in separated subrutines like:

sub check_from_table_table($
# some more) {
   my ($value
      # some more variables
   )= @_;
   my @row= undef;
   $sql= $db->prepare("SELECT id FROM table WHERE value=^$value';");
   $sql->execute();
   @row= $sql->fetch_array();
   return $row[0];
}

sub insert_into_table_table($) {
}

The main program part will call those SQL actions like:

   my $ID;
        if ( ! ($REGION_ID= check_region($REGION, $LANGUAGE_ID)) ) {
                insert_region($REGION, $LANGUAGE_ID);
                $REGION_ID= check_region($REGION, $LANGUAGE_ID);
        }

-- TWikiAdminUser - 2009-11-17