Build your own custom ODBC Driver for a custom REST API - Advanced

Update: For a code-less solution, look at our new Autonomous REST Connector, which is designed to connect to any REST API easily. You can get started by following this tutorial.

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 are at the right place.  

The OpenAccess SDK allows you to take full advantage of the data processing capabilities and it does that by allowing you to work in two modes of operation

• Row-based mode
• SQL pass-through mode

In the Row-based mode, the OpenAccess SDK SQL engine performs all the parsing, planning, joins, union, nested query, and other SQL operations. The IP is responsible for handling row-based operations — read rows for a specific table or update rows into a specific table. Row-based mode is preferred when your REST API has some sort of basic operations of filtering or if it doesn’t have any at all. OpenAccess SDK SQL engine will take care of some or all JOINS by supporting join pushdown and taking care of grouping.

In SQL pass-through mode, the OpenAccess SDK SQL engine handles the parsing and validation of the SQL statement against the exposed schema, and makes the SQL statement available to the IP through the OpenAccess SDK SQL engine API. The IP must perform the operation that is requested by the SQL query using the mechanism that is supported by the data source. SQL pass-through mode is preferred when the backend already supports SQL or SQL-like language that can handle joins, unions, nested queries, and other SQL operations.

In this tutorial, we will be using the OpenAccess Native IP to build a driver that operates in Row-based mode and would be implementing most of the Code for the IP instead of relying on a template generator. This opens customizing the IP code to your need so that you can implement custom Authentication schemes with your API, handle dynamic schema’s, push down filters from your driver to REST API (if your API supports filters) etc., 

I will be demonstrating on how you can build a ODBC driver for NYC Parking Violations SODA API. To be more precise, the API endpoint URL I will be using in this tutorial is:

https://data.cityofnewyork.us/resource/ati4-9cgt.json

Although this API supports SQL like language, I will be treating it to have most basic operations of $where to illustrate ROW-based mode in this tutorial and let OpenAccess take care of all the post processing.

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

1. Create a new folder for your Project in your workspace.
2. Go to \Path\to\Progress\DataDirect\oaserver80\ip\oajava\template and copy the damip.java file to your new folder created above and rename it to OpenAccessIP.java
3. Open the OpenAccessIP.java and rename the class name to OpenAccessIP.
4. By default the package name for the above template file would be oajava.template. Replace the template in package name with your own custom name. I named my package oajava.nycdata, for your reference.
5. Create a new folder structure for the package you have created above and move the OpenAccessIP.java from your Project root to appropriate folder under your package. Here is mine for your reference
   
   
6. Import the Project in to your favorite Java IDE.
7. Add the OpenAccess library (C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\oasql.jar) to project build path, and build the Project. You should be able to build it successfully without any errors.

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 an 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:

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

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

       \path\to\Project\lib\ json-20170516.jar;

       \Add-Any-External-Libraries-to-this-Path-that-you-have-to-use-now-or-when-you-need-it-in-future\external.jar;

        

       Note:

       a. If you use eclipse, replace the 2^nd line in the path with \path\to\Project\bin

       b. Note that I am using JSON In Java library for parsing the JSON responses, so I added the path library which is in my Project Lib folder to the service classpath. You must add all your external libraries that you intend to use to this path.

     • 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
     • DataSourceIPSchemaPath: \path\to\Progress\DataDirect\oaserver80\ip\schema\template_dynamic
     • DataSourceIPType: DAMIP
     • DataSourceIPClass: oajava/<name_from_step_9>/OpenAccessIP
     • DataSourceIPCustomProperties: baseurl=?

        

   • Optional: User Security:

     If your API or data source needs Username and password based authentication, you need to change the DataSourceLogonMethod to DBMSLogon(UID, PWD)

      

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 baseURL needed by you for accessing the NYC SODA API in the code from a Java Map object.
7. Right click on the service and click on ‘Start <ServiceName>’

1. OpenAccess IP provides you with an interface of 19 functions abstracting a lot complexities needed to build a driver. If you open the OpenAccessIP.java file, you should see these functions. Don’t let the sheer number of functions intimidate you, as you need only 4 of these functions to run the POC ODBC driver successfully.

    

