VOOZH about

URL: https://www.cdata.com/kb/tech/acumatica-jdbc-jruby.rst

⇱ Connect to Acumatica Data in JRuby

Connect to Acumatica Data in JRuby

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Create a simple JRuby app with access to live Acumatica data.

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

Configure a JDBC Connection to Acumatica 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 Acumatica\lib).

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

Set the following connection properties to connect to Acumatica:

  • User: Set this to your username.
  • Password: Set this to your password.
  • Company: Set this to your company.
  • Url: Set this to your Acumatica URL, in the format http://{Acumatica ERP instance URL}/entity/{Endpoint name}/{Endpoint version}/.
    For example: https://acumatica.com/entity/Default/17.200.001/

See the Getting Started guide in the CData driver documentation for more information.

Built-in Connection String Designer

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

java -jar cdata.jdbc.acumatica.jar

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

👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)

Below is a typical JDBC connection string for Acumatica: 

jdbc:acumatica:Url = https://try.acumatica.com/ISV/entity/Default/17.200.001/;User=user;Password=password;Company=CompanyName;

Create a JRuby App with Connectivity to Acumatica Data

Create a new Ruby file (for example: AcumaticaSelect.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 Acumatica 2018/lib/cdata.jdbc.acumatica.jar'

url = "jdbc:acumatica:Url = https://try.acumatica.com/ISV/entity/Default/17.200.001/;User=user;Password=password;Company=CompanyName;"
conn = java.sql.DriverManager.getConnection(url)
stmt = conn.createStatement
rs = stmt.executeQuery("SELECT Id, location_displayname FROM Events")
while (rs.next) do
 puts rs.getString(1) + ' ' + rs.getString(2)
end

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

jruby -S AcumaticaSelect.rb

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