How to Setup Company Directory Sync - ODBC Search
pixel500w-500x1
Zero Admin
With the new Dashboard
pixel500w-500x1
Bulletproof Security
With SSL certs and NGINX
pixel500w-500x1
Install on $100 Appliance
Intel MiniPC architecture
pixel500w-500x1
New, Intuitive Windows Client
More themes, more UC
pixel500w-500x1
More CRM Integrations
Scripting Interface to add your own
pixel500w-500x1
Improved Integrated Web Conferencing
iOS and Android apps included
pixel500w-500x1
Personal Click2Meet URLs

How to Setup Company Directory Sync – ODBC Search

How to Setup Company Directory Synchronization - ODBC Search

In this chapter

Introduction

Workflow

Configuring Contacts Options

Configuring ODBC for 3CX

Step 1: Creating an ODBC Data Source (DSN)

Step 2: ODBC configuration

DSN Name

MSSQL Information

Query String

Sample: Search only in column mobile for a number

Sample: Search only in column mobile and mobile2 for a number

Sample: Search in all columns for a number

Sample: Search with function for a number

Step 3: Fields mapping

Test Tool

Optional settings

Introduction

The ODBC extension for the company directory manager allows your business to connect the 3CX Phone System to any data source which can be accessed via an ODBC driver. In this way contact data can be imported from any database driven ERP or CRM when an incoming call is received.

Workflow

The functionality will perform a contact lookup against your database of choice on incoming call from an unknown external number (not yet in company phonebook). The lookup will be performed once the call is picked up by an extension. If contact has been found successfully, it will be automatically imported to the company phonebook. In consequence, the contact name will not be displayed on the first occurrence of the unknown caller ID.

Configuring Contacts Options

Under “Contacts” > “Options” > “Match Caller ID’s to a contact entry” section the option to “Match at least X number of characters” should be ticked and set with a minimum of 6 characters. This option is by default enabled and ensures that phonebook matching occurs on incoming calls.

Configuring ODBC for 3CX

Step 1: Creating an ODBC Data Source (DSN)

Install the ODBC driver for the required database which holds the contact information. For this example we will use MySQL http://dev.mysql.com/downloads/connector/odbc/

  1. Run ODBC by typing “odbc” in the Windows Start Menu. Make sure to run the x64 bit version.

  1. Switch to the “System DSN” tab and click “Add…”.

  1. Create a connection to your database. Note the Data Source Name (DSN) is very important for further use. Use the “Test” button to verify the connection to your database.

Step 2: ODBC configuration

Go in the 3CX Phone System Management Console > “Contacts” > “ODBC”.

DSN Name

Fill the DSN Name field with the Data Source Name from control panel as set in Step 1.

MSSQL Information

In case of MSSQL database you will have to specify a connection string including user and password of the database. To do so, in the DSN Name field should be appended: “ODBCdatabase;uid=USER;pwd=PASS;

Query String

The Query String can contain any valid SQL statement.

Some prerequisites applies to the data:

  • The selected fields must all have a value, which can be an empty strings but not null values.
  • The numbers fields must not have any special character other than +, no spaces should be present. See sample “Search with function for a number” if a number needs to be formatted on request.

In regards to our example database “client” (seen below) use:

Sample: Search only in column mobile for a number

SELECT *

FROM client

where mobile like '%%number%'

Sample: Search only in column mobile and mobile2 for a number

SELECT *

FROM client

where mobile like '%%number%' or mobile2 like '%%number%'

Sample: Search in all columns for a number

SELECT *

FROM client

where mobile like '%%number%' or mobile2 like '%%number%' or home like '%%number%' or home2 like '%%number%' or business like '%%number%' or business2 like '%%number%' or other like '%%number%' or businessfax like '%%number%' or homefax like '%%number%' or pager like '%%number%'

Sample: Search with function for a number

The values within the database have an inconsistency in the way the number is stored. You need to analyze the data within all phone number columns and create a function which will remove all non numeric values once a query is made against the value field. Most CRM application will have this function already in place to start calls from the their application. Request the function name. Below is a function for our example database called ExtractInteger

******

DROP FUNCTION `ExtractInteger`//

CREATE DEFINER=`root`@`localhost` FUNCTION `ExtractInteger`(String VARCHAR(2000)) RETURNS varchar(1000) CHARSET latin1

BEGIN

DECLARE Count INT;

DECLARE IntNumbers VARCHAR(1000);

SET Count = 0;

SET IntNumbers = '' ;

WHILE Count <= LENGTH(String) DO

IF (SUBSTRING(String,Count,1) >= '0' ) AND (SUBSTRING(String,Count,1) <= '9') THEN

SET IntNumbers = CONCAT (IntNumbers, SUBSTRING(String,Count,1));

END IF;

SET Count = Count + 1;

END WHILE;

RETURN IntNumbers;

END

******

The query string in this case needs to involve the function and the column

 

SELECT *

FROM client

where ClientDB.ExtractInteger (mobile) LIKE '%%number%'

Step 3: Fields mapping

As a general rule, database field numbering starts from zero, making in our sample table FirstName equivalent to 0, LastName to 1.

Map the existing columns to the field value of the ODBC map. In case your database does not feature all the columns, simply leave the value blank inside the 3CX Phone System Management Console.

Test Tool

3CX provides a test tool which can simulate an incoming call request against your database and lists all found results from the query. The Configuration syntax is exactly the same as for the 3CX Phone System. Download our test tool to test your configuration.

