asterisk users - Mar 2003 - Intergrate with MySQL

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.

Thanks and Regards
Ajit

On Thu, 2003-03-13 at 14:26, Ajit Kallingal wrote:
> 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.
You can write an AGI app and have your app do the queries to the
database where your connection method is totally up to you. This is
probably prefered as perl and/or other languages will let you do better
error handling if your connection is down to the database.
-- 
Steven Critchfield  <critch@basesys.com>

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@thecomplex.com   <URL:http://thecomplex.com/>
the fool stands only to fall, but the wise trip on grace... [Sarah Masen]
-------------------------------------------------------------------------