Author |
Message |
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Sun Mar 06, 2016 9:47 pm Post subject: 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 _________________ Regards,
Kelvin
NGT Networks Pte Ltd |
|
Back to top |
|
Tata Brekeke Master Guru
Joined: 27 Jan 2008 Posts: 223
|
Posted: Mon Mar 07, 2016 11:16 am Post subject: |
|
|
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-SQL-query-from-Dialplan |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Mon Mar 07, 2016 8:23 pm Post subject: |
|
|
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-SQL-query-from-Dialplan |
Thank you. Didn't notice require configure connection again. Thought it will make use of existing connection. _________________ Regards,
Kelvin
NGT Networks Pte Ltd |
|
Back to top |
|
Tata Brekeke Master Guru
Joined: 27 Jan 2008 Posts: 223
|
Posted: Mon Mar 07, 2016 8:37 pm Post subject: |
|
|
Hope it works. |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Mon Mar 07, 2016 9:12 pm Post subject: |
|
|
Tata wrote: |
Hope it works. |
db-name.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
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 |
|
Back to top |
|
Tata Brekeke Master Guru
Joined: 27 Jan 2008 Posts: 223
|
Posted: Tue Mar 08, 2016 10:45 am Post subject: |
|
|
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. |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Tue Mar 08, 2016 7:40 pm Post subject: |
|
|
Tata 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. |
Yes. is advanced edition and the dialplan did execute but value always return as "null" even the callee is registered in DB
============================================
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 |
|
Back to top |
|
ambrosio Brekeke Master Guru
Joined: 27 Mar 2008 Posts: 215
|
Posted: Wed Mar 09, 2016 4:53 pm Post subject: |
|
|
Are you sure the table "t_userdir" exists in the database? |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Sun Mar 27, 2016 9:40 am Post subject: |
|
|
ambrosio wrote: |
Are you sure the table "t_userdir" exists in the database? |
Yes, I have no issue using the External database for SIP registration authentication. It just failed to query via diaplan. _________________ Regards,
Kelvin
NGT Networks Pte Ltd |
|
Back to top |
|
janP Brekeke Master Guru
Joined: 25 Nov 2007 Posts: 336
|
Posted: Sun Mar 27, 2016 5:24 pm Post subject: |
|
|
> 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? |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Mon Mar 28, 2016 3:16 am Post subject: |
|
|
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? |
Yes. we running multiple domain. the username is correct and exact entry we store in table. _________________ Regards,
Kelvin
NGT Networks Pte Ltd |
|
Back to top |
|
janP Brekeke Master Guru
Joined: 25 Nov 2007 Posts: 336
|
Posted: Mon Mar 28, 2016 12:04 pm Post subject: |
|
|
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>
------------------------------ |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Tue May 10, 2016 10:11 pm Post subject: |
|
|
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 |
|
Back to top |
|
kelvintee Brekeke Member
Joined: 15 Apr 2012 Posts: 19
Location: Singapore
|
Posted: Thu May 12, 2016 2:32 am Post subject: |
|
|
Hi JanP,
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 |
|
Back to top |
|
janP Brekeke Master Guru
Joined: 25 Nov 2007 Posts: 336
|
Posted: Thu May 12, 2016 4:00 pm Post subject: |
|
|
nice! |
|
Back to top |
|
britchey Brekeke Junior Member
Joined: 21 May 2016 Posts: 7
Location: PA
|
Posted: Fri Jun 10, 2016 7:19 pm Post subject: 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 |
|
Back to top |
|
ambrosio Brekeke Master Guru
Joined: 27 Mar 2008 Posts: 215
|
Posted: Mon Jun 13, 2016 11:41 pm Post subject: |
|
|
Both editions are ok. |
|
Back to top |
|
|