[HOWTO] Accessing MSSQL with perl in Ubuntu/Debian

Have you ever had the need to access a MSSQL database from Ubuntu/Debian via perl? Well, it’s rather easy once you have the right tools. All you need is to install a few things, edit a config file and off you go. Just follow the below instructions and you should go good to go.

sudo aptitude install freetds-bin libdbi-perl libdbd-sybase-perl

This install the required programs and modules needed for perl to access your MSSQL server. Now you have to edit the freetds config file.

sudo editor /etc/freetds/freetds.conf

Add the following:

[hostname]
host = xxx.xxx.xxx.xxx
port = 1433
tds version = 7.0

Now for the final touch, the actual perl code:

#!/usr/bin/perl -w
#
# Sample MSSQL connect script
#
use strict;
use warnings;

use DBI;
use DBD::Sybase;
use Data::Dumper;

# Hostname to MSSQL server (same as [hostname] in freetds.conf)
my $hostname = "hostname";
# Name of database to use
my $database = "db_name";
# Username used to connect with
my $username = "user123";
# Password used for connection
my $password = "qwerty123";

my $dbh = DBI->connect("dbi:Sybase:server=$hostname;database=$database",
$username,
$password,
{RaiseError => 1, AutoCommit => 1})
or die ("Can't connect to database $database on $hostname: " . $DBI::errstr . "\n");

# Replace this with your SQL statement
my $sql=(q/SELECT * FROM some_table/);
my $sth = $dbh->prepare($sql);
$sth->execute();

# We use Data::Dumper to show the fetched data
print Dumper($sth->fetch());

# Now we clean up after ourselves
$sth->finish();
undef $sth;
$dbh->disconnect();

That should be it! Happy SQLing with perl and MSSQL 🙂