Note: the test tool will run the query as the current logged-in Windows user, whereas the PBX will do so as System. This may result in different outcome and relies on the authentication passed on the Connection String or the ODBC DSN configuration.

Optional settings

If you have LDAP Directory Search configured, you may choose which database 3CX Phone System queries first. If you want the PBX to query ODBC followed by LDAP, check the option right under the ODBC mapper. Alternatively if you want the PBX to query LDAP followed by ODBC, leave the box unchecked.

If you have not configured LDAP Directory Search, you need to check the box in order for ODBC queries to be carried out.

You might also be interested in:


Ask a Question

Please only post questions in regards to the document you are currently reading.
Technical support or pre sales questions must be posted via the support or sales channels and such comments will be deleted. Thank you for understanding

Leave a Reply

  1. Yann

    This looks really great but I can’t see the ODBC Search tab

    June 27, 2015 at 4:46 am Reply
    • Charalambos Eleftheriou

      Hi Yann

      In the 3CX Management Console, expand ‘Settings’ > click ‘Advanced’ then choose the ‘Company Directory Sync’ tab from the top menu, underneath which you will see the ‘ODBC Search’ Tab.

      June 27, 2015 at 11:54 am
  2. Hi Guys, All Setup
    All details in
    ODBC Link tested and running the Pro version

    BUT …

    Cant find the ‘ON’ button – Any ideas where you tell the system to actually run the search?

    August 6, 2015 at 8:18 pm Reply
    • Charalambos Eleftheriou

      @Charlie, thanks for the question. The on switch is an incoming call. When a call comes in the 3CX Phone System will search against the configured database for a match, if match is found the entry will be automatically imported into the 3CX phonebook. On the page above there is a link to download a test tool that simulates an incoming call to tear your ODBC search with. Thanks.

      August 7, 2015 at 8:16 am
  3. Andre

    Hi,

    I’m using MSSQL 2015 and we are working with SAP Business One and can`t get it to work.

    I created a VIEW in MSSQL and used the test tool and it only works when I enter the connection string WITH credentials.

    DSN=3cx_phonebook;UID=3cx_ro; PWD=PASSWORD;database=DBNAME

    in the 3cx console I can only enter the DSN name and can`t enter the username/password.

    also when use the test tool with the DSN name only I get an auth error.

    when I use the TEST ODBC connection I get a TEST SUCCESFULL

    any idea`s on how to solve my problem?

    September 16, 2015 at 6:55 pm Reply
    • Charalambos Eleftheriou

      @Andre, Thanks for the question. Due to the limitation of these in page comments we are unable to offer direct support for complex issues that require investigation to resolve. Please contact your 3CX partner or distributor to open a ticket with our support team so that they can look into this for you.

      September 17, 2015 at 5:43 pm
  4. Markus Dages

    Is there any logfile to troubleshoot the odbc connection?
    I’ve configured ODBC as explained here, no error with the Test-Tool, but nothing happens on incoming calls. Don’t see what could be wrong and without any logfile with responses to the odbc connections, troubleshooting becomes very difficult.
    Thanks for answer.

    January 22, 2016 at 3:58 pm Reply
    • Andrew

      Same problem, Test-tool is working fine, but nothing happens on incoming calls.

      January 27, 2016 at 3:00 am
    • Charalambos Eleftheriou

      @Andrew, thanks for the question. You may find a log with connection details within the ‘CallHistoryService.log’ found under C:\ProgramData\3CX\Instance1\Data\Logs (3Cx Phone System v14), on the 3CX Phone System server. You need to though make sure that the logging level is set to ‘verbose’ in order to see these details within the log file.

      January 27, 2016 at 10:31 am
  5. Alex

    Is it possible to synchronize/import all contacts from a ODBC data source automatically and periodically?

    It would be fine to search and dial contacts from the 3cx sofphone/cti client.

    In the moment you only can find contacts that have allready called because they were imported to the 3cx phonebook while calling.

    February 13, 2016 at 5:33 pm Reply
    • Charalambos Eleftheriou

      @Alex, thanks for the question. Contacts can not be imported all at once using the ODBC search feature I’m afraid. This feature adds contacts to the 3CX Company PhoneBook on incoming calls, if they do not exist already. See this guide for information on how to import/synchronize all your contacts at once: http://www.3cx.com/docs/exchange-ldap-phonebook-directory/.

      February 15, 2016 at 10:16 am
    • Joe

      CallHistoryService.log doesn’t appear in version 15, with verbose logging enabled

      September 21, 2016 at 4:13 pm
    • It has been renamed to SystemService.log.

      September 22, 2016 at 12:01 pm
  6. 3cx ODBC setup does not accept any equals signs (=) when entering the DNS name for the ODBC connection (The test tool does and there it works like a charme).
    Therefore it is not possible to setup ODBS with a complete connection string.

    July 20, 2016 at 1:57 pm Reply
    • This is a bug and we will fix it – I’ll send you a workaround via email.

      July 20, 2016 at 3:12 pm
    • Ian

      DSN name cannot have = sign. This is not fixed in V15.
      When will this be fixed? We still have not chance to get this working with MSSQL.
      However, like a lot of other users, the test tool works perfectly.

      July 23, 2016 at 2:57 pm
  7. Andrew Rosenbaum

    MS SQL is perfectly working. You will need to pass login data to the ODBC as our service run as System and the tool with with rights of the logged in user. Sample: DNS=MSSQL;uid=USERNAME;pwd=PASS;QUERRY

    July 27, 2016 at 12:20 pm Reply
<