A lot my clients use different databases for different applications e.g. Sage Line 50, QuickBooks, Legacy database apps, etc. I have developed several utility apps that synchronizes data from those databases with Salesforce.com. However, I am now wondering what solution can we use to access these databases directly within Apex. Is there something available that works like ODBC connection from right within Salesforce? Is there something available in these databases that exposes meta-data and data both through Webservice Access?
Add a comment
|
3 Answers
Salesforce.com can connect to REST- and SOAP-enabled database servers. Unfortunately, older database servers, such as MySQL, do not support these modern protocols, instead depending on drivers and binary software libraries to expose their interfaces. ODBC is another common example of a "pre-Internet" protocol-- it relies on the existence of system drivers to provide the interface.
This means that, ultimately, any solution with Apex Code requires a middle-man approach. PHP can connect to MySQL and ODBC drivers, for example, so it would be possible to set up a server running PHP that can listen for incoming requests, translate the incoming request into a format that the other database system can understand, execute the command, then return the results back in REST or SOAP format (depending on the exact configuration of PHP code).
At the time of this answer, a cursory search on several popular search engines suggest that there is no open-source solutions written in PHP (or Perl, Ruby, ASP, JSP...) that addresses this need. However, there is an abundance of data on the Internet on how to write various scripts in any modern server scripting language, so it would simply be a matter of stitching that information together into a single coherent script.
No, you cannot directly connect from Salesforce to an outside database. You will need some kind of integration middle-layer, whether it is a webservice that you call or a proprietary solution like Informatica, Jitterbit, Cast Iron, etc. . . .
answered Nov 1, 2013 at 12:39
There are a number of, unfortunately, server-specific tools that enable this to one extent or another. For example, you can use DbAmp to connect sql-server and salesforce.
Additionally, you can use jitter bit/informattica / etc. to do timed bi-directional upserts.
