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?
[SalesForce] How to access SQL, Oracle and Access databases from within Salesforce
Related Solutions
I have solved it myself after a long research. The solution is to get connected to SharePoint Online with OAuth and leverage the power of SharePoint 2013 REST APIs. Features covered:
- Get authenticated
- Upload files with metadata
- Retrieve Files with metadata
- check permissions
I have put all of my effort results as posts on my blog. Below are the blog post links.
This seems timely as Grumpy just popped out of the developer console at me.
As per @sfdcfox's comment and answer to the linked question.
The Seven Dwarfs live in the platform code. You should never see DOPEY, SLEEPY, DOC, GRUMPY, SNEEZY, BASHFUL, or HAPPY, but occasionally they break out and appear to a user. If you spot one of them, you need to contact support so they can be put back in their dwarfy walled garden.
There are references to them back in 2009. See Salesforce, Snow White and the Seven Dwarfs and The Silver Lining - Meaningful Error Messages #94.
I suspect anyone who can tell you exactly where they are in relation to the database has probably signed a non-disclosure agreement. That doesn't mean we can't make some wild conjectures.
The presence of Oracle error codes like ORA-00001 and ORA-20191 plus the java.sql.SQLException would indicate they are some sort of layer immediately above the database running Java.
Its also interesting that they tend to bypass the GACK. Either they aren't being caught by the GACK mechanism or they are being rethrown. The latter seems unlikely. Why surface these errors rather than the generic GACK message. So the dwarfs live outside the standard application GACK error handling.
Why are there only 7? I'd assume it has something to do with the way Salesforce scales. Each instance/pod, e.g. na7, may have exactly 7 dwarfs. There are never any more or less and they would stand up a new pod rather than scale out the dwarfs. If you've got seven fixed servers I guess you are either naming them after dwarfs or deadly sins. Getting errors from Happy are probably better than errors from Lust or Gluttony.
Here's slide 8 from a Salesforce.com -architecture talk that I've defaced with where I think the dwarfs roughly reside in relation to the servers that makeup a pod and it's database cluster.
Here's another diagram from The Force.com Multitenant Architecture pdf.
The Query Servers might be in about the right place and performing the expected dwarf functions.
In Performance Monitoring and Testing in the Salesforce Cloud the Pod is shown as "30 plus servers" being used for Application,API, and Search servers over a "8 Node RAC cluster". I've never done infrastructure at that scale, but I suspect there is something working between all those servers and the database cluster.
See also: * Quora: What does Salesforce's infrastructure look like?
Best Answer
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.