How to pushdown a function to underlying data source when using CData adapter?

How to pushdown a function to underlying data source when using CData adapter?

book

Article ID: KB0073620

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions

Description

This article outlines the process that needs to be followed to push down any function to the underlying data source. 

Issue/Introduction

How to pushdown a function to underlying data source when using CData adapter?

Resolution

For demonstration purposes, we are using a MySQL CData adapter to connect to Singlestore DB and push down SECURITY_LISTS_INTERSECT() function. 
Note: SECURITY_LISTS_INTERSECT() is a built-in Singlestore DB function.

Step 1: Use CData MySQL adapter. (Note: If this adapter is not available in your TDV server, navigate to https://www.cdata.com/solutions/dv/tibco/#download to download the adapter and follow the instructions in the README file to deploy it in TDV)

Step 2: Configure "QueryPassthrough" in data source configuration. (Open the data source => In the 'Other' property box, add "QueryPassThrough=true;" without the quotes)

Step 3: Add the required markup under the "function_support" => "members" section of [TDV_Install_Dir]/packages/MySQL_1/conf/MySQL_properties.yaml 
Sample markup:
---------------------------------------
    security_lists_intersect:
      display_name: SECURITY_LISTS_INTERSECT
      native_expression: SECURITY_LISTS_INTERSECT($1, $2)
      arguments:
        combination1:
          arguments_in_order:
           - cis.any
           - cis.any
---------------------------------------
User-added image

Step 4: Create a custom function in TDV for us to use in queries in TDV. 

TDV Custom function:
---------------------------------------
PROCEDURE SECURITY_LISTS_INTERSECT(IN in1 VARCHAR(4000), IN in2 VARCHAR(4000), OUT out1 Boolean)
    BEGIN
    END
---------------------------------------
Navigate to Studio -> Administration -> Custom function to add the above function as a custom function.
User-added image