How to set up directory synchronization in IP PBX - search by ODBC

The ODBC extension allows you to connect 3CX Phone System to a data source that the ODBC driver supports. Thus, any database that supports ERP or CRM can import contact information from a common database when an incoming call is received.




Creating an ODBC data source (DSN)
')
Install the ODBC driver for the database where the contact information is stored. In our example, we will use MySQL http://dev.mysql.com/downloads/connector/odbc/

1. Start ODBC by typing "odbc" in the Windows Start menu. Be sure to use the x64-bit version.



2. Switch to the “System DSN” tab and click the “Add ...” button



3. Create a connection to the database. Note: The DATA Source Name field is very important for proper operation. Use the "Test" button to test the connection to the database.

Sample

As an example, we created a table named “client” in our database “ClientDB”



Note: values ​​for the column “FirstName” set “0”, for “LastName” the value is “1”, for “Pager” the value is “13”.



To configure ODBC settings in 3CX Phone System, go to Settings> Advanced> Company Directory Sync> ODBC Search. If the columns in your database do not match, then leave them empty in 3CX Phone System.



If you do not have LDAP Directory Search configured, then you need to check the box in order for ODBC requests to be executed.

Work with the Database

Now configure the query string using the ODBC driver. You must enter a DSN. In our example, this is “ODBCdatabase”.



In the "Query String" any valid SQL statement can be entered.
For our example client database, we could use:

Search only in the "mobile" column:
SELECT *
FROM client
where mobile like '%%number%'


Search by “mobile” and “mobile2” columns:
SELECT *
FROM client
where mobile like '%%number%' or mobile2 like '%%number%'


Search all columns:
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%
'

Inside the database may be copies of contacts. You must analyze the data in the phone numbers and create a function that will delete all non-numeric values ​​of the field to which the request is made. Most CRMs have this feature initially. For our example, below is a function 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
ers = CONCAT (IntNumbers, SUBSTRING (String, Count, 1));
END IF;
SET Count = Count + 1;
END WHILE;
RETURN IntNumbers;
END
******

Check



3CX provides a test tool that can simulate a database query and show the result. The configuration syntax is the same as for 3CX Phone System. Download the test tool here .