ODBC PowerPack v1.2 release brought an interesting feature for all API drivers. New version gives you an ability to create Custom Objects. You can create parameterized Stored Procedure and Virtual Tables on the same Data Source (ODBC DSN or Data Gateway Data Source).
Custom Stored Procedure
You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.
Here is an example stored procedure for JSON Driver. You can insert Placeholders anywhere inside Proc Body. Read more about placeholders here
CREATE PROCEDURE usp_UploadData
SELECT * FROM $
(METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA'
To call stored procedure from client app using below SQL. Must prefix EXEC proc_name [parameter values….]
EXEC usp_UploadData 'Jan_data1.json' , 'HRApp'
ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you dont have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.
Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.
All Virtual Tables will be listed along with Other Tables returned by your Data Source from Response.
Use Case – MS Access – Import Tables
If you are using ZappySys ODBC Driver to integrate API in MS Access then Virtual Tables are useful because MS Access only allows to choose Tables when you import and no place to enter custom Query.
Use Case – Informatica – Import Tables (Create Source / Target Schema from ODBC DSN)
Here is another use case of informatica rest / json api integration. Informatica doesnt allow to import SQL Query structure so you must choose exposed tables. Using Virtual Tables feature you can import multiple tables from Same ODBC DSN
Once virtual tables are defined you can go to preview tab and it will show entries there regardless your Data Source settings on main UI.
While running in Access\Excel\other and reading data from DSN created with ODBC PowerPack, if you get this error "License type [ODBC_PP_TRIAL] not found or its expired"
Please refer to this article for the same: Troubleshooting "License type [ODBC_PP_TRIAL] not found or its expired" error in Microsoft Access