[asterisk-users] Authorize & Microsoft SQL

[Gregory Malsack]

I would like to thank everyone for their input. This project is completed and the solution is working wonderfully! There was some mention of some people having difficulties with asterisk/mssql connectivity via the dialplan when under heavy load. Running the connection through agi was the suggested way they resolved this issue. With this in mind, I decided best to learn for other experiences. Here is what I have done to accomplish this goal:


1. Installed freetds
2. Compiled php with the --with-mssql=/usr compile flag. This provided php with the ability to connect to a mssql database.
3. I wrote a php script and placed it in the /var/lib/asterisk/agi-bin folder.
4. Configured the dialplan to call the script via the agi command and send the user entered information as an argument.

Everything is working perfectly. Below are the dialplan and php scripts used. The dialplan is not polished, but for testing purposes does what I want it to do.

Again, thanks to everyone for the help and ideas! Happy Holidays!

Greg



extensions.conf

exten => _NXXXXXX,1,Playback(please-enter-the)
exten => _NXXXXXX,n,Read(account|access-code|10)
exten => _NXXXXXX,n,Set(CDR(accountcode)=${account})
exten => _NXXXXXX,n,agi(validate.php,${account})
exten => _NXXXXXX,n,gotoif($["${VALIDCASE}" = "1"]?godial)
exten => _NXXXXXX,n,Playback(astcc-account-number-invalid)
exten => _NXXXXXX,n,goto(1)
exten => _NXXXXXX,n(godial),dial(SIP/${EXTEN}@sip.broadvoice.com,30,wW)
exten => _NXXXXXX,n,congestion()
exten => _NXXXXXX,n+101, busy()



/var/lib/asterisk/agi-bin/validate.php (must be chmod 755)

#!/usr/local/bin/php
<?php
$server = '<sql-server name or IP>:<sql-server port default 1433>';
$case = $argv[1];
$link = mssql_connect($server, '<username>', '<password>');
$query = 'SELECT casenumber,casename from tblcase where casenumber = \'' . $case . '\'';

if(!$link)
{
    die('Something went wrong while connecting to MSSQL');
}

$search = mssql_query($query);
$data = mssql_fetch_array($search);

if($data[1])
{
        fwrite(STDOUT, "SET VARIABLE VALIDCASE \"1\" \n");
        fflush(STDOUT);
}
else
{
        fwrite(STDOUT, "SET VARIABLE VALIDCASE \"0\" \n");
        fflush(STDOUT);
}

?>

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.552 / Virus Database: 270.9.19/1859 - Release Date: 12/20/2008 2:34 PM