[Asterisk-Users] Intergrate with MySQL

[Roderick Montgomery]

According to Ajit Kallingal:
> Hello All,
> Can the current Asterisk be integrated with mySQL to query a database ?
> I am looking at a  typical IVR scenario where the user punches a "product
> code" and the database query will determine if the product is available or
> not. The reply would be number of items available , else none.

Sure, something like the following AGI script would work. Just grab the Perl
AGI module from <URL:http://asterisk.gnuinter.net/>, drop the following in
your extensions.conf...

exten => s,1,Answer
exten => s,2,Wait,1
exten => s,3,AGI,script.agi

...and put the following as "script.agi" in /var/lib/asterisk/agi-bin:

---------------------------------------------------------------
#!/usr/bin/perl

use Asterisk::AGI;
use DBI;

my $AGI = new Asterisk::AGI;
my %input = $AGI->ReadParse();

### Play "welcome.gsm", greeting the caller 

$AGI->stream_file('welcome');

### Play "product-code-prompt.gsm", instructing the caller to enter the 
### product code, then allow ten seconds to enter four-digits

$prodcode = $AGI->get_data('product-code-prompt',10000,4);

$quantity = &quan_by_code($prodcode);

if ($quantity != 0) {
	$AGI->stream_file('there-are');
	$AGI->say_digits($quantity);
	$AGI->stream_file('available');
} else {
	$AGI->stream_file('none-available');
}

$AGI->stream_file('goodbye');
$AGI->hangup;
exit;




sub quan_by_code {
### Takes a product code as input, then returns the quantity available.
        my $code = shift;
        my $dbh = open_connection();
        my $sql = "SELECT code, quantity FROM product WHERE code=\'$code\' LIMIT 1";
        my $sth = $dbh->prepare($sql);
        $sth->execute or die "Unable to execute SQL query: $dbh->errstr\n";
        my $row = $sth->fetchrow_arrayref;
        $sth->finish;
        $dbh->disconnect;
        if ( ($code == $row->[0]) && ($code != 0) ) {
                return $row[1];
        } else {
                return 0;
        }
}

sub open_connection {
        my $dsn = "mysql:dbname:localhost";
        my $username = 'dbusername';
        my $password = 'dbpassword';

        return DBI->connect("DBI:$dsn",$username,$password) or die $DBI::errstr;
}
---------------------------------------------------------------


Of course, you'll need to record prompts and responses for:
  welcome
  product-code-prompt
  there-are
  available
  none-available
  goodbye


Hope this get you started,
rm
-------------------------------------------------------------------------
 Roderick Montgomery   rod at thecomplex.com   <URL:http://thecomplex.com/>
the fool stands only to fall, but the wise trip on grace... [Sarah Masen]
-------------------------------------------------------------------------