Simple SQL Access to Salesforce.com

I wonder how many people remember one of Apple's very first iMac ads, about how easy it was to get online?

Apple iMac Commercial - 3 Steps (via YouTube)

Jeff Goldblum steps the user through the three steps needed to get online with the new iMac.

First, you plug in the machine.

Next, you get connected.

Step three - there is no step three! It's so simple, there is no step three! And then you hear him start laughing about how absurdly easy that was!

The new Connect XE JDBC Driver for Salesforce.com makes it almost as easy to access Salesforce.com from either custom or shrink-wrapped java applications!

Let's look at what's required to setup this driver to access Salesforce.com. (You must be running J2SE 5 or higher to use the driver.)

Step 1 - Download the driver

Step 2 - Install the driver

Step 3 - Configure your application!

Step 4 - There is no step four!

Ok, that's not QUITE as simple as getting an iMac online, but compared to installing and configuring many of the complicated, albeit very powerful integration servers often used to access Salesforce.com, it's pretty close!

Let's expand these steps. Downloading the driver installer is pretty straightforward. A fully functional evaluation copy of the driver is available at the datadirect.com website. Fill out a simple form and download the installer.

The native java installer is only 10 MB!

Initiating the installation can either be done by simply double-clicking the PROGRESS_DATADIRECT_CONNECTXE_JDBC_4.2.0.jar file if you're not on Windows, or the Setup.exe file, if you are. (Command-line installation is also available for those more comfortable with a terminal prompt!)

Direct the installer to install the driver (actually a collection of .jar files) in the desired location. By default, the installer creates a "DataDirect" folder at the root level of the system, with a "ConnectXE" folder inside that. Of course, it can be over-ridden, allowing installation virtually anywhere. In this case, the installation is directed to /Users/gstasko/Progress/DataDirect/ConnectXE_for_JDBC_42 on my system.

Installation Complete!

Now let's configure an application to use this driver - a shrink-wrapped application that was designed independently of this driver, but one that supports the JDBC specification.

In this case, the application used will be Aqua Data Studio (ADS) from Aquafold.com. Aqua Data Studio is a JDBC-based desktop query application. I especially like using an application with a graphical interface for posts like this - much easier to grasp what's happening, compared to having to configure text files with sometimes cryptic settings. But that's just me! There's another post by Jonathan Bruce that walks through using the driver with a command-line application. There is even a YouTube video available!

ADS is preconfigured "out of the box" for many frequently encountered databases, such as Oracle or DB2. However, it also has a "Generic - JDBC" option, which allows users to use virtually any JDBC-compliant driver. A driver such as the Progress DataDirect ConnectXE for Salesforce.com JDBC driver!

Here's the panel where a JDBC driver is defined for use by ADS.

As expected, there are numerous parameters that ADS requires to use a generic JDBC driver. This information must be provided to an application that calls a JDBC driver, regardless of which driver it is. A URL that properly describes how to reach the datasource, the driver or data source classes, and the location of the driver.

The only item that may be somewhat atypical with the Salesforce.com JDBC driver is specifying the location of the driver. This driver is actually made up of multiple jars, and the location of each must be specified in the driver location. Otherwise, it's "vanilla" JDBC!

Valid connection URLs are described in the Reference Guide, page 32, all of which build upon the basic form of "jdbc:datadirect:sforce:.

With ADS, a basic configuration to use the Progress DataDirect ConnectXE JDBC Driver for Salesforce.com might look like this.

You can see the required three data points are specified - The URL, the driver class, and the location of all the JAR files that make up the driver.

With ADS, once the configuration pane is completed and "OK" is clicked, the main window of the application appears. If there are no error messages, ADS will automatically connect to the specified datasource, and there should be a new entry in the list of servers on the left, as shown below.

With ADS, we instantiate a "Query Analyser" to have a workspace to run our queries from, and a query can either be built interactively using the GUI, or simply typed in as with virtually any SQL editor. Here, a query has been created and run, asking to locate accounts where the name is "PROGRESS".

In just a matter of minutes, we were able to install and configure JDBC access to Salesforce.com! As simple as defining access to Oracle or DB2 or SQL Server! Get a driver, configure it, and go!

The best proof is for you to test the driver in YOUR environment. Whether you're an ISV with a COTS application, or a corporate development shop with internally-written applications, see how easy it can be to add Salesforce.com to your list of supported data sources!

Update - with the Connect releases of early 2012, this same SQL access is available with Force.com and Database.com!