2. To get started with a basic POC driver, the functions that you are required to implement are ipGetSupport, ipConnect, ipSchema and ipExecute

    

3. Please note that I have pushed my code for this project to GitHub, feel free to reference it for the rest of this section.
4. ipConnect Method
   • As soon as you start connecting to the driver, the ipConnect method is called. In this method, you get to connect to data source you are connecting, in this case as we are dealing with REST API and check if the connection is successful.
   • You will be provided username, password and any custom properties that were set when configuring the driver, which you can use it while checking the connection to data source.
   • If you can connect to the data source successfully using the Authentication details provided, you will just return a success code.
5. ipGetSupport Method
   • This method would be called once your connection is determined to see what are the features supported by the driver like SELECT, INSERT, UPDATE, DELETE, SCHEMA Functions, Operators, Joins etc.,
   • Refer to array ip_support_array from my Github repository in the class OpenAccessIP to get an idea on this.
   • For this tutorial, I chose to support SELECT, Schema functions and operators “=” and “>”
6. ipSchema Method
   • The OpenAccess SDK SQL engine calls this method when it needs to get any of the following information about the schema.
     • List of Tables
     • Columns for a Table
     • Details about each column
     • Foreign Keys, Primary Keys
     • Indexes
     • Other Schema objects
   • You will be able to know what type of schema information is being requested for by checking the input argument iType. To know more about the types of information, check out this documentation page.
   • For a basic driver that is implementing dynamic schema, when there’s request for type DAMOBJ_TYPE_TABLE, you would have to provide the tables information. It can be all the tables or a single table and this can be checked using pSearchObj input argument of ipSchema table. If pSearchObj is null, you return all the tables info that you want to expose from your REST API and if it’s not null, you return the specific table info requested in pSearchObj.
   • If the request is for DAMOBJ_TYPE_COLUMN, then you would have to provide column information for the tables. Again, here you must check for pSearchObj to get to know the table that is being requested for and provide the column information for that table. If pSearchObj is null, then you would have to provide all the columns for all the tables.
   • You add the schema information to OpenAccess using the method dam_add_schemaobj for all the schema types using their respective Objects. Learn more about this function from this page in documentation.
   • Refer to the ipSchema implementation of sample that I have provided in GitHub to get an overview. In the sample, since I am dealing with only one table, I am getting metadata for that table and just providing to OpenAccess as discussed above.
