Connect to Outlook Data in JRuby

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

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

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

Using OAuth Authentication

Microsoft Graph API uses OAuth 2.0 for authentication. You must register an application in the Microsoft Azure Portal to obtain OAuth credentials (Client ID and Client Secret).

Obtaining OAuth Credentials

1. Log in to the Azure Portal.
2. Navigate to Azure Active Directory > App registrations.
3. Click New registration to create a new application.
4. Enter an application name and select the appropriate account types.
5. Set the Redirect URI to your application's callback URL (e.g., http://localhost:33333 for desktop apps).
6. Click Register to create the application.
7. On the application overview page, copy the Application (client) ID - this is your OAuthClientId.
8. Navigate to Certificates & secrets and create a new client secret.
9. Copy the client secret value - this is your OAuthClientSecret.
10. Navigate to API permissions and add the required Microsoft Graph API permissions:
    • Mail.Read - For accessing email messages
    • Contacts.Read - For accessing contacts
    • Calendars.Read - For accessing calendar events
    • Tasks.Read - For accessing To Do tasks
    • offline_access - For obtaining refresh tokens
11. Click Grant admin consent to grant these permissions.

Connecting with OAuth

After setting the following connection properties, you are ready to connect:

• AuthScheme: Set this to OAuth.
• InitiateOAuth: Set this to GETANDREFRESH. The CData API Profile for Outlook will automatically walk through the OAuth process in order to obtain the access token.
• OAuthClientId: Set this to the Application (client) ID from Azure Portal.
• OAuthClientSecret: Set this to the client secret value from Azure Portal.
• TenantId: Set this to your Azure AD tenant identifier (GUID or domain name like 'contoso.onmicrosoft.com').
• CallbackURL: Set this to the Redirect URI you specified in your app registration (e.g., http://localhost:33333 for desktop apps).

Example connection string

Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;

Built-in Connection String Designer

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

java -jar cdata.jdbc.api.jar

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

Below is a typical JDBC connection string for Outlook:

jdbc:api:Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;

Create a JRuby App with Connectivity to Outlook Data

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

url = "jdbc:api:Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;"
conn = java.sql.DriverManager.getConnection(url)
stmt = conn.createStatement
rs = stmt.executeQuery("SELECT ,  FROM CalendarGroupCalendars")
while (rs.next) do
  puts rs.getString(1) + ' ' + rs.getString(2)
end

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

jruby -S APISelect.rb

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