Need help with ODBC Search

[billthenerd]

Hi. I am trying to setup ODBC search in 3CX Phone Server PRO, and I cannot understand why it is not working.

I assumed that if the ODBC connection was setup and working, then any call coming in to the system would have the CallerID matched in the DSN and the Caller Name(s) displayed to the user in 3CX Phone. Is that the intended behaviour?

Perhaps the intention with this feature is not to display the Caller Name to the user of 3CX Phone - I can't see anywhere to confirm or deny this, but it seems like an obvious expectation.

I have followed the instructions at http://www.3cx.com/docs/odbc-search-phonebook-directory/

I have a valid SQL table to check against, and created a System DSN on the server to access that database.

However, the ODBC data is not shown to the user on receiving a call from a contact in the SQL database.

Is there another 3CX Server log I can check which will tell me if the ODBC search is trying to work, and what the results are?

Nothing appears in the Server Event Logs, Activity Logs or in Windows Event Logs related to this.

Problem Solving:
The DSN tests fine for connecting in Windows.

I have run the test tool from that same article, and it works, although I have to provide the UserID and Password for the DSN in the connection string for it to work, which doesn't seem right?

If I run the test tool without the UID & PWD fields, I get back an error:
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.

I have also tried manually adding the UserID and Password to the ODBC settings in the registry, but it doesn't help.

However, I cannot save the UID & PWD fields in 3CX Management > Advanced > Company Directory Sync > ODBC Search, as they are dropped every time I try.

I tried creating the DSN as 32bit, since the server is 64bit, but that gives another error, which makes sense;
The specified DSN contains an architecture mismatch between the Driver and Application

I did a stack trace on the ODBC connection, and when the test tool connects, I can see the traffic and commands get logged, and I get results back.

However, when I enable the ODBC Search in 3CX Server and make some test calls, nothing is logged in the stack trace, so it appears no SQL lookup is being made.

 

[billthenerd]

I have managed to solve the DSN connection issue by using a Trusted Connection (Windows Auth) setup in the DSN, and adding the 3CX Windows Server to the allowed list of logins.

However, while it seems that the ODBC connection should be all up and running, nothing has changed in 3CX Phone's behaviour. It still displays only the CallerID without searching the ODBC connector for First, Last and Company names.

Can anyone tell me if 3CX Phone, by default, should match and display caller name information based on the results of the ODBC search?
And if so, is there anything else to do apart from configuring the ODBC Search function in the console?

I would really appreciate it.

 

[billthenerd]

This issue is resolved - It turns out the last step was matching the country code of the caller correctly, so just some tweaking in the odbc source required.

 

[jms]

What sort of "tweaking in the odbc source" was required, Bill?

 

[billthenerd]

What sort of "tweaking in the odbc source" was required, Bill?

I had to alter the database source data, removing the country code from stored phone numbers.
The incoming callerID passed to 3CX by our VoIP carrier did not include the country code.

I also found that the odbc connection wasn't established immediately once I saved the settings in the management console. It took a minute or 2 for it to starting working, so I had to slow down my testing a bit.