OpenAccess ODBC Pass Through Mode

Update: For a code-less solution, look at our 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?

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 the driver that operates in SQL Pass Thru 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 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

Please note that this API supports SQL query like constructs and I will be using that to push down most of the SQL query and will not be relying on using OpenAccess post processing of data.

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

         

         

     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\Progress\DataDirect\oaserver80\ip\schema\template_dynamic

     2. DataSourceIPType: DAMIP
     3. DataSourceIPClass: oajava/<name_from_step_9>/OpenAccessIP
     4. 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. 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 OpenAccessPassThruIP.java
3. Open the OpenAccessPassThruIP.java and rename the class name to OpenAccessPassThruIP.
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.nycpassthru, for your reference.
5. Create a new folder structure for the package you have created above and move the OpenAccessPassThruIP.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. OpenAccess IP provides you with an interface of 19 functions abstracting a lot complexities needed to build a driver. If you open the OpenAccessPassThruIP.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 OpenAccessPassThruIP to get an idea on this.
   • To enable SQL Pass through mode, in the IP Support array set the property IP_SUPPORT_PASSTHROUGH_QUERY to 1
6. ipSchema Method

    

   • The OpenAccess SDK SQL engine calls this method when it needs to get any of the following information about the schema.
     1. List of Tables
     2. Columns for a Table
     3. Details about each column
     4. Foreign Keys, Primary Keys
     5. Indexes
     6. 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. IP Execute Method
   • This method is where you get the SQL Parse Tree from OpenAccess, parse the tree, 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.

      

   • You should use the SQL Engine Parse tree methods provided by OpenAccess SDK to retrieve more information about the query than you get from a Row based mode. Go to Github and follow my code to get an idea on how to parse through the SQL Tree as it can be complex.
   • Once you have done parsing through the SQL tree, 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. As your API supports filter conditions and aggregation, you need to push it down to your API as OpenAccess SQL Engine does minimal to no Post processing of data in this mode. Your API will be responsible for most of the post processing. Say you have a query like this

      

     SELECT VEHICLE_YEAR , COUNT(VEHICLE_YEAR) FROM NYCOPENDATA GROUP BY VEHICLE_YEAR

      

     The IP code should translate this query to NYC Open Data API as

     https://data.cityofnewyork.us/resource/ati4-9cgt.json?$select=vehicle_year,count(vehicle_year)&$group=vehicle_year

      

      

   • 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 damex_addxxxResValToRow or damex_addxxxColValToRow, 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 or resultant column number. Say if I am trying to add a VARCHAR column to row, here is the code that I would use
   • 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

 

 

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.