Asterisk + Freetds +MSSQL 2000

If you are anything like my surely you like to steer clear of all things NOT opensource (i.e. Microsoft) althought I got to admit i have always had a soft spot for Solaris (mmm thinking about it I don't were they stand these days in OpenSource world...) but there comes time that it is unavoidable to work with some of this stuff.

One of the things I love about linux is that it will handle all/most things you throw at it and MSSQL Server integration is one of those, here is my recipe:

1. freetds , an open source Tabular Data Stream implementation. TDS is the protocol that databases such as Sybase and MSSQL "speak", there are many versions of it, MS SQL 2000 version is TDS 8.0
2. DBD-Sybase Perl Module, although the name can seem misleading, since Sybase and MSSQL both "speak" TDS it allows the Perl DBI driver to work with both. As of this writing the latest version is 1.07

In this post I am asuming you already know how to work with AGI scripts, we will use Asterisk::AGI Perl module in this examples. I will devote a post for this later on.. for now you might want to check this out.

Ok lets start cooking.

freetds part
#tar -xzvzf freetds-stable.tgz
# cd freetds-0.64
#./configure --prefix=/usr/local/freetds --with-tdsver=8.0 (we will be installing inito /usr/local/freetds dir, and since we will be using it for MSSQL we us version 8.0 of TDS)
#make
#make install

DBD-Sybase part
#tar -xvzf DBD-Sybase-1.07.tar.gz
#cd DBD-Sybase-1.07
#export set SYBASE=/usr/local/freetds
#export set LD_LIBRARY_PATH (this is REALLY really really important step, it will save you dealing with the Dynaloader.pm error which took me a LONG LONG LONG time to figure out... i wish i had this recipe in my hands a few years ago...)
#./configure
#make
#make install

Ok, now I will throw out a little example on how to use this with Asterisk::AGI this next program connects to MSSQL and checks if the callerid of the calling party is a client.

alright aaaall together now:

#!/usr/bin/perl
use Asterisk::AGI;
use DBI;
$AGI = new Asterisk::AGI;
my %input = $AGI->ReadParse(); #Read in the initial data
#Connect to MSSQL Server
$user = "user";
$passwd = "password";
$dbh = DBI->connect("DBI:Sybase:server=10.1.34.0", $user, $passwd, {PrintError => 0});
$dbh->do("use Clients");
unless ($dbh) {
die "Unable for connect to server $DBI::errstr";
}
$AGI->verbose("Checking IF client",1);
$sql = "select count(*) from clients where ClientPhone=\"$input{callerid}\"";
$sth = $dbh->prepare($sql);
$sth->execute();
while ( my @row = $sth->fetchrow_array( ) ) {
$isclient=$row[0];
}
$sth->finish();
$dbh->disconnect();
if ($isclient > 0)
{
$AGI->verbose("$input{callerid} is a client!!",1);
$AGI->stream_file('you are a client');
}
else
{
$AGI->verbose("$input{callerid} is not a client!!",1);
$AGI->stream_file('you are not a client');
}



Pretty neat huh? I thought so!! Good luck!