Build SQL Access to REST APIs using ODBC Driver SDK

Introduction

Do you have a custom API that you use in your company internally and would like to connect to your favorite analytics tool or integrate with any other tool using standards-based connectivity like ODBC?

Do you have a data source which has a REST API and doesn’t have a ODBC driver, but you would like to have one for your analytics or integration purposes?

Then you're at the right place! This tutorial will get you started in building your own ODBC driver using Progress DataDirect OpenAccess SDK. In this tutorial, I will build an ODBC driver for the popular event organizer Meetup.com API

Install the OpenAccess server by running the installer named oaserverxx_win_setup.exe.

On the Product registration, Enter your name and company. Enter serial as ‘EVAL’ and leave the Key textbox empty for 15-day trial period.

For the all the next steps in installation, leave the default settings as it is and proceed to install the OpenAccess server.

Install the OpenAccess ODBC client by running the installer named oaodbcxx_win_setup.exe. This will install the OpenAccess ODBC driver in your machine.

Download and Install the latest version of Java JDK from Oracle website.

Back to top

Generating Template Code

To get you started with OpenAccess SDK easily with least friction, we have released a template code generator for writing your own ODBC driver called OpenAccess REST IP Generator.

Copy the oa_rest_ip_generator_8_X_X.jar to your workspace and create a new folder for the project. For future references in the tutorial, I named my project folder Meetup2017.

Extract the oa_rest_ip_generator_8_X_X.jar by running the following command on your command prompt

jar xvf oa_rest_ip_generator_8_X_X.jar

This will generate a new folder called ip with a structure as shown below

Move the contents of RestGenerator folder from the extracted files to the new folder that you have created in step 2 above. Below is my folder structure for your reference.

Before you start generating template code, you need to do two things.

Build a schema file for the API endpoint

Provide configuration info in input.props

First we will create schema file for the Meetup.com REST endpoint and then proceed to configuring input.props

Create a new folder named schema in your project folder. In that folder create a file called Cities.xml, Categories.xml and Groups.xml, where Cities, Categories and Groups are the names of the table that I am going to expose through ODBC and the file holds the schema information for the Meetup.com endpoints.

Following are the Meetup.com endpoints that I am going to use for this tutorial. Each endpoint corresponds to a xml file from the previous step.

You can access the schema files for Categories, Cities and Groups in this GitHub repository

Notice that each column has datatype associated with it, if you want to know about datatypes and their corresponding ID, visit the following documentation page.

Along with that you can also add more information like Primary keys, foreign keys and indexes. To learn more about the schema file options, go to page 20 in this document.

With the schema done, next up is configuring input.props file. You provide basic details such as Database name, schema name, schema files location, path where template code must be generated, base class name and base URL for REST API you are building the driver. Below is my properties file.

Notice the property PATHTOWRITE where I specified src as its value, which means the code is going to be generated in the src folder with in your project folder. Save the file and close it after you have finished editing.

In the root folder of your project, open command prompt and run the following command to generate the code template.

java -jar oarestipgen.jar input.props

You should now see a new folder named src created with template code in it.

Back to top

Setting up the Project

Open IntelliJ IDE and import the project folder in to your workspace. By default, all the libraries in the path-to\Meetup2017\lib folder should be added to build path.

Setting up OpenAccess Service

On the Management Console, under Manager(localhost), connect to OpenAccessSDK8XX_Agent

Right Click on Services Folder -> New -> Services to create a new service. You should now see a Add Service window. Follow the below instructions to create the service successfully.

Service Type: Service for Java

Service Name: <Any name that you choose>

TCP Port: <Any port between 1024 and 65534>

Check the Register Service check box

Go to the newly created service -> Configuration -> Service Settings and configure it as follows.

IP Parameters

ServiceJVMLocation: C:\Program Files\Java\jdk1.X.X_xxx\jre\bin\server

ServiceJVMClassPath:

C:\Program Files\Progress\DataDirect\oaserver8X\ip\oajava\oasql.jar;

\path\to\Project\out\production\<project>\;

\path\to\Project\lib\cxf-api-2.7.8.jar;

\path\to\Project\lib\cxf-rt-bindings-xml-2.7.8.jar;

\path\to\Project\lib\cxf-rt-core-2.7.8.jar;

\path\to\Project\lib\cxf-rt-frontend-jaxrs-2.7.8.jar;

\path\to\Project\lib\cxf-rt-transports-http-2.7.8.jar;

\path\to\Project\lib\jackson-core-asl-1.8.10.jar;

\path\to\Project\lib\jackson-mapper-asl-1.8.10.jar;

\path\to\Project\lib\javax.ws.rs-api-2.0-m10.jar;

\path\to\Project\lib\wsdl4j-1.6.3.jar;

Note: If you use eclipse, replace the 2nd line in the path with \path\to\Project\bin

ServiceIPModule: oadamipjava.dll

Logging (during development)

ServiceDebugLogLevel: 127

SeriviceIPLogOption: Enable Full Tracing

Go to DataSource Settings -> Default and configure IP Parameters it as follows.

DataSourceIPType: DAMIP

DataSourceIPClass: com/ddtek/common/ip/RestIP

DataSourceIPCustomProperties: key=?

DataSourceIPProperties:

RESOURCE_PROPERTIES=\path\to\Project\Schema

Notice the DataSourceIPCustomProperties value, it’s a way of how you can pass any values to the code other than credentials. You will be able to access the API key needed by you for accessing the Alpha Vantage API in the code from a Java Map object.

Right click on the service and click on ‘Start <ServiceName>’

Back to top

Writing Code

When you use REST IP Generator to generate the template code, we take care of 95% of heavy lifting for you. All you must do is build a request URL for API with respect to the SQL query issued, send it, parse the response and return the data to OpenAccess.

Go to your project in eclipse and go to the package com.ddtek.<schemaname>.dataprocessor, you should find three classes called CategoriesDataProcessor.java, CitiesDataProcessor.java and GroupsDataProcessor.java. Each java file corresponds to each table that you have created schema for. This is the only class that you might have to edit to get a basic POC up and running.

In these JAVA classes, you can see there are two methods that Override the methods from DataProcessor class.

buildRequest method

This is the method where you observe the conditions for the SQL query that was issued to the driver, build a corresponding request URL for the Alpha Vantage API and send it to OpenAccess. The template code will handle making the request to server and providing the response to the parseJSONResponse method

parseJSONResponse method

In this method, you will be provided the response to the request that you built in the buildRequest method. All you must do is parse the response, send it back to OpenAccess in a ArrayList Object filled with HashMap object.

You can access the project on GitHub for your reference. Also, here is a quick link for you to access the three JAVA classes that I have written for each table.

Once you have written your code, save it and build it.

IMPORTANT: Once you have built the code, you would have to restart the OpenAccess service that you have created above to see the changes.

Back to top

Debugging the Code

You can debug the code IntelliJ during development using Remote debugging.

To enable remote debugging, Go to OpenAccess service ->Service Settings -> IP Parmeters and make the below changes.

You should now see a terminal, enter the following command to connect to the above ODBC data source.

Connect Meetup2017

You should now be able to run queries. If you have started debugging in IDE and placed breakpoints, you should be able to debug too simultaneously.

Here are sample queries you should be able to run, if you have used my code from GitHub.

SELECT * FROM GROUPS WHERE zip=27601

SELECT * FROM GROUPS WHERE COUNTRY='US' and STATE='CA' AND CITY='San Jose'

SELECT * FROM CITIES WHERE COUNTRY='us' and STATE='NC'

SELECT * FROM CITIES

SELECT * FROM CATEGORIES

We hope this tutorial helped you to build your own custom ODBC driver using Progress DataDirect OpenAccess SDK. If you have any questions/issues, feel free to reach out to us, we will be happy to help you during your evaluation.

Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.