SQL access via ODBC/JDBC versus web services APIs to SaaS in the cloud

SQL access via ODBC/JDBC versus web services APIs to SaaS in the cloud

May 17, 2012 0 Comments

I have been engaged with an increasing number of organizations that have started down the path of purchasing, building or contracting a one-time application specific custom integration against Salesforce.   Then the business migrates or adds a new Software as a Service (SaaS) CRM, HR or ERP system putting them back at square one.

The more things change, the more they stay the same.

20 years ago, ODBC solved the challenges of DBMS independence by using a driver as a standards based translation layer between the application and data source.  Progress DataDirect has taken this proven approach to provide SaaS independence going forward with standards based ODBC and JDBC drivers.

DataDirect has been accessing SaaS data in real time via SQL access since 2006.

Before learning about the DataDirect Connect XE Salesforce, Database.com and Force.com drivers, organizations have shared their hesitation about real time access to SaaS data. But now that our next generation driver architecture is running in production environments across the globe, the game has completely changed.


SaaS SQL architecture

We are now getting requests for additional data sources including: Netsuite, Oracle OnDemand, Sugar CRM, Workday, Microsoft Dynamics, Eloqua, Amazon, Aplicor, and many others.  Our product team is actively engaged with 300+ OEM partners and architects from SMBs to the Fortune 500 to meet the world's SaaS data access demands; and we'd love to hear from you.

Who are some SaaS vendors on board with SQL access to cloud data sources?

What are example cases where shops switched from direct calls to Salesforce API to SQL access via Connect XE ODBC/JDBC drivers:

1. Hand-coded custom web services API integration "blew up".  

A Salesforce shop was using hard coded web service calls from Oracle middleware to the Salesforce.com API with certain assumptions about their on premise applications.  The integration failed for different reasons whenever an on-premise patch was applied or a Salesforce API version was changed (~every 3 months).  Being several months behind schedule, they were able to get the POC working within days using SQL based applications in their Oracle environment with the DataDirect Connect XE for ODBC Salesforce driver.

I learned about this very same challenge integrating applications with Salesforce from a presentation at Collaborate 12, Oracle's User Conference, by Prisio Technologies: #9293 - Considering Salesforce.com? What You Need to Know as an Oracle e-Business Suite Customer. They explain the challenges in using two systems and how the release cycles affect integration decisions.

2. Speed up real-time access from web service API.

A loan processing system required integration with Salesforce in real time for a customer facing web application, however response times were becoming too slow from the SOAP integration in place.  This shop came to us looking for improved performance and efficient data access using the driver's SQL-92 interface for joining Salesforce objects.

They are building the data access layer against the DataDirect Connect XE for JDBC Salesforce driver to leverage the built in change data capture caching feature that can be configured per Salesforce object.  Performance will increase significantly since only the changes are requested through the API whenever the refresh interval elapsed.

This shop is also interested in the bulk load interface built into the driver for loading data from a batch process.

3. Get real-time access to a variety of cloud sources from Business Intelligence or Data Warehousing tools.

A shop is planning their BI requirements going forward and has Netsuite, Salesforce and an on premise relational database.  The initial plan was to create a local relational database to sync the SaaS data using web service calls for near real time access.  Instead, they chose the direct ODBC interface from Tableau for real-time connectivity with the DataDirect Connect XE for ODBC Salesforce driver and Netsuite ODBC driver (built using OpenAccess SDK for ODBC) provided by NetSuite for their SaaS data sources.

4. Leverage existing development skill sets to boost productivity and reduce costs.

DataDirect built the Connect XE cloud drivers specifically for accessing SaaS data sources so your team can focus on application development versus learning the details of the Salesforce schemas, query language, network performance, and Web service APIs.  For this case, I looked internally to our R&D team that built the Salesforce.com ODBC and JDBC drivers; and they shared their perspective on application development:

Applications need to write to three different Salesforce APIs to get all of the equivalent driver functionality

  • Web Service
  • Bulk
  • Metadata

Additional features provided by the driver

  • Full Group By and Aggregate support for arbitrary length results
  • Views
  • Caching
  • Invoke Reports
  • Join arbitrary objects
  • CASE Statements
  • Scalar Functions
  • Codeless support for Bulk APIs without changing a single line of code for:
    • DELETE
    • UPDATE

DataDirect solved data connectivity to SaaS applications, but do they have an answer on how to pronounce "SaaS"?

In Morrisville, NC; we try to avoid pronouncing it as "SAS" to avoid confusion with SAS Institute down the road.  The more local popular pronunciation is "sauz", but then we confuse our clients and make the R&D team crave BBQ.   Wikipedia says to pronounce it as "sæs"; but the claim still needs a reliable source.  Maybe we'll just stick to data connectivity and avoid phonetics.

Post a comment to share your experiences where SQL access to SaaS data was the preferred solution.  Or call 1-800-876-3101 to speak with a live Solutions Consultant to learn more about SaaS data connectivity.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Comments are disabled in preview mode.
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
ProgressNEXT: Premier Event for Modern Application Development
Read More
Getting Started with Kinvey
Read More
Low-Code Platforms: What Developers Think and Why
Read More