Do you have a custom API that you use internally in your company that you would like to connect to your favorite analytics tool? Do you have a data source which has a REST API and doesn’t have an ODBC driver, but you would like to have one for your analytics or integration purposes?
If so, you're in the right place. Progress OpenAccess SDK is a framework that enables you to build a driver for any REST data source quickly and easily, without you having to deal with low level ODBC API’s. This tutorial will get you started in building your own ODBC driver using Progress DataDirect OpenAccess SDK. We will walk you through building an ODBC driver for a free financial REST API provided by Alpha Vantage, which offers real time and historical stock market data.
dam_describeTable(long hstmt, StringBuilder pCatalog, StringBuilder pSchema, StringBuilder pTableName, StringBuilder pTablePath, StringBuilder pUserData)
long dam_getCol(long hstmt, String ColumnName)where hstmt is statement handle. You 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,StringBuilder 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.
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 is 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 we send a query like:
SELECT * FROM TIMESERIES WHERE "function"='TIME_SERIES_INTRADAY' AND symbol='PRGS' AND "interval"='5min'
If your API doesn’t have an advanced filtering option 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 it 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.
dam_addCharValToRow(dam_hstmt, hrow_handle, columnHandle, data, ip.XO_NTS)You can learn more about the function dam_addxxxValToRow here.
dam_isTargetRow(long hstmt, long hRow)which will return DAM_TRUE, if the row matches restriction.
dam_addRowToTable(long stmt_handle, long row_handle)where row_handle is the handle that you got when you created memory for the row.
We hope this tutorial helped you to build your own custom ODBC driver using Progress DataDirect OpenAccess SDK.