Server Side Microsoft SQL Server, MySQL, PostgreSQL Database Integration (2024)

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 contactfrom 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 Chromeand Microsoft Edge.

Step 1: Configure 3CX

1. 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.
2. 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.:

1. myServerName

2. myServerName\myInstanceName

3. 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.

3. 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

   See Also

   3CX Phone SystemThe 3CX IP PBX Tutorial19 Best 3CX Alternatives & Competitors (Free & Paid) in 2024What is IVR?
   ,

   pager

   ,

   photourl

   . If your database table has different column names, you can change the name returned as follows:

SELECT id as contactid, first_name as firstname, last_name as lastname, email as email, phone as phonebusiness, mobile as phonemobile, fax as faxbusiness FROM contacts WHERE phone LIKE CONCAT('%',@Number,'%') or mobile like CONCAT('%',@Number,'%')or fax LIKE CONCAT('%',@Number,'%')

4. 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:

SELECT id as contactid, first_name as firstname, last_name as lastname, email as email, phone as phonebusiness, mobile as phonemobile, fax as faxbusiness FROM contacts WHERE email = @Email

5. 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

   See Also

   How to Develop a 3CX Plugin
   ,

   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:

SELECT id as contactid, first_name as firstname, last_name as lastname, email as email, phone as phonebusiness, mobile as phonemobile, fax as faxbusiness FROM contacts WHERE first_name LIKE CONCAT('%',@SearchText,'%')or last_name LIKE CONCAT('%',@SearchText,'%')or phone LIKE CONCAT('%',@SearchText,'%')or mobile like CONCAT('%',@SearchText,'%')or fax LIKE CONCAT('%',@SearchText,'%')

6. 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

1. If you want to report external calls to the database, check the Enable Call Journaling checkbox and configure the Call Journaling SQL Statement.
2. 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.
3. Your Call Journaling SQL Statement should be an INSERT statement, for example:

For MS SQL:

INSERT INTO calls (contactnumber, agentextension, description, calldatetime, callduration) VALUES (@Number, @Agent, '3CX PhoneSystem Call', CONVERT(VARCHAR, @CallStartTimeUTC,127), @Duration)

For MySQL:

INSERT INTO calls (contactnumber, agentextension, description, calldatetime, callduration) VALUES (@Number, @Agent, '3CX PhoneSystem Call', DATE_FORMAT(@CallStartTimeUTC, '%Y-%m-%dT%H:%i:%s%Z'), @Duration)

For PostgreSQL:

INSERT INTO calls (contactnumber, agentextension, description, calldatetime, callduration) VALUES (@Number, @Agent, '3CX PhoneSystem Call', TO_CHAR(@CallStartTimeUTC, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'), @Duration)

4. If you want to report chats to the database, check the Enable Chat Journaling checkbox and configure the Chat Journaling SQL Statement.
5. 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.
6. Your Chat Journaling SQL Statement should be an INSERT statement, for example:

For MS SQL:

INSERT INTO chats (email, agentextension, subject, description, calldatetime, callduration) VALUES (@Email, @Agent, '3CX PhoneSystem Chat Session', @ChatMessages, CONVERT(VARCHAR, @ChatStartTimeUTC,127), @Duration)

For MySQL:

INSERT INTO chats (email, agentextension, subject, description, calldatetime, callduration) VALUES (@Email, @Agent, '3CX PhoneSystem Chat Session', @ChatMessages, DATE_FORMAT(@ChatStartTimeUTC, '%Y-%m-%dT%H:%i:%s%Z'), @Duration)

For PostgreSQL:

INSERT INTO chats (email, agentextension, subject, description, calldatetime, callduration) VALUES (@Email, @Agent, '3CX PhoneSystem Chat Session', @ChatMessages, DATE_FORMAT(@ChatStartTimeUTC, '%Y-%m-%dT%H:%i:%s%Z'), @Duration)

Step 3: Configure Contact Creation

1. 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”.
2. 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.
3. The Contact Creation SQL Statement must return the following case-sensitive columnsfor 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.
4. For the Contact Creation SQL Statement, you can use a combined statement, inserting the record first, and querying it later. For example:

INSERT INTO contacts (first_name, last_name, phone) VALUES (@FirstName, @LastName, @Number);SELECT id as contactid, first_name as firstname, last_name as lastname, phone as phonebusiness FROM contacts WHERE phone = @Number

5. 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 Freshdeskintegration.
• Learn how to set up HubSpotintegration.
• Learn how to set up Jetpack CRMintegration.
• Learn how to set up Zendeskintegration.
• Learn how to set up MongoDBintegration.

Last Updated

This document was last updated on 18 December 2023

https://www.3cx.com/docs/sql-database-pbx-integration/