Integrate Sugar with external database

Recomendation as per best practices is to configure a WebLogicHook after_save from Sugar to a layer at external DB.

But if this is not an option so you can configure a custom db instace which points to external DB and send data from a LogicHook after_save.

We have do that for SugarCRM MySQL to External Oracle and MySQL.

The code may be something like that:

$adapter = null;
$driver_list = DBManagerFactory::getDbDrivers();

dbconfig = array(
	'db_host_name' => 'external_db_hostname',
	'db_user_name' => 'external_db_user',
	'db_password' => 'external_db_password',
	'db_name' => 'external_db_name',
	'db_type' => 'external_db_type', // oci8
);

if(isset($driver_list[$dbconfig['db_type']])) {
	$adapter = $driver_list[$dbconfig['db_type']];
	$adapter->connect($dbconfig, false);
}

Regards

We chose to do a 2 step approach:

An after save logic hook puts the changes into a staging table when the record is changed, then a scheduler reads the recently staged records with "staged" in the status from Sugar and makes the changes in Oracle, when the Oracle update comes back successful then the staged record is changed to status "synced", if there are issues it is reported to the developer that there is a problem and the status is changed to "error".  This allows us to track changes and track the sync. to better handle any errors.

Of course you need to have 1:1 relationship between Sugar Contact and Oracle Contact, and make Sugar the master.
Things can get really messy if you allow your users to create/modify Contacts in both systems.

I highly recommend against any 2-way sync.

FrancescaS

I would second, strongly, the point of avoiding 2 - way sync. 

Regardless of which systems are in play, bidirectional synchronization is a complex topic that can easily cause many problems and potentially lead to data loss.

Hello Rene,

SugarCRM strongly recommends using middleware for the integrations - the middleware deals with Sugar via API avoiding the database layer.
We employ LB DataBridge web-service  - an on-premise data broker, - and use the Logic Builder configuring tool for configuring both external call preparations and response processing for each Exchange Event  - all in Sugar, maximum flexibility, and no coding for integration and further support.

Briefly, the scheme is the following:


It looks like for implementing your case it is needed to provide LB DataBridge web-service with login to Oracle -  to maintain a direct DB connection to Oracle for ApplicationServer-IntegratingSystem interaction - as it is shown on the scheme.

The rest could be configured with the Logic Builder tool on the Sugar side - as I see, that should be insert/update query for your Oracle.

Here is the integration sequence for integration Data Exchange event :

If you have any questions, please feel free to reach out to me dch@integroscrm.com or via Club messaging

Hi André,

Thank you very much, pointing me in that direction! I was not  aware of such an approach. I took this, and built my function around it. An after_save hook rsp. in my case a before_save hook, suits perfectly. 

Thank you!
Rene

Hi Francesca,

Thank you very much for your answer. 

The process at the moment looks like this. The sales admin set a "role"-Field on the contact module to initiate a manual workflow to an admin person. This person creates a new User on a web application (e.g. named as "MVD" ). Currently the "role" Update on contacts module triggers a SUGAR BPM which creates a Task and send an Email to the admin person. This person create the user and close the Task after. So far so good.

My task is to automate the whole process after updating the "role"-field. In this case, a third application (data pool) play a role in addition to CRM and MVD.

With your mentioned approach (suggested staging table), i'll keep the process from updating the contact to create a Task record. Collecting the contact data for "MVD" I would do with a before_save hook on the task module.

As all our customer applications will manly populated from the mentioned data pool (CRM -> Accounts), there exists a process table. So the before_save hook writes a process record into the data pool table (realized with the above help from André Lopes)

From there a DB Procedure create the user in MVD. After successfully processing, the same procedure closes the Task record out from the data pool(via REST). 

The contacts (MVD-Users) will be administrated only out from CRM. Admin people can't login to MVD (anymore) nor to the data pool

I hope this is understandable and also looks quiet similar to what you have suggested from a CRM point of view.

Rene

Hi Andre,

Is there a list of available db_type values somewhere?

Thanks

Hello Polys,

Nice to find you in the Club

Probably the list of values depends on the list of drivers available https://support.sugarcrm.com/Knowledge_Base/Platform_Management/Determining_Which_Database_Sugar_is_Running/

Please note, that according to Sugar Cloud Policy there is a restriction for Sugar Cloud instances, that "Outbound HTTP connections must have timeouts under 1 second."

AFAIK, SugarCRM strongly suggests a middleware approach - before implementing outbound connections for production, I would suggest checking with Sugar Support whether limitations on other types of outbound connections for the Cloud instances.

LBDataBridge webservice fits the recommendations as middleware for Sugar integrations.

Hi André!

I'm researching if exists some way to stablish a connection to an external SQL DataBase from a LogicHook, and I found your post...

So, once the adapter is connected ($adapter->connect())

Which is the form to make querys with this one?

Regards,

Norberto Baltézar.

To connect SugarCRM v9 with an external Oracle 19 database, you can use Skyvia's integration services. Skyvia can detect field changes in the Contacts Module and trigger data flows to update the external database accordingly. Given that both databases are Oracle 19, Skyvia can use DBlink or direct login to establish the connection, facilitating real-time data synchronization between the two databases.