7. ipExecute Method
   • This method is where you get the SQL query information, prepare the REST URL based on the filters, aggregation in the query, send the GET request to the REST server and then process the data and send it to OpenAccess. Once you have done that, you will be able to see the result set for the query you issued.
   • This method has two important input parameters called dam_hstmt and iStmtType where dam_hstmt is handle to statement being executed and iStmtType lets you know which type of statement is being executed like SELECT, UPDATE, INSERT, DELETE etc. To know more about all the statement types, you can visit this documentation page.
   • In this tutorial, we will be focusing on SELECT statement. First, you should check for which type of statement you are dealing with and if it’s SELECT, you proceed ahead to check the table on which SELECT is being requested. You can check that using the function

     dam_describeTable(long hstmt, StringBuffer pCatalog, StringBuffer pSchema, StringBuffer pTableName, StringBuffer pTablePath, StringBuffer pUserData).

     You pass empty StringBuffer objects as input arguments and you should have the table name and schema name once the function gets executed.

   • Once you have the table name that you are dealing with, you would need to start checking the query for various clauses like WHERE, TOP etc., and column handles for each column in the table.
   • To get Column handles for each column in table, you need to use the function

     long dam_getCol(long hstmt, String ColumnName)

     where hstmt is statement handle. You would have to get column handles for all the columns in the table by iterating through them. These column handles will be referenced later in the code when you are adding data to the resultsets.

   • To get to know about TOP clause in the query, all you need to do is call the method

     int dam_getInfo(long hdbc, long hstmt,int iInfoType,StringBuffer pStrInfoValue, xo_int pIntInfoValue)

     The argument iInfoType must be set to DAM_INFO_QUERY_TOP_ROWS, which will get you the TOP ‘N’ value. The iInfoType can take many arguments and depending on the type of information being requested, it will get you that information. Visit this table to learn more about the Info types that can be used with dam_getInfo function.

   • Next up you can check the WHERE clause. To get set of condition lists, you need to use the function

     long dam_getSetOfConditionListsEx(DAM_HSTMT hstmt, int iType, DAM_HCOL  hcol, int * pbPartialLists)

     which fetches the expression from WHERE clause on one or more columns in the form of AND/OR expressions.

     The type of condition list requested:

     SQL_SET_CONDLIST_INTERSECT - transform the where clause into a set of AND conditions and return these as a list. This is valid only if IP_SUPPORT_UNION_CONDLIST is set to 0.

     SQL_SET_CONDLIST_UNION - transform the where clause into a set of OR conditions and return these as a list. This is valid only if IP_SUPPORT_UNION_CONDLIST is set to 1.

     Learn more about this function by visiting this documentation page

   • Once you get the Set of Conditions lists, you need to iterate through them to fetch individual conditions and separate the conditions based on the operator. This requires you to use following functions and you can refer to the code in GitHub on how to use these functions properly.

     int64 dam_getFirstCondList(int64 hset_of_condlist)

     int64 dam_getNextCondList(int64 hset_of_condlist)

     long dam_getFirstCond(long hstmt, long hlist)

     long dam_getNextCond(long hstmt, long hlist)

   • Next, you need to build a request URL for your REST API based on all the above factors and send the request to get the data. If your API supports filter conditions or any aggregation, it’s always a good idea to push them down from the driver to REST API for better performance. Say, for this NYC Parking violations example, when I send a query like

     SELECT * FROM NYCOPENDATA WHERE VEHICLE_YEAR>2014

     The IP code translates this query to NYC Open Data API as

     https://data.cityofnewyork.us/resource/ati4-9cgt.json?$where=vehicle_year>”2014”

     In case your API doesn’t have any advanced filtering option, don’t worry, OpenAccess will take care of sending the correct result set from the full data that you add to OpenAccess.

   • After you get the response, all you need to do is parse the response and start adding data to the result set. To add the data to result set, first you need to allocate memory for a row by calling the function

     long dam_allocRow(long hstmt)

     which returns a handle that you need to use it in the next steps.

   • Once you have the memory allocated, all you got to do is iterate through your result set and add one column after another to the row that you have created in the previous step, based on the datatype of column. To do that you must use the function dam_addxxxValToRow, where xxx is datatype of the column that you are trying to add. You need to use the row handle that you got in the previous step and the column handle of the column that you got in step e. Say if I am trying to add a VARCHAR column to row, here is the code that I would use

     jdam.dam_addCharValToRow(dam_hstmt, hrow_handle, columnHandle, data, ip.XO_NTS);

      

     You can learn more about the function dam_addxxxValToRow here.

   • After you have added all the columns to the row, it’s time to finally add the row to result set. Before adding to result set, you can check if the row that you are adding satisfies the SQL query conditions by using the function

     int dam_isTargetRow(long hstmt, long hRow)

      

     which will return DAM_TRUE, if the row matches restriction.

   • Finally, you can now add the row to table using the function

     int dam_addRowToTable(long stmt_handle, long row_handle)

     where row_handle is the handle that you got when you created memory for the row.

   • Parse through all your data and add them to the result set to be able to see the result when the query is executed. Congratulations, you have developed a basic ODBC driver for your custom REST API.

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.

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. Notice that I have provided the REST endpoint URL in Custom properties, which will be needed by your code to connect to the service and fetch data.
5. Click on Test Connect to test the connection. If your OpenAccess service is running, you should be able to connect successfully.
6. Now go to Start Menu -> All Programs -> Progress DataDirect OpenAccess Client for ODBC xx_bit SDK and open Interactive SQL program.
7. You should now see a terminal, enter the following command to connect to the above ODBC data source.

   Connect nycdata

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

    

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

 

SELECT * FROM NYCOPENDATA

SELECT * FROM NYCOPENDATE WHERE VEHICLE_YEAR=2014

SELECT * FROM NYCOPENDATE WHERE VEHICLE_YEAR>2014

SELECT registration_state, count(registration_state) FROM NYCOPENDATA GROUP BY registration_state

SELECT registration_state, count(registration_state) FROM NYCOPENDATA GROUP BY registration_state ORDER BY registration_state desc

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.