Build your own Custom ODBC driver for REST API - Quick Start

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?

If so, 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 a free financial REST API provided by the Alpha Vantage Stock API Platform, which offers real time and historical stock market data. Please refer to this stock market analysis that describes how stock API providers typically adjust historical prices based on corporate events such as splits and dividends.  

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.

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.

1. 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 Stocks-Alphavantage.
2. Extract the oa_rest_ip_generator_8_X_X.jar by running this on your command prompt:
   jar xvf oa_rest_ip_generator_8_X_X.jar
3. This will generate a new folder called ip with a structure as shown below




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




5. 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
6. First we will create schema file for the Alpha Vantage REST endpoint and then proceed to configuring input.props
7. Create a new folder named schema in your project folder. In that folder create a file called TimeSeries.xml, where TimeSeries is the name of the table that I will expose through ODBC and the file holds the schema information for the Alpha Vantage endpoint.
8. I am going to be using the Alpha Vantage Time Series Data API in this tutorial and make sure that I support below functions of the Time Series Data API
   
   • TIME_SERIES_INTRADAY
   • TIME_SERIES_DAILY
   • TIME_SERIES_DAILY_ADJUSTED
   • TIME_SERIES_WEEKLY
   • TIME_SERIES_MONTHLY
9. Before going any further with the tutorial, I strongly recommend to get a bit familiar with the API which is a simple API and shouldn’t take long to understand.
10. If you have noticed for all the above functions in the Time Series Data API of Alpha Vantage, the responses for these API have two main parts.
    
    • Metadata section which basically describes the data.
    • Time Series Data, which has stock values for timestamps in a day/week/month based on the function you use to request from API.
11. Based on the above information, I have created schema for the table TimeSeries table with following columns in the TimeSeries.xml file, available on Github




12. 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.
13. 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.
14. With the schema done, next up is configuring the 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, available on Github




15. 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.
16. 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
17. You should now see a new folder named src created with template code in it.

1. Open IntelliJ IDE and import the project folder in to your workspace. By default, all the libraries in the path-to\Stocks-Alphavantage\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.

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
   
   • IP Parameters
     
     • ServiceJVMLocation: C:\Program Files\Java\jdk1.X.X_xxx\jre\bin\server
     • ServiceJVMClassPath:
       
       
       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
     • ServiceIPLogOption: Enable Full Tracing
   • Go to DataSource Settings -> Default and configure IP Parameters as follows.
     
     • DataSourceIPSchemaPath: \path\to\Project\Schema
     • DataSourceIPType: DAMIP
     • DataSourceIPClass: com/ddtek/common/ip/RestIP
     • DataSourceIPCustomProperties: apikey=?
     • 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’

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 a class called TimeSeriesDataProcessor.java. This is the only class that you might have to edit to get a basic POC up and running.
3. In the TimeSeriesDataProcessor class, 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 TimeSeriesDataProcessor class that I have written.
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.

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

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. Configure the driver as shown below. The port number is the port that you have given when you created OpenAccess service.




4. Click on Test Connect to test the connection. If your OpenAccess service is running, you should be able to connect successfully.
5. Now go to Start Menu -> All Programs -> Progress DataDirect OpenAccess Client for ODBC xx_bit SDK and open Interactive SQL program.
6. You should now see a terminal, enter the following command to connect to the above ODBC data source.
   
   Connect Stocks
   
7. 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.
8. Here are some sample queries you should be able to run, if you have used my code from GitHub.

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.