Author |
Message |
CastB Brekeke Addict
Joined: 05 Feb 2011 Posts: 32
Location: the Netherlands
|
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. |
|
Back to top |
|
hope Brekeke Master Guru
Joined: 15 Jan 2008 Posts: 862
|
Posted: Mon Mar 14, 2011 11:49 am Post subject: |
|
|
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. |
|
Back to top |
|
CastB Brekeke Addict
Joined: 05 Feb 2011 Posts: 32
Location: the Netherlands
|
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 |
|
Back to top |
|
hope Brekeke Master Guru
Joined: 15 Jan 2008 Posts: 862
|
Posted: Thu Mar 17, 2011 2:41 pm Post subject: |
|
|
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 |
|
Back to top |
|
CastB Brekeke Addict
Joined: 05 Feb 2011 Posts: 32
Location: the Netherlands
|
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 |
|
Back to top |
|
hope Brekeke Master Guru
Joined: 15 Jan 2008 Posts: 862
|
Posted: Fri Mar 18, 2011 2:41 pm Post subject: |
|
|
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 |
|
Back to top |
|
Haddas Brekeke Guru
Joined: 17 Jan 2008 Posts: 170
|
Posted: Wed Mar 23, 2011 12:16 pm Post subject: |
|
|
Which edition of Brekeke SIP Server are you using?
The "$db.query" function is available for Brekeke SIP Server Advanced edition only. |
|
Back to top |
|
CastB Brekeke Addict
Joined: 05 Feb 2011 Posts: 32
Location: the Netherlands
|
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? |
|
Back to top |
|
lakeview Brekeke Master Guru
Joined: 15 Nov 2007 Posts: 319
Location: Florida
|
|
Back to top |
|
CastB Brekeke Addict
Joined: 05 Feb 2011 Posts: 32
Location: the Netherlands
|
Posted: Mon Mar 28, 2011 1:04 pm Post subject: |
|
|
That explains a lot
We just ordered the advanced license
Thanks all for the help. |
|
Back to top |
|
lakeview Brekeke Master Guru
Joined: 15 Nov 2007 Posts: 319
Location: Florida
|
Posted: Tue Mar 29, 2011 10:15 am Post subject: |
|
|
good luck |
|
Back to top |
|
|