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 JDBC?
Do you have a data source which has a REST API and doesn’t have a JDBC driver, but you would like to have one for your analytics or integration purposes?
Then you are at the right place. If you want to get started quickly and have a feel of how Progress DataDirect OpenAccess SDK can help you in building a ODBC/JDBC driver, I recommend you to follow our beginner tutorials.
- Build your own custom ODBC driver for REST API in 2 hours
- Build your own custom JDBC driver for REST API in 2 Hours
The above tutorials use OpenAccess REST IP template generator to make it easy for you guys to build a most basic JDBC driver.
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:
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.
- To test the driver, I will use a free SQL query tool called SQL Workbench.Add the OpenAccess JDBC driver that you have installed earlier as shown below.
- Configure the OpenAccess driver as shown below and click on OK.
- 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.
- 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 JDBC 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.