How to Setup Company Directory Synchronization - ODBC Search
In this chapter
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.
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/
- Run ODBC by typing “odbc” in the Windows Start Menu. Make sure to run the x64 bit version.
- Switch to the “System DSN” tab and click “Add…”.
- 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”.
Fill the DSN Name field with the Data Source Name from control panel as set in Step 1.
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;”
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
where mobile like '%%number%'
Sample: Search only in column mobile and mobile2 for a number
where mobile like '%%number%' or mobile2 like '%%number%'
Sample: Search in all columns for a number
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
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));
SET Count = Count + 1;
The query string in this case needs to involve the function and the column
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.
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.
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.