Database query in dialplan

CastB · Posted: Mon Mar 14, 2011 9:49 am Post subject: Database query in dialplan

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/networkpatterns.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 · Brekeke Master Guru Joined: 15 Jan 2008 Posts: 862

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 · Posted: Tue Mar 15, 2011 3:07 am Post subject:

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 · Brekeke Master Guru Joined: 15 Jan 2008 Posts: 862

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 · Posted: Fri Mar 18, 2011 4:25 am Post subject:

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 · Brekeke Master Guru Joined: 15 Jan 2008 Posts: 862

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 · Brekeke Guru Joined: 17 Jan 2008 Posts: 170

Which edition of Brekeke SIP Server are you using?

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

CastB · Posted: Wed Mar 23, 2011 2:56 pm Post subject:

> 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 · Posted: Thu Mar 24, 2011 1:00 pm Post subject:

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

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

CastB · Posted: Mon Mar 28, 2011 1:04 pm Post subject:

That explains a lot Smile

We just ordered the advanced license

Thanks all for the help.

lakeview · Posted: Tue Mar 29, 2011 10:15 am Post subject:

good luck