ODBC Search

[mv.ape]

Hello,

I'm testing 3CX system, almost every things work well expect ODBC search.
I've:
-Created an DSN (tested, connexion successful)
-Configurated in 3CX
-Checked ODBC search prefered
-Download 3CX test tool, query it's good and return result.

But it's don't work. I tried to trace ODBC via windows and nothing happen on incoming call. Is there somthing else to do?

Thanks
Version: 3CX 12 Pro edition

 

[Pierre_3CX]

Hi there,
Kindly open a ticket on our support portal for further investigations on this matter.

 

[mv.ape]

Hello,

I've no maintenance yet. Prior to buy the product I want to test all fonctionnality.
So I can't open a ticket.

 

[Pierre_3CX]

Ok I though you had one because you said you were activated on pro license.
Then few things to check :
- in Settings / General / Global Options / "Resolved caller ID to phonebook entry", should be checked, with matching on "Minimum length" : X digits.
- ODBC DSN should be declared in system DSN's (not users) and in the 64 bit version if OS is 64 bit
- in Settings / Advanced / Company Directory Sync / ODBC Search / DSN name should match the one from control panel
- query should have syntax similar to : SELECT field0,field1,field2 WHERE field3 LIKE '%%number%'
- fields numbers should be mapped accordingly (at least 3 fields countring from 0, first name, last name, and mobile)

 

[mv.ape]

For the moment I'm using the demo prodcut.
I've checked every things, but when I trace in obdc tool, I don't see anything (no connection, the file is empty, but with 3cx ODBC tool I see lines in this file).

-The DSN name is correct (is 64bits system DSN, MySQL 5.3)
-Resolved caller ID to phonebook entry is checked, I also restart services
-Query is correct and return valid result
-Fields are configured from 0 to 2 (0= Company, 1=Name, 2=Tel)

 

[Pierre_3CX]

Hi there,

Are you using LDAP phonebook aswell ?
If not, you must ensure that the option Settings / Advanced / Company Directory Sync / ODBC Search / "Check this option to prefer ODBC results over LDAP or if LDAP is not configured" is checked.

Also, please confirm that you are running latest v12.5 release (build 12.5.39117.982 - check on login page) not prior beta release.

 

[mv.ape]

Hello,

I'm not using LDAP or other phonebook, only ODBC search.
I've checked version v12.5.39117.982

I've also unchecked Check this option to prefer ODBC results over LDAP or if LDAP is not configured, but I do not seen anything in SQL trace

 

[Pierre_3CX]

Calls must be picked for matching to be triggered, so you must in fact :
- make a first call, and pick it without having name on screen, at the same time it will trigger matching through odbc and insert a new row in phonebook if any match
- make a second call, this time you should see name displayed, then pick

Additionally, you also have the log file C:\ProgramData\3CX\Data\Logs\3CXCompanyDirectory.trace.log which will give more infos on the phonebook matching. You'll need to be in Settings / Advanced / Logging level : verbose, and have restarted services, prior to see these traces.

 

[mv.ape]

I made multiple calls from the same number without results.
I've already activated verbose logging and analyzed logs, but i don't find any line about directory or odbc.

In the file C:\ProgramData\3CX\Data\Logs\3CXCompanyDirectory.trace.log, I only can see these lines:
17/02/2015 11:38:00 CalendarSync: Today is a holiday, do nothing

 

[mv.ape]

In log on server I can found:

18/02/2015 11:23:42:578 | Looking for phone number xxxxxxxx9 in tenant's 'default' phonebook
18/02/2015 11:23:42:578 | Tenant's phonebook has failed to load!
18/02/2015 11:23:42:578 | CallerNameAddr: "xxxxxxxx9"<sip:xxxxxxxx9;nf=e>

 

[mv.ape]

I've tried to enter a contact in COmpany phone book (General Settings -> company phonebook) and the name displayed on phone

 

[mmj]

I experience the exact same problem.

Is there any solution?

- Lookup works using the CDM_ODBC_Test.exe example application
- According to DSN Logs 3CX does not even try to query
- 3CXCompanyDirectory.trace.log = only holiday message
- I tried both, "Check this option to prefer ODBC results over LDAP or if LDAP is not configured" checked and unchecked -> BTW: In this forum pj3cx says it needs to be unchecked while 3CX says the opposite in http://www.3cx.com/docs/odbc-search-phonebook-directory/. - [PJ Edit : yes apologizes, I edited previous post the option must be checked]
- I made calls and picked them up
- I'm on PRO Version
- If I enter my number manually in the company phonebook it is displayed.
- DSN Name is correct, 64bit used...

Somehow 3CX seems to fail to initiate the query.
Very frustrating

 

[mv.ape]

Hello,

There is a "bug", the work around:
Configure a fake LDAP
Server: rubbish
Port: 389
TTL: 385

And on ODBC Search, uncheck "prefer ODBC search".

That worked for me

 

[mmj]

Thank you so much, that did the trick. It works now!

The only thing I needed to change afterwards: +1 to each column because I did not know the first is counted as 0.

 

[art_gruby]

I confirm malfunction and work around with fake LDAP and turning off ODBC preference :lol:

Additionally, when incoming call goes through IVRs and Virtual Extensions (out Inbound Rules)
PBX makes two separate query to ODBC (for each query got response).
After the first ODBC query-response in Company Phonebook was entered entry for the incoming call identification.
Why the second query was executed?
Of course, as a result of the second query-response in Company Phonebook the same second entry was created.
And at the result there is no name identification for the call.
Name is presented correctly but for next calls.

Cooperation functionality of Company Phonebook with ODBC support is full of bugs :S

 

[Pierre_3CX]

Hi there,
We already have a fix for those 2 bugs mentionned in this topic, if someone wants them at current they are provided through support tickets, but those will come with future update aswell.

 

[kerem.askin]

I experience the exact same problem too. I read all your comment and try your solutions but the problem is not solved. I have a log in 3CXCompanyDirectory.trace file. This log:

2.6.2015 16:50:46 Trying to match number against LDAP
2.6.2015 16:50:51 No result found in LDAP for number: 05545411542, so search in SQL
2.6.2015 16:50:51 Looking for 2 according to Minimal Matching Strategy in 3CX
2.6.2015 16:50:51 Error while trying to establish database connection: ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.

Odbc testing tool connect sql server and select required field. But log says : "Login failed for user" :roll:

thanks.

 

[ormondcollege]

I found a way for the server to store the credentials for MS SQL server, go to Control Panel, Credential Manager. Click the Windows Credentials icon, then add a Windows Credential. Specify the server name as {fqdn}:1433 or {ip address}:1433 then enter the correct login DOMAIN\USER and password to access the SQL Server.

The test tool works fine, using just the DSN name.....but the actual lookup process fails with a different SQL error...

30/06/2015 3:15:36 PM Unknown incoming number: 0423406523
30/06/2015 3:15:36 PM Looking for number: 0423406523 in SQL
30/06/2015 3:15:36 PM Looking for 23406523 according to Minimal Matching Strategy in 3CX
30/06/2015 3:15:36 PM Error while trying to establish database connection: ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
30/06/2015 3:15:41 PM No result found in SQL for number: 0423406523, so search in LDAP

Perhaps the service needs to run under the logged in user, rather than LocalSystem ??

 

[Pierre_3CX]

Hi there,
In DSN field you can specify authentication for MSSQL via syntax below :
DSN_NAME;uid=USER;pwd=PASS;