{"id":8895,"date":"2020-05-12T23:54:41","date_gmt":"2020-05-12T23:54:41","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8895"},"modified":"2022-09-08T16:43:07","modified_gmt":"2022-09-08T16:43:07","slug":"odbc-drivers-custom-objects-feature","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/","title":{"rendered":"Using Custom Objects in ZappySys Drivers (Proc \/ View)"},"content":{"rendered":"

Introduction<\/h2>\n

ODBC PowerPack v1.2<\/a> 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<\/a><\/p>\n

Custom Objects<\/h2>\n

There are mainly 3 types of custom objects you can create using ZappySys Drivers.<\/p>\n

Custom Stored Procedure<\/h3>\n

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.<\/p>\n

\"ZappySys

ZappySys ODBC Driver – Custom Objects – New Stored Procure<\/p><\/div>\n

Stored Procedure with Parameters<\/strong><\/h4>\n

Here is an example stored procedure for JSON Driver. You can insert Placeholders anywhere inside Proc Body. Read more about placeholders here<\/a><\/p>\n

CREATE PROCEDURE usp_UploadData\r\n @file='test.json',\r\n @source='HRApp'\r\nAS\r\nSELECT * FROM $\r\nWITH \r\n(METHOD='POST', HEADER='Content-Type: text\/plain || x-hdr1: AAA'\r\n,SRC='http:\/\/httpbin.org\/post?src=<@source>&timestamp=<<FUN_TODAY>>'\r\n,BODY='@c:\\files\\<@file,FUN_TRIM>'\r\n,IsMultiPart='True'\r\n)<\/pre>\n
To call stored procedure from client app using below SQL. Must prefix EXEC proc_name [parameter values….]\n
EXEC usp_UploadData 'Jan_data1.json' , 'HRApp'<\/pre>\n
Stored Procedure without Parameters<\/strong><\/h4>\n
\"ZappySys<\/p>\n
Using Functions in Stored Procedure<\/strong><\/h4>\n
ZappySys provides many functions<\/a> which can be used as placeholder anywhere inside Stored Proc code or in your SQL query for Driver<\/p>\n
Example of simple function (No parameter)<\/strong><\/p>\n
CREATE PROCEDURE usp_OrdersByCountry\r\n @country='INDIA' --always supply some default parameter so metadata can be guessed\r\nAS\r\nSELECT * FROM Orders\r\nWHERE Country='<@country,FUN_TRIM>')<\/pre>\n
Example of function with parameters<\/strong><\/p>\n
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<\/p>\n
CREATE PROCEDURE usp_OrdersByDate\r\n @orderdate='2012-01-01'  --always supply some default parameter so metadata can be guessed\r\nAS\r\nSELECT * FROM Orders\r\nWHERE OrderDate=DATETIME('<<<@orderdate>||yyyy-MM-dd,FUN_TO_DATE>>')<\/pre>\n
 <\/p>\n
Virtual Table – Connection String Mode<\/h3>\n
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.<\/p>\n
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.<\/p>\n
vt__Accounts<\/strong>\r\nDataPath=http:\/\/mycompany\/api\/customers\r\n\r\nvt__Leads<\/strong>\r\nDataPath=http:\/\/mycompany\/api\/leads\r\n\r\nvt__Contacts<\/strong>\r\nDataPath=http:\/\/mycompany\/api\/contacts<\/pre>\n
All Virtual Tables will be listed along with Other Tables returned by your Data Source from Response.<\/p>\n
 <\/p>\n
\"ZappySys<\/p>\n
 <\/p>\n
Virtual Table – SQL Mode (v1.6 or higher)<\/h3>\n
Version 1.6+ introduced new mode to create Virtual Table. Now you can write custom SQL just like a View in Relational DB.<\/p>\n
See below example<\/p>\n
\"ZappySys<\/p>\n
Virtual Table Use Cases<\/h2>\n
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.<\/p>\n
MS Access – Import Tables<\/h3>\n
If you are using ZappySys ODBC Driver to integrate API in MS Access<\/a> then Virtual Tables are useful because MS Access only allows to choose Tables when you import and no place to enter custom Query.<\/p>\n
\"Importing<\/a>
Importing Virtual Tables in MS Access (JSON REST API Example)<\/p><\/div>\n
Informatica – Import Tables (Create Source \/ Target Schema from ODBC DSN)<\/h3>\n
Here is another use case of informatica rest \/ json api<\/a> 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<\/p>\n
\"Select<\/a>
Select JSON Source Table in Informatica Mapping Designer (JSON file or REST API)<\/p><\/div>\n
Preview Data<\/h3>\n
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.<\/p>\n
\"Query<\/a>
Query Virtual Table – Preview Data<\/p><\/div>\n
Troubleshooting Errors<\/h2>\r\n
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<\/strong>\"<\/p>\r\n\r\n
Please refer to this article for the same:\u00a0\u00a0Troubleshooting \"License type [ODBC_PP_TRIAL] not found or its expired\" error in Microsoft Access<\/a><\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":8902,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[276],"tags":[524,75,397],"yoast_head":"\r\nUsing Custom Objects in ZappySys Drivers (Proc \/ View) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Using Custom Objects in ZappySys Drivers (Proc \/ View) - ZappySys Blog\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Using Custom Objects in ZappySys Drivers (Proc \/ View) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Using Custom Objects in ZappySys Drivers (Proc \/ View) - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2020-05-12T23:54:41+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2022-09-08T16:43:07+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"837\" \/>\r\n\t<meta property=\"og:image:height\" content=\"720\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/\",\"url\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/\",\"name\":\"Using Custom Objects in ZappySys Drivers (Proc \/ View) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png\",\"datePublished\":\"2020-05-12T23:54:41+00:00\",\"dateModified\":\"2022-09-08T16:43:07+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Using Custom Objects in ZappySys Drivers (Proc \/ View) - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png\",\"width\":837,\"height\":720,\"caption\":\"Importing Virtual Tables in MS Access (JSON REST API Example)\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using Custom Objects in ZappySys Drivers (Proc \/ View)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using Custom Objects in ZappySys Drivers (Proc \/ View) | ZappySys Blog","description":"Using Custom Objects in ZappySys Drivers (Proc \/ View) - ZappySys Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/","og_locale":"en_US","og_type":"article","og_title":"Using Custom Objects in ZappySys Drivers (Proc \/ View) | ZappySys Blog","og_description":"Using Custom Objects in ZappySys Drivers (Proc \/ View) - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2020-05-12T23:54:41+00:00","article_modified_time":"2022-09-08T16:43:07+00:00","og_image":[{"width":837,"height":720,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/","url":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/","name":"Using Custom Objects in ZappySys Drivers (Proc \/ View) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png","datePublished":"2020-05-12T23:54:41+00:00","dateModified":"2022-09-08T16:43:07+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Using Custom Objects in ZappySys Drivers (Proc \/ View) - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/zappysys-driver-virtual-table-import-msaccess.png","width":837,"height":720,"caption":"Importing Virtual Tables in MS Access (JSON REST API Example)"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/odbc-drivers-custom-objects-feature\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Using Custom Objects in ZappySys Drivers (Proc \/ View)"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8895"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=8895"}],"version-history":[{"count":13,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8895\/revisions"}],"predecessor-version":[{"id":9770,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8895\/revisions\/9770"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/8902"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=8895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=8895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=8895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}