Using Custom Objects in ZappySys Drivers (Proc / View)

Contents hide

1 Introduction

2 Custom Objects

2.1 Custom Stored Procedure

2.1.1 Stored Procedure with Parameters

2.1.2 Stored Procedure without Parameters

2.1.3 Using Functions in Stored Procedure

2.2 Virtual Table – Connection String Mode

2.3 Virtual Table – SQL Mode (v1.6 or higher)

3 Virtual Table Use Cases

3.1 MS Access – Import Tables

3.2 Informatica – Import Tables (Create Source / Target Schema from ODBC DSN)

3.3 Preview Data

4 Troubleshooting Errors

4.1 More from ZappySys Blog

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). For more information you see here

Custom Objects

There are mainly 3 types of custom objects you can create using ZappySys Drivers.

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.

ZappySys ODBC Driver – Custom Objects – New Stored Procure

Stored Procedure with Parameters

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
 @file='test.json',
 @source='HRApp'
AS
SELECT * FROM $
WITH 
(METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA'
,SRC='http://httpbin.org/post?src=<@source>&timestamp=<<FUN_TODAY>>'
,BODY='@c:\files\<@file,FUN_TRIM>'
,IsMultiPart='True'
)

CREATE PROCEDURE usp_UploadData

@file='test.json',

@source='HRApp'

SELECT * FROM $

WITH

(METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA'

,SRC='http://httpbin.org/post?src=<@source>&timestamp=<<FUN_TODAY>>'

,BODY='@c:\files\<@file,FUN_TRIM>'

,IsMultiPart='True'

)

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

EXEC usp_UploadData 'Jan_data1.json' , 'HRApp'

1	EXEC usp_UploadData 'Jan_data1.json' , 'HRApp'

Stored Procedure without Parameters

Using Functions in Stored Procedure

ZappySys provides many functions which can be used as placeholder anywhere inside Stored Proc code or in your SQL query for Driver

Example of simple function (No parameter)

CREATE PROCEDURE usp_OrdersByCountry
 @country='INDIA' --always supply some default parameter so metadata can be guessed
AS
SELECT * FROM Orders
WHERE Country='<@country,FUN_TRIM>')

CREATE PROCEDURE usp_OrdersByCountry

@country='INDIA' --always supply some default parameter so metadata can be guessed

SELECT * FROM Orders

WHERE Country='<@country,FUN_TRIM>')

Example of function with parameters

In below example we have invoked function after we replaced parameter. Our function FUN_TO_DATE has 2 parameters (actual date value and format we want to output). This way we can control replacement order

CREATE PROCEDURE usp_OrdersByDate
 @orderdate='2012-01-01'  --always supply some default parameter so metadata can be guessed
AS
SELECT * FROM Orders
WHERE OrderDate=DATETIME('<<<@orderdate>||yyyy-MM-dd,FUN_TO_DATE>>')

CREATE PROCEDURE usp_OrdersByDate

@orderdate='2012-01-01' --always supply some default parameter so metadata can be guessed

SELECT * FROM Orders

WHERE OrderDate=DATETIME('<<<@orderdate>||yyyy-MM-dd,FUN_TO_DATE>>')

Virtual Table – Connection String Mode

ZappySys 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.

<strong>vt__Accounts</strong>
DataPath=http://mycompany/api/customers

<strong>vt__Leads</strong>
DataPath=http://mycompany/api/leads

<strong>vt__Contacts</strong>
DataPath=http://mycompany/api/contacts

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.

Virtual Table – SQL Mode (v1.6 or higher)

Version 1.6+ introduced new mode to create Virtual Table. Now you can write custom SQL just like a View in Relational DB.

See below example

Virtual Table Use Cases

Now lets look at a few real world use cases where Virtual Tables might be useful. You can use when your Client Application doesn’t have support to connect to ODBC in SQL Query mode and only allows Table selection / import.

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.

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

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

Select JSON Source Table in Informatica Mapping Designer (JSON file or REST API)

Preview Data

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.

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