asterisk users - Mar 2008 - AEL - SQL and TIMEDIFF()

Hello list,

I'm having some problem integrating the SELECT TIMEDIFF() and SELECT
DATEDIFF() in my code. The syntax I'm using works without any problems if I
run them directly from the MySQL Client, but from the Asterisk Dialplan it
just wont work. Is there a limitation in the MySQL() application for the
Asterisk dialplan that produces this error?

<CODE>
context testsql {
  s => {
    MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB});
    MYSQL(Query resultid ${connid} SELECT TIMEDIFF(callend,callstart) FROM
tblCall WHERE id=7);
    MYSQL(fetch fetchid ${resultid} temp);
    MYSQL(Disconnect ${connid});
  }
}
</CODE>

The error I'm getting is below:
[Mar  6 08:59:35] WARNING[27116]: app_addon_sql_mysql.c:268 aMYSQL_query:
aMYSQL_query: mysql_query failed. Error: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near ') FROM tblCall WHERE id=7' at line 1

Has anyone done this kind of calculation before, or is there a better way to
do it?

Thanks,
Best regards,
Tobias
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.digium.com/pipermail/asterisk-users/attachments/20080306/dec2d350/attachment.htm

> context testsql {
>   s => {
>     MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB});
>     MYSQL(Query resultid ${connid} SELECT 
> TIMEDIFF(callend,callstart) FROM tblCall WHERE id=7);
>     MYSQL(fetch fetchid ${resultid} temp);
>     MYSQL(Disconnect ${connid});
>   }
> }
> </CODE>
> 
> The error I'm getting is below: 
> [Mar  6 08:59:35] WARNING[27116]: app_addon_sql_mysql.c:268 
> aMYSQL_query: aMYSQL_query: mysql_query failed. Error: You 
> have an error in your SQL syntax; check the manual that 
> corresponds to your MySQL server version for the right syntax 
> to use near ') FROM tblCall WHERE id=7' at line 1
I think the solution would be to escape the , with a backslash, so 
your query would look like this:
SELECT TIMEDIFF(callend\,callstart) FROM tblCall WHERE id=7

Maybe even the brackets ()....

-- 
Andreas Sikkema