How to Setup Company Directory Sync - ODBC Search
Zero Admin
With the new Dashboard
Bulletproof Security
With SSL certs and NGINX
Install on $200 Appliance
Intel MiniPC architecture
New, Intuitive Windows Client
More themes, more UC
More CRM Integrations
Scripting Interface to add your own
Improved Integrated Web Conferencing
iOS and Android apps included
Run On-Premise or in the Cloud
Google, OVH, Windows & Linux

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:

<