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
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:
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.
a. If you use eclipse, replace the 2nd 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.
If your API or data source needs Username and password based authentication, you need to change the DataSourceLogonMethod to DBMSLogon(UID, PWD)
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.
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.
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.
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
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)
SELECT * FROM NYCOPENDATA WHERE VEHICLE_YEAR>2014
The IP code translates this query to NYC Open Data API as
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.
long dam_allocRow(long hstmt)
which returns a handle that you need to use it in the next steps.
jdam.dam_addCharValToRow(dam_hstmt, hrow_handle, columnHandle, data, ip.XO_NTS);
You can learn more about the function dam_addxxxValToRow here.
int dam_isTargetRow(long hstmt, long hRow)
which will return DAM_TRUE, if the row matches restriction.
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.
-Xrunjdwp:transport=dt_socket,address=9015,server=y,suspend=n -Xdebug -Xrs
SELECT * FROM NYCOPENDATA
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.