Integrate Sugar with external database

Question

Hi everyone, 
 I need to connect Sugar CRM (v9) with an external database. Specifically, I want to react to the change of a field on the Contacts Module. Information of the changed record should then be transferred to another database. The remote database is only accessible via DBlink or direct login. Sugar DB and the remote DB are Oracle 19 DB's. 
 First thought was a trigger on the database (field level - Oracle PLSQL). But I would like to avoid that. I also don't really want to set up a scheduler. 
 Were there other approaches or best practice to reach this. Any ideas were very appreciated. 
 Thank you in advance! Rene

Rene Ryhner · Answer

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

Rene Ryhner · Answer

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

Rene Ryhner · Answer

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 u se 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