Microsoft SQL Server, MySQL, PostgreSQL Database Integration
Introduction
3CX provides integration with SQL databases via the 3CX API for CRM with the following features:
- Caller ID to Contact Name – inbound calls trigger an SQL database contact lookup.
- Contact Lookup in SQL database based on Name, Number or Email if you search for a name in the 3CX Webclient
- Call & Chat Journalling – calls and chats are logged in the SQL database.
- Create a new SQL database contact from the 3CX client for calls from new numbers.
- Click to Call – Providing that your system is a web application, launch calls straight from your system via 3CX, using the 3CX Click2Call Browser extensions for Google Chrome and Microsoft Edge.
Step 1: Configure 3CX
- In the 3CX Admin Console go to “Integrations” > “CRM”. Select your SQL database type from the dropdown list. In order to integrate with MariaDB, please select the MySQL Database option and proceed as usual.
- Enter the connection information:
- Server: the server name or IP address. For MS SQL Server can include the instance name or port number when needed, e.g.:
- myServerName
- myServerName\myInstanceName
- myServerAddress,Port
- Port: the port number on which the connection must be established. Not applicable to MS SQL Server.
- Database: the name of the database to use.
- Username: the username to use to connect to the database.
- Password: the password to use to connect to the database.
- Enter the Lookup By Number SQL Statement. This will be used when doing the lookup for calls or chat interactions through SMS. This must be a SELECT statement returning the following case-sensitive columns: contactid,firstname,lastname,companyname,email,phonemobile,phonemobile2,phonehome,phonehome2,phonebusiness,phonebusiness2,phoneother,faxbusiness,faxhome,pager,photourl. If your database table has different column names, you can change the name returned as follows:
- Enter the Lookup By Email SQL Statement. This will be used when doing the lookup for Live Chat interactions. This must be a SELECT statement returning the following case-sensitive columns: contactid,firstname,lastname,companyname,email,phonemobile,phonemobile2,phonehome,phonehome2,phonebusiness,phonebusiness2,phoneother,faxbusiness,faxhome,pager,photourl. If your database table has different column names, you can change the name returned as follows:
- Enter the Search Contacts SQL Statement. This will be used when searching contacts from the Web Client. You should search for the text in any field in your database. This must be a SELECT statement returning the following case-sensitive columns: contactid,firstname,lastname,companyname,email,phonemobile,phonemobile2,phonehome,phonehome2,phonebusiness,phonebusiness2,phoneother,faxbusiness,faxhome,pager,photourl. If your database table has different column names, you can change the name returned as follows:
- Enter the Contact URL prefix and suffix. These are required to create the Contact URL, which is the concatenation of the Contact URL Prefix, the ContactID returned by the database, and the Contact URL Suffix. This Contact URL is considered the key for the contact in 3CX Contacts, and is used to update the contact if any change is detected in the database. Also, this Contact URL is shown in the 3CX Web Client, so if you have a web page to show the contact record, use these settings to create the URL to point to that page.
Step 2: Configure Call & Chat Journaling
- If you want to report external calls to the database, check the Enable Call Journaling checkbox and configure the Call Journaling SQL Statement.
- Please note that you can use variables in the Call Journaling SQL Statement. Variables are specified with the @ prefix, for example the external number is specified as @Number. The available variables to use are listed here.
- Your Call Journaling SQL Statement should be an INSERT statement, for example:
For MS SQL:
For MySQL:
For PostgreSQL:
- If you want to report chats to the database, check the Enable Chat Journaling checkbox and configure the Chat Journaling SQL Statement.
- Please note that you can use variables in the Chat Journaling SQL Statement. Variables are specified with the @ prefix, for example the external number is specified as @Number. The available variables to use are listed here.
- Your Chat Journaling SQL Statement should be an INSERT statement, for example:
For MS SQL:
For MySQL:
For PostgreSQL:
Step 3: Configure Contact Creation
- To create new contacts in the database when the caller number can’t be found, check the “Allow contact creation directly to your CRM using 3CX Web Client” checkbox and configure the “Contact Creation from Client SQL Statement”.
- Please note that you can use variables in the SQL Statement. Variables are specified with the @ prefix, for example the external number is specified as @Number. The available variables to use are listed here.
- The Contact Creation SQL Statement must return the following case-sensitive columns for the contact created: contactid,firstname,lastname,companyname,email,phonemobile,phonemobile2,phonehome,phonehome2,phonebusiness,phonebusiness2,phoneother,faxbusiness,faxhome,pager,photourl. You can change the name of the columns returned using the same technique explained above for the Lookup SQL Statement.
- For the Contact Creation SQL Statement, you can use a combined statement, inserting the record first, and querying it later. For example:
- When the caller’s number can’t be matched to a contact, the 3CX client will let the user create the contact by filling the details in a dialog.
See Also
- See how to integrate your CRM with the 3CX API.
- Learn how to set up Bitrix24 integration.
- Learn how to set up Freshdesk integration.
- Learn how to set up HubSpot integration.
- Learn how to set up Jetpack CRM integration.
- Learn how to set up Zendesk integration.
- Learn how to set up MongoDB integration.
Last Updated
This document was last updated on 18 December 2023