Connect to Sugar CRM Data in JRuby

JRuby is a high-performance, stable, fully threaded Java implementation of the Ruby programming language. The CData JDBC Driver for Sugar CRM makes it easy to integrate connectivity to live Sugar CRM data in JRuby. This article shows how to create a simple JRuby app that connects to Sugar CRM data, executes a query, and displays the results.

Configure a JDBC Connection to Sugar CRM Data

Before creating the app, note the installation location for the JAR file for the JDBC Driver (typically C:\Program Files\CData\CData JDBC Driver for Sugar CRM\lib).

JRuby natively supports JDBC, so you can easily connect to Sugar CRM and execute SQL queries. Initialize the JDBC connection with the getConnection function of the java.sql.DriverManager class.

The User and Password properties, under the Authentication section, must be set to valid SugarCRM user credentials. This will use the default OAuth token created to allow client logins. OAuthClientId and OAuthClientSecret are required if you do not wish to use the default OAuth token.

You can generate a new OAuth consumer key and consumer secret in Admin -> OAuth Keys. Set the OAuthClientId to the OAuth consumer key. Set the OAuthClientSecret to the consumer secret.

Additionally, specify the URL to the SugarCRM account.

Note that retrieving SugarCRM metadata can be expensive. It is advised that you store the metadata locally as described in the "Caching Metadata" chapter of the help documentation.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the Sugar CRM JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.sugarcrm.jar

Fill in the connection properties and copy the connection string to the clipboard.

Below is a typical JDBC connection string for Sugar CRM:

jdbc:sugarcrm:User=MyUser;Password=MyPassword;URL=MySugarCRMAccountURL;CacheMetadata=True;

Create a JRuby App with Connectivity to Sugar CRM Data

Create a new Ruby file (for example: SugarCRMSelect.rb) and open it in a text editor. Copy the following code into your file:

require 'java'
require 'rubygems'
require 'C:/Program Files/CData/CData JDBC Driver for Sugar CRM 2018/lib/cdata.jdbc.sugarcrm.jar'

url = "jdbc:sugarcrm:User=MyUser;Password=MyPassword;URL=MySugarCRMAccountURL;CacheMetadata=True;"
conn = java.sql.DriverManager.getConnection(url)
stmt = conn.createStatement
rs = stmt.executeQuery("SELECT Name, AnnualRevenue FROM Accounts")
while (rs.next) do
 puts rs.getString(1) + ' ' + rs.getString(2)
end

With the file completed, you are ready to display your Sugar CRM data with JRuby. To do so, simply run your file from the command line:

jruby -S SugarCRMSelect.rb

Writing SQL-92 queries to Sugar CRM allows you to quickly and easily incorporate Sugar CRM data into your own JRuby applications. Download a free trial today!