In today’s blog I would like to present a way how to communicate with two different target systems with the use of simple SQL statements. For this purpose I will be using Connect Bridge and its SugarCRM and Exchange connectors. The integration is live and bi-directional. All entities are supported. The following is just one of any integration scenarios between the two systems.
Why to use Connect Bridge?
Usually, in order to be able to communicate with 2 different target systems, in our case a MS Exchange server and SugarCRM, the developer would need to know the API of each target system. This requires some studying of the API in order to understand how they work and how to write the source code.
With the use of Connect Bridge and its SugarCRM and Exchange connector, the time necessary to understand and be able to successfully implement the solution is significantly reduced. The main differences are that Connect Bridge allows the developer to use simple SQL statements to communicate with each target system. This is not possible when using the API of the target system itself, because of different schemas and architectures used to develop the target system. Connect Bridge also allows connection to multiple target systems at the same time by using the available connectors. For more information about Connect Bridge visit the web page.
What do we need?
1. Firstly we will need credentials to connect to Exchange and SugarCRM. As we are going to be using Connect Bridge, we will need a running Connect Bridge server with Exchange and SugarCRM connectors.
2. For our demonstration, let’s assume that accounts for SugarCRM and Exchange were already created in the Connect Bridge server. The user credentials for each target system are used during the setup of each account so the Connect Bridge knows what credential to use when communicating with the target system (Exchange credentials for communication with Exchange and SugarCRM credentials for communication with SugarCRM).
4. For exchange connector we will use account with name ‘Exchange’ and for SugarCRM we will use account with name ‘localSugarCRM’.
5. I will also be using Connect Bridge query analyzer tool to demonstrate the SQL statements and the output that is returned by Connect Bridge server.
Create a lead in the SugarCRM
To create a new lead in SugarCRM, select the localSugarCRM connector account in query analyzer.
and execute the following SQL statement :
insert into Leads(first_name,last_name, description)
values (‘Janko’,’Hrasko’,’example lead’);
The select scope_identity() returns the ID of the newly created Lead, that will be used later in order to execute a select statement.
As you can see from the following screenshot of SugarCRM GUI, the Lead was successfully created.
Synchronize a SugarCRM Lead into an Exchange Contact
Now with the use of Query analyzer, execute a select statement to get back the data from SugarCRM. In the connectors combo box select the localSugarCRM connector account to execute the statement towards the SugarCRM system.
We will execute the following SQL statement:
select id, first_name, last_name, description
from Leads where id = ‘1183a8cb-4bff-584c-9a89-56b426a8f170’;
From the output of query analyzer we can see that the communication works both ways and the returned data are the same data that were used in the insert statement.
In order to create a contact in Exchange, in query analyzer select the Exchange connector account in the combo box to change the target system where to execute the SQL statements.
For the SQL statement, we will use the data that were returned by the previous select statement in SugarCRM connector.
Because of different schema of each target system, a slight modification has to be made to the insert statement. We need to change the name of the columns to the correct name used by the target system. Our final SQL statement will look like this:
insert into Contact(givenname, surname, subject)
values (‘Janko’,’Hrasko’,’example lead’);
As you can see from the following screenshot of MS Exchange OWA, the contact has been created.
From this short demonstration you can see how fast the data can be accessed with minimum effort. The developer can use any kind of coding language and use ODBC, JDBC or web services to communicate with Connect Bridge server. The simple use of SQL statements across multiple target systems makes the development process more efficient and faster than using direct APIs of the target systems. A skilled developer can create various synchronization scripts for different target systems and develop powerful cross target system synchronization tools. To test the power of Connect Bridge, request your free 30-day trial today.