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:
- 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
- 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
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!
