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-SQL-query-from-Dialplan

[kelvintee]

[Tata]

Hope it works.

[kelvintee]

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

[ambrosio]

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

[kelvintee]

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

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

Hi JanP,

working perfect. THank you very much.

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