Listed on

Perl on BSD talking to MS-SQL Server

A lot of broadcast monitoring equipment seems to use Microsoft SQL Server as backend for storing alerts and other data. As we use a FreeBSD based monitoring box it proved necessary to find a way of accessing these servers via ODBC.
The solution is relatively simple, but confusingly documented. The following worked example should help make the documentation clearer.

We chose to install libiodbc and DBD


cd /usr/ports/databases/libiodbc

make install

cd /usr/ports/databases/freetds

make config



Select the IODBC and MSDBLIB options


cd /usr/ports/databases/p5-DBD-ODBC

make install



If you setup a /usr/local//etc/freetds.conf file like:


[DBNAME]

host = 10.0.1.3

port = 1433

tds version = 8.0



The you can access the database via the tsql command. Eg:


tsql -S DBNAME -U username -P password


To access the DBNAME database located on the MS SQL server on 10.0.1.3; SQL commands are executed when the GO command is entered on a line by itself.

To get it all to work with Perl:


#!/usr/bin/perl

use DBI;

my $server=’10.0.1.3’;

my $port=1433;

my $db=‘DBNAME’;

my $user=‘username’;

my $pw=‘password’;

my $DSN = “Driver=/usr/local/lib/libtdsodbc.so;’”

“Server=$server;Port=$port;Database=$db;UID=$user;PWD=$pass;”.

“TDS_VERSION=8.0;”;

my $dbh = DBI->connect("dbi:ODBC:$DSN", {RaiseError=>1, odbc_cursortype=>2});



Simple really. Note that the contents of /usr/local//etc/freetds.conf are only important for tsql.