Query your REST API using a Custom ODBC Driver from SQL Server Linked Server

Do you have a Custom REST API that you want to query from SQL Server? Do you want to join the data from REST API with Native SQL Server tables?  With the help of Progress DataDirect OpenAccess SDK, you can create a custom ODBC driver for your REST API, and use it with Linked Server in SQL Server. This tutorial will help you get started with creating your own Custom ODBC Driver for your REST API.

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 1.8 from Oracle website.

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 Stocks-Alphavantage.
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. Move the contents of RestGenerator folder from the extracted files to the new folder that you have created in step-6. Below is my folder structure for your reference.



6. 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
7. First we will create schema file for the Alpha Vantage REST endpoint and then proceed to configuring input.props
8. 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 am gonna expose through ODBC and the file holds the schema information for the Alpha Vantage endpoint.
9. 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

    

10. 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 it.
11. 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.
12. Based on the above information, I have created schema for the table TimeSeries table with following columns in the TimeSeries.xml file.


<?xml version="1.0" encoding="UTF-8"?>
<tables>
<table name="TimeSeries">
 
<column name="Information" dataType="12" userData=""/>
<column name="Function" dataType="12" userData=""/>
<column name="Symbol" dataType="12" userData=""/>
<column name="LastRefreshed" dataType="12" userData=""/>
<column name="Interval" dataType="12" userData=""/>
<column name="OutputSize"  dataType="12" userData=""/>
<column name="TimeZone"  dataType="12" userData=""/>
<column name="Timestamp_Recorded" dataType="12"  userData=""/>
<column name="open" dataType="8" userData=""/>
<column name="high" dataType="8" userData=""/>
<column name="low" dataType="8" userData=""/>
<column name="close" dataType="8" userData=""/>
<column name="volume" dataType="2" userData=""/>
 
 
<stat nonUnique="0" indexName="IDXTMESTMPREC" indexType="1" seqInIndex="1" columnName="Timestamp_Recorded" cardinality="-1" pages="-1" filterConditions="" />
 
<pkfk pkColumnName="Timestamp_Recorded" keySeq="1" updateRule="-1" deleteRule="-1" pkName="PKTMESTMPREC" />
 
</table>
</tables>




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

     

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

     

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




DATABASE_NAME=Stocks

CATALOG_NAME=AlphaVantage

SCHEMA_NAME=Stocks

DATABASE_VERSION=1.0

SCHEMA_LOCATION=schema

SCHEMA_TYPE=XML

PATHTOWRITE=src

BASEPACKAGE=com.ddtek

DATASOURCE=Stocks

BASE_URL=https://www.alphavantage.co/query




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

     

18. 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
     1. ServiceJVMLocation: C:\Program Files\Java\jdk1.X.X_xxx\jre\bin\server
     2. 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 2^nd line in the path with \path\to\Project\bin

         

         

     3. ServiceIPModule: oadamipjava.dll

    

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

    

5. Go to DataSource Settings -> Default and configure it as follows.
   • IP Parameters
     1. DataSourceIPSchemaPath: \path\to\Project\Schema
     2. DataSourceIPType: DAMIP
     3. DataSourceIPClass: com/ddtek/common/ip/RestIP
     4. DataSourceIPCustomProperties: apikey=?
     5. 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>’

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 and go to the tab System DSN.
2. In System DSN, 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.

1. In your SQL Server Management Studio, connect to the database server where you would want to create the Linked Server.
2. Under Server Objects -> Linked Servers -> Right Click and create a new Linked Server.
3. Provide a name for the Linked Server, Change the Provider to “Microsoft OLEDB Provider for ODBC Drivers” and enter the value for DataSource which should be same as ODBC System DSN you have created above.




4. Click on OK to create the Linked Server

1. With Linked Server in place for the REST API, you can query your REST API using OPENQUERY as shown below. Also, you can join the data from REST API through Linked Server with the native SQL Server tables as shown below

SELECT * FROM OPENQUERY(OPENACESSSTOCKS, 'select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES where "function"=''TIME_SERIES_MONTHLY'' AND "SYMBOl"=''PRGS''')
SELECT * FROM OPENQUERY(OPENACESSSTOCKS, 'select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES where "function"=''TIME_SERIES_DAILY'' AND "SYMBOl"=''PRGS'' AND "interval"=''5min''')
 
SELECT * FROM OPENQUERY(OPENACESSSTOCKS, 'select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES where "function"=''TIME_SERIES_INTRADAY'' AND "SYMBOl"=''PRGS'' AND "interval"=''5min''')
2. Also, you can join the data from REST API through Linked Server with the native SQL Server tables as shown below


SELECT *  FROM OPENQUERY(OPENACESSSTOCKS, 'select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES where "function"=''TIME_SERIES_MONTHLY'' AND "SYMBOl"=''PRGS''') API
INNER JOIN Chinook2.dbo.Company C ON
API.SYMBOL = C.TICKER



3. Else, you can create a stored procedure for your REST API and access the data by executing the Stored Procedure. Here is one sample Stored Procedure I have created for the AlphaVantage API

    

   USE [Chinook2]

   GO

    

   /****** Object:  StoredProcedure [dbo].[sp_stocksapi]    Script Date: 3/29/2018 10:37:48 AM ******/

   SET ANSI_NULLS ON

   GO

    

   SET QUOTED_IDENTIFIER ON

   GO

    

   CREATE PROCEDURE [dbo].[sp_stocksapi]

   (

       @pfunction varchar(100),

       @psymbol varchar(5),

       @pinterval varchar(10))

    

   AS

   BEGIN

       DECLARE @TSQL varchar(8000)

       IF @pinterval is null

       BEGIN

           SELECT @TSQL='SELECT * FROM OPENQUERY(OPENACESSSTOCKS,''select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES WHERE "function" = ''''' + @pfunction + ''''' and symbol = '''''+ @psymbol + ''''''')'

       END

    

       IF @pinterval is not null

       BEGIN

           SELECT @TSQL='SELECT * FROM OPENQUERY(OPENACESSSTOCKS,''select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES WHERE "function" = ''''' + @pfunction + ''''' and symbol = '''''+ @psymbol + ''''' and "interval"=''''' +@pinterval+ ''''''')'

       END

    

    

       EXEC(@TSQL);

    

   END

   GO

    

4. You can access this Stored procedure from my Github gist. Now you can query the data as shown below using the stored procedure.

 

EXEC sp_stocksapi 'TIME_SERIES_DAILY', 'PRGS', '5min'

EXEC sp_stocksapi 'TIME_SERIES_INTRADAY', 'PRGS', '1min'

EXEC sp_stocksapi 'TIME_SERIES_MONTHLY', 'PRGS', null

EXEC sp_stocksapi 'TIME_SERIES_WEEKLY', 'PRGS', null

 

We hope this tutorial helped you to real-time join your Native SQL Server table with the data from REST API through a Linked Server using the custom ODBC driver built using Progress DataDirect OpenAccess SDK for your REST API. If you have any questions/issues, feel free to reach out to us, we will be happy to help you during your evaluation.