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

Environment Setup

  1. Go to the OpenAccess SDK landing page for ODBC and Download OpenAccess SDK for ODBC.
  2. On the download page,
    • Under Server and IP SDK, download Windows installers based on your machine architecture (32-bit or 64-bit)
    • Under Client/Server Configuration, download ODBC Client for Windows (32-bit or 64-bit)
    • Under SDK add-ons, download OpenAccess REST IP generator.
  3. 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.
  4. Install the OpenAccess ODBC client by running the installer named oaodbcxx_win_setup.exe. This will install the OpenAccess ODBC driver in your machine.
  5. Download and Install the latest version of Java JDK from Oracle website.

Generating Template Code

  1. 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.
  2. 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.
  3. 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

     

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

  5. OpenAccess Meetup API 1

  6. 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.

  7. OpenAccess Meetup API 2
  8. 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
  9. First we will create schema file for the Meetup.com REST endpoint and then proceed to configuring input.props
  10. 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.
  11. 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.

     

    Groups Endpoint

    Categories Endpoint

    Cities Endpoint

  12. Based on the documentation, I have created the below schema file for Categories endpoint


  13. <?xml version="1.0" encoding="UTF-8"?>
    <tables>
        <table name="Categories">
             
            <column name="id" dataType="4" userData=""/>
            <column name="name" dataType="12" userData=""/>
            <column name="sort_name" dataType="12" userData=""/>
            <column name="shortname" dataType="12" userData=""/>
         
            <stat nonUnique="0" indexName="IDXCATID" indexType="1" seqInIndex="1" columnName="id" cardinality="-1" pages="-1" filterConditions="" />
         
            <pkfk pkColumnName="id" keySeq="1" updateRule="-1" deleteRule="-1" pkName="PKCATID" />
     
        </table>
     
    </tables>




  14. You can access the schema files for Categories, Cities and Groups in this GitHub repository
  15. 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.

     

  16. 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.

     

  17. 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.


  18. DATABASE_NAME=Meetup
    CATALOG_NAME=OpenAccess
    SCHEMA_NAME=Meetup
    DATABASE_VERSION=1.0
    SCHEMA_LOCATION=schema
    SCHEMA_TYPE=XML
    PATHTOWRITE=src
    BASEPACKAGE=com.ddtek
    DATASOURCE=Meetup




  19. 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.
  20. 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

     

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

Setting up the Project

  1. 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.
  2. In addition to that, you need to add OpenAccess java library (C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\oasql.jar) to project build path.
  3. Build the Project.

Setting up OpenAccess Service

  1. From the Start Menu, Go to All Programs -> Progress DataDirect OpenAccess Server and IP XX-Bit SDK 8.X -> Management Console.
  2. On the Management Console, under Manager(localhost), connect to OpenAccessSDK8XX_Agent
  3. 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
  4. Go to the newly created service -> Configuration -> Service Settings and configure it as follows.
    1. 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

         

    2. Logging (during development)
      • ServiceDebugLogLevel: 127
      • SeriviceIPLogOption: Enable Full Tracing

         

  5. 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

       

  6. 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.
  7. Right click on the service and click on ‘Start <ServiceName>’

Writing Code

  1. 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.
  2. 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.
  3. 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.

       

  4. 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.
  5. 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.

Debugging the Code

  1. You can debug the code IntelliJ during development using Remote debugging.
  2. To enable remote debugging, Go to OpenAccess service ->Service Settings -> IP Parmeters and make the below changes.
    1. ServiceJVMOption

      -Xrunjdwp:transport=dt_socket,address=9015,server=y,suspend=n -Xdebug -Xrs

       

  3. Save the Configuration, Restart the service.
  4. In the IntelliJ, Go to Run -> Edit Configurations -> Create Remote Debugging config as shown below.

OpenAccess Meetup API 3

Testing the Driver

  1. Open ODBC Administrator (32 bit if you have installed 32 bit OpenAccess ODBC client, else open 64-bit).
  2. Add New DataSource -> Choose Driver as DataDirect OpenAccess SDK 8.X and click on Finish to configure the driver as shown below.


  3. OpenAccess Meetup API 4

  4. Configure the driver as shown below. The port number is the port that you have given when you created OpenAccess service. 

  5. OpenAccess Meetup API 5
  6. Click on Test Connect to test the connection. If your OpenAccess service is running, you should be able to connect successfully.
  7. Now go to Start Menu -> All Programs -> Progress DataDirect OpenAccess Client for ODBC xx_bit SDK and open Interactive SQL program.
  8. You should now see a terminal, enter the following command to connect to the above ODBC data source.

     

    Connect Meetup2017

     

  9. 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.

     

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


  11. 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.


ODBC TUTORIAL

Build SQL Access to REST APIs using ODBC Driver SDK

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers