Diaplan db.query not working

[kelvintee]

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

[Tata]

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

[kelvintee]

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

Thank you. Didn't notice require configure connection again. Thought it will make use of existing connection.

[Tata]

Hope it works.

[kelvintee]

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.

[Tata]

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.

[kelvintee]

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

[ambrosio]

Are you sure the table "t_userdir" exists in the database?

[kelvintee]

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.

[janP]

> 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?

[kelvintee]

> 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.

[janP]

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>
------------------------------

[kelvintee]

Hi JanP,

you are right. it seem this is root caused. let me try it. thank you very much

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>
------------------------------

[kelvintee]

Hi JanP,

working perfect. THank you very much.

Hi JanP,

you are right. it seem this is root caused. let me try it. thank you very much

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>
------------------------------

[janP]

nice!

[britchey]

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.

[ambrosio]

Both editions are ok.