Using Custom Objects in ZappySys Drivers

Introduction

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

To call stored procedure from client app using below SQL. Must prefix EXEC proc_name [parameter values….]

Virtual Table

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.

vt__Accounts
DataPath=http://mycompany/api/customers

vt__Leads
DataPath=http://mycompany/api/leads

vt__Contacts
DataPath=http://mycompany/api/contacts

All Virtual Tables will be listed along with Other Tables returned by your Data Source from Response.

Importing Virtual Tables in MS Access (JSON REST API Example)

Importing Virtual Tables in MS Access (JSON REST API Example)

 

Query Virtual Table - Preview Data

Query Virtual Table – Preview Data

Troubleshooting Errors

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

Posted in ODBC PowerPack.