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

Posted on 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
  • DISTINCT
  • UNION
  • Scalar Functions
  • Codeless support for Bulk APIs without changing a single line of code for:
    • BULK INSERT
    • 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

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

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

Loading animation