Diaplan db.query not working
Moderator: Brekeke Support Team
Diaplan db.query not working
1. Brekeke Product Name and Version:
3.5.5.2/424-1
2. Java version:
1.8.0_66
3. OS type and the version:
Linux version 3.10.0-327.4.4.el7.x86_64
4. UA (phone), gateway or other hardware/software involved:
Softphone
5. Your problem: db.query not working via MS SQL.
We trying implement dialplan db query register user with external database MS SQL. We not issue connect brekeke SIP server to MS SQL for user registration database but it always return null value when using dial plan db.query registered user entry. The sql statement paste in MS SQL query able retrieve record.
dialplan & logfile as below
Matching Patterns:
$request="^INVITE";
To="sip:(.+)\\\>";
$db.query("dbname", "select username from t_userdir where username='%1'")="(.+)";
sv log:
============================================
Rule [Reject_nonSIP]
Pattern: $request = ^INVITE
Input: $request = INVITE sip:6597115774@sgvivohub.xxxx:6061 SIP/2.0
Result: true
Pattern: To = sip:(.+)\>
Input: To = <sip:6597115774@sgvivohub.xxxxx.com>
%1 <= 6597115774@sgvivohub.xxx.com
Result: true
Pattern: $db.query("vXiPP", "select username from t_userdir where username='%1'") = (.+)
Input: $db.query("vXiPP", "select username from t_userdir where username='6597115774@sgvivohub.xxxx.com'") = null
Result: false
============================================^M
3.5.5.2/424-1
2. Java version:
1.8.0_66
3. OS type and the version:
Linux version 3.10.0-327.4.4.el7.x86_64
4. UA (phone), gateway or other hardware/software involved:
Softphone
5. Your problem: db.query not working via MS SQL.
We trying implement dialplan db query register user with external database MS SQL. We not issue connect brekeke SIP server to MS SQL for user registration database but it always return null value when using dial plan db.query registered user entry. The sql statement paste in MS SQL query able retrieve record.
dialplan & logfile as below
Matching Patterns:
$request="^INVITE";
To="sip:(.+)\\\>";
$db.query("dbname", "select username from t_userdir where username='%1'")="(.+)";
sv log:
============================================
Rule [Reject_nonSIP]
Pattern: $request = ^INVITE
Input: $request = INVITE sip:6597115774@sgvivohub.xxxx:6061 SIP/2.0
Result: true
Pattern: To = sip:(.+)\>
Input: To = <sip:6597115774@sgvivohub.xxxxx.com>
%1 <= 6597115774@sgvivohub.xxx.com
Result: true
Pattern: $db.query("vXiPP", "select username from t_userdir where username='%1'") = (.+)
Input: $db.query("vXiPP", "select username from t_userdir where username='6597115774@sgvivohub.xxxx.com'") = null
Result: false
============================================^M
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
Did you set variables below in the [Configuration] -> [Advanced] page?
---------------------------
db-name.driver = <jdbc_driver>
db-name.url = <database_url>
db-name.user = <user_name>
db-name.password = <password>
----------------------------
Refer http://wiki.brekeke.com/wiki/Execute-SQ ... m-Dialplan
---------------------------
db-name.driver = <jdbc_driver>
db-name.url = <database_url>
db-name.user = <user_name>
db-name.password = <password>
----------------------------
Refer http://wiki.brekeke.com/wiki/Execute-SQ ... m-Dialplan
Thank you. Didn't notice require configure connection again. Thought it will make use of existing connection.Tata wrote:Did you set variables below in the [Configuration] -> [Advanced] page?
---------------------------
db-name.driver = <jdbc_driver>
db-name.url = <database_url>
db-name.user = <user_name>
db-name.password = <password>
----------------------------
Refer http://wiki.brekeke.com/wiki/Execute-SQ ... m-Dialplan
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
db-name.driver = com.microsoft.sqlserver.jdbc.SQLServerDriverTata wrote:Hope it works.
db-name.url = jdbc\:sqlserver\://xxx.xxx.xxx.xxx\:1433;databasename\=vXiPP
db-name.user = dbusrname
db-name.password = ******
I have setup the connection but still not seeing any SQL response to database.
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
Yes. is advanced edition and the dialplan did execute but value always return as "null" even the callee is registered in DBTata wrote:Is it Advanced Edition? If not, $db.query() doesn't work.
Go to the DialPlan history page and check whether the expected rule is executed or not.
============================================
Rule [Reject_nonSIP]
Pattern: $request = ^INVITE
Input: $request = INVITE sip:6597115774@xxxxx.ngtnetworks.com:6061 SIP/2.0
Result: true
Pattern: To = sip:(.+)\>
Input: To = <sip:6597115774@xxxxx.ngtnetworks.com>
%1 <= 6597115774@xxxxx.ngtnetworks.com
Result: true
Pattern: $db.query("vXiPP", "select username from t_userdir where username='%1'") = (.+)
Input: $db.query("vXiPP", "select username from t_userdir where username='6597115774@xxxxxx.ngtnetworks.com'") = null
Result: false
============================================^M
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
Yes. we running multiple domain. the username is correct and exact entry we store in table.janP wrote:> Pattern: $db.query("vXiPP", "select username from t_userdir where username='%1'") = (.+)
> Input: $db.query("vXiPP", "select username from t_userdir where username='6597115774@xxxxxx.ngtnetworks.com'") = null
Are you sure "6597115774@xxxxxx.ngtnetworks.com" is a SIP username?
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
kelvintee,
I tested your DialPlan definition in my Brekeke SIP Server without any issues. So I can get "username" value as the result successfully.
I think you don't have the correct settings.
Have you set vXiPP.driver, vXiPP.url, vXiPP.user and vXiPP.password in [Configuration]->[Advanced] page?
You must specify "vXiPP" instead of "db-name" because you defined it with $db.query.
like this
------------------------------
vXiPP.driver = <jdbc_driver>
vXiPP.url = <database_url>
vXiPP.user = <user_name>
vXiPP.password = <password>
------------------------------
I tested your DialPlan definition in my Brekeke SIP Server without any issues. So I can get "username" value as the result successfully.
I think you don't have the correct settings.
Have you set vXiPP.driver, vXiPP.url, vXiPP.user and vXiPP.password in [Configuration]->[Advanced] page?
You must specify "vXiPP" instead of "db-name" because you defined it with $db.query.
like this
------------------------------
vXiPP.driver = <jdbc_driver>
vXiPP.url = <database_url>
vXiPP.user = <user_name>
vXiPP.password = <password>
------------------------------
Hi JanP,
you are right. it seem this is root caused. let me try it. thank you very much
you are right. it seem this is root caused. let me try it. thank you very much
janP wrote:kelvintee,
I tested your DialPlan definition in my Brekeke SIP Server without any issues. So I can get "username" value as the result successfully.
I think you don't have the correct settings.
Have you set vXiPP.driver, vXiPP.url, vXiPP.user and vXiPP.password in [Configuration]->[Advanced] page?
You must specify "vXiPP" instead of "db-name" because you defined it with $db.query.
like this
------------------------------
vXiPP.driver = <jdbc_driver>
vXiPP.url = <database_url>
vXiPP.user = <user_name>
vXiPP.password = <password>
------------------------------
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
Hi JanP,
working perfect. THank you very much.
working perfect. THank you very much.
kelvintee wrote:Hi JanP,
you are right. it seem this is root caused. let me try it. thank you very much
janP wrote:kelvintee,
I tested your DialPlan definition in my Brekeke SIP Server without any issues. So I can get "username" value as the result successfully.
I think you don't have the correct settings.
Have you set vXiPP.driver, vXiPP.url, vXiPP.user and vXiPP.password in [Configuration]->[Advanced] page?
You must specify "vXiPP" instead of "db-name" because you defined it with $db.query.
like this
------------------------------
vXiPP.driver = <jdbc_driver>
vXiPP.url = <database_url>
vXiPP.user = <user_name>
vXiPP.password = <password>
------------------------------
Regards,
Kelvin
NGT Networks Pte Ltd
Kelvin
NGT Networks Pte Ltd
Brekeke standard version with MySql
Are MySql database query's compatible with the standard Brekeke Sip server, or does it need to be the advanced version. The Brekeke documentation for third party database integration seems to suggest the standard version will work.
britchey