Asterisk + Perl AGI + Database Issue


#1

Hello everyone,
I’m having some troubles with an AGI script I created to select then update a table in MYSQL.
I need to select data from mysql then compare with a number and then update another table with this number I compared.
See the script:

#!/usr/bin/perl -wT
use warnings;
use DBI();
use Asterisk::AGI;
$AGI = new Asterisk::AGI;

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

$id = $ARGV[0];


#print "Digite o uniqueid: \n";
#chomp ($id = <STDIN>) ;

if (! open FILE, ">>/home/felipe/log.txt") {
        die "Nao abriu o arquivo" . "\n" ;
}

#Dados para acesso ao banco de dados
$dbh = DBI->connect("DBI:mysql:qstats:localhost", "root", "password") or die print $DBI::errstr;
$dbh->{RaiseError} = 1;

# Executando query
$cmd = "SELECT qagent FROM queue_stats WHERE uniqueid like $id and qevent=4" ;
$sth = $dbh->prepare($cmd);
$sth->execute();
$result = $sth->fetchrow_array;

print FILE "$result - Esse foi o retorno do banco" . "\n" ;

$sth->finish;
$dbh->disconnect();

if ($result eq 20) {
        print FILE "20!!!\n" ;
}

elsif ($result eq 2) {
        print FILE "7!!!\n" ;
}

else {
        sleep (5);
        $dbh = DBI->connect("DBI:mysql:asteriskcdr:localhost", "root", "password") or die print $DBI::errstr;
        $dbh->{RaiseError} = 1;
        $dbh->do(q/UPDATE cdr SET userfield = ? WHERE uniqueid like ?/,undef,299,$id);
        $sth->finish();
        $dbh->disconnect();

        print FILE "Foram afetados " .$sth->rows . " registro(s) \n";
}


close FILE;

If the select query returns 20 I will print something, if returns 2, i will print another thing, and if returns another number, i will update the table ‘cdr’ with a number.

Perl returns no erros in logs neither in the Asterisk CLI.

Here is the example of the mysql log:

The select query

111109 17:04:59     445 Connect     root@localhost on qstats
                    445 Query       set autocommit=1
                    445 Query       SELECT qagent FROM queue_stats WHERE uniqueid like 1320865474.3486 and qevent=4
                    445 Quit

And the update

111109 17:05:04     446 Connect     root@localhost on asteriskcdr
                    446 Query       set autocommit=1
                    446 Query       UPDATE cdr SET userfield = '299' WHERE uniqueid like '1320865474.3486'
                    446 Quit

Well, but in fact the table is not being updated. And the select query returns me nothing.
Take a look at the log file “/home/felipe/log.txt” which returns the result of database query:

 - Esse foi o retorno do banco
Foram afetados 0 registro(s)

It affected no rows.

The point is that if I run this command outside AGI/Asterisk enviorment, it works pretty fine.

So, how could you help me?

Perl version: 5.10.0
Distro: Debian Lenny
Asterisk: 1.6.2.13

Thanks!!!