Database query in dialplan

CastB · Post by **CastB** » Mon Mar 14, 2011 9:49 am

1. Brekeke Product Name and version: Sip Server 2.4.7.3/286.1

2. Java version: 1.6.0_17

3. OS type and the version: 2.6.18-194.32.1.el5.centos.plus

4. UA (phone), gateway or other hardware/software involved:
X-lite on windows7

5. Select your network pattern from http://www.brekeke-sip.com/bbs/network/ ... terns.html : Sip Server is in the public and both softclients are in the same LAN behind NAT

6. Your problem: Databse query in dialplan does not work

I created a test dialplan with a database query as it is explained in the BKK wiki, however the query gets never executed.

MySQL db:
-------------
a mysql database "mydb" with a table "bbb" and column "aaa"

In Configuration -> Advanced i add the following rules
---------------------------------------
mydb.driver = com.mysql.jdbc.Driver
mydb.url = jdbc:mysql://domain/serverdb
mydb.user = user
mydb.password = password

Matching pattern
----------------
$Request=^INVITE
$db.query( "mydb", "select aaa from bbb where 1=1") = (.+)

Deploy pattern
---------------
$response=603

I also checked the log from the MYSQL server but there is never a attempt to do something with the query.

It seems BKK does not execute the query, what am i doing wrong?

Thanks.

hope · Post by **hope** » Mon Mar 14, 2011 11:49 am

does the query "select aaa from bbb where 1=1;" work if run it from mysql server db?
Is "mydb" your database name?
is there mysql db driver copied at sip server\webapps\proxy\WEB-INF\lib\?

mydb.url = jdbc:mysql://domain/serverdb
if serverdb is your database name, change all mydb in advanced settings to serverdb
and need to restart sip server when change settings at advanced tab.

CastB · Post by **CastB** » Tue Mar 15, 2011 3:07 am

Hi Hope,

> does the query "select aaa from bbb where 1=1;" work if run it from mysql server db?
Yes

> Is "mydb" your database name?
Yes

>is there mysql db driver copied at sip server\webapps\proxy\WEB-INF\lib\?
Yes:
/usr/share/tomcat6/webapps/ROOT/WEB-INF/lib/mysql-connector-java-5.1.14-bin.jar
This one is used for all sip --> db connecties (user, register db etc)

>mydb.url = jdbc:mysql://domain/serverdb
> if serverdb is your database name,
Actually it is: mydb.url = jdbc:mysql://domain/mydb (i made a typo in the forum post)

Is there a way to see if BKK tries to execute the query? I tried setting the log level (net.sip.loglevel.file=255) but cannot find any evidence in the logs

hope · Post by **hope** » Thu Mar 17, 2011 2:41 pm

check sv.xxx.x.log with the date you test call
at tomcat\webapps\proxy\WEB-INF\work\sv\log\2011\03
is there any error or exception about mysql?

put mysql-connector-java-5.1.14-bin.jar to tomcat\webapps\proxy\WEB-INF\lib\
restart tomcat

if there is any other dial plan set at sip server, put the one with database access on the top

CastB · Post by **CastB** » Fri Mar 18, 2011 4:25 am

Hi,

> put mysql-connector-java-5.1.14-bin.jar to tomcat\webapps\proxy\WEB-INF\lib\
> restart tomcat

> if there is any other dial plan set at sip server, put the one with database access on the top

done, but still same problem

> check sv.xxx.x.log with the date you test call
> at tomcat\webapps\proxy\WEB-INF\work\sv\log\2011\03
> is there any error or exception about mysql?

No, there are no errors or exceptions in the logs.
We do use a mysql database also for users and registrations and that works fine (configured via configuration/database in bkk).

It seems like the db.query rule in the dialplan never gets executed.
Am i perhaps missing some modules or is this perhaps not supported in the evaluation version?

Thanks

hope · Post by **hope** » Fri Mar 18, 2011 2:41 pm

does "select aaa from bbb where 1=1;" return anything if run from database directly?

change log level
http://wiki.brekeke.com/wiki/log-SIP-packets
and check if the dial plan is used from sv.xxxx.log

Haddas · Post by **Haddas** » Wed Mar 23, 2011 12:16 pm

Which edition of Brekeke SIP Server are you using?

The "$db.query" function is available for Brekeke SIP Server Advanced edition only.

CastB · Post by **CastB** » Wed Mar 23, 2011 2:56 pm

> does "select aaa from bbb where 1=1;" return anything if run from database directly?
Yes

> change log level and check if the dial plan is used from sv.xxxx.log
We have sip, registrar and tcp loglevel at 255 but not any message from this dialplan in the log.

>Which edition of Brekeke SIP Server are you using?
Sip Server 2.4.7.3/286.1 We are still on the evaluation version. Is it not available in the evaluation?

lakeview · Post by **lakeview** » Thu Mar 24, 2011 1:00 pm

You can request Brekeke SIP Server Advanced Edition Trial.
http://www.brekeke.com/company/company_ ... ss-adv.php

Standard evaluation doesn't have "$db.query" method.

CastB · Post by **CastB** » Mon Mar 28, 2011 1:04 pm

That explains a lot

We just ordered the advanced license

Thanks all for the help.

lakeview · Post by **lakeview** » Tue Mar 29, 2011 10:15 am

good luck