TIBCO Data Virtualization SQL Syntax Expansion Procedure
Article ID: KB0075412
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 7.0.0 and later |
Description
Sometimes in a TDV procedure or view, you might want to use SQL syntax that is supported by a third-party datasource vendor but is not supported by the TDV SQL script language.
Generally, you are restricted to the TDV SQL support when you submit SQL statements against a datasource. However, in cases where the TDV does support an SQL function that matches your need but is simply represented by the datasource vendor with a different keyword, such as SUBSTR versus SUBSTRING, attempts can be made to emulate the support. Review the resolution section for examples on this.
Generally, you are restricted to the TDV SQL support when you submit SQL statements against a datasource. However, in cases where the TDV does support an SQL function that matches your need but is simply represented by the datasource vendor with a different keyword, such as SUBSTR versus SUBSTRING, attempts can be made to emulate the support. Review the resolution section for examples on this.
Issue/Introduction
This document describes how to, in limited circumstances, force the TDV Structured Query Language (SQL) parser to accept and push certain SQL functions to a datasource that supports it natively.
Resolution
Expand the SQL Syntax in CIS
There are two steps that must be completed in order to expand the SQL syntax in the TDV, which are described in the next sections.
Create a New Custom Function/User Defined Function (UDF)
In order to emulate the supportability of a new keyword, you must create a new custom function in Composite that allows you to interpret the new keyword in your query during parsing. For SUBSTR, this is completed with the creation of a stored procedure in the SQL Script that takes zero or more scalar parameters as inputs, and has exactly one scalar output parameter.
After the procedure is created, navigate to the Administration > Custom Functions menu and enable the procedure as a custom function. Here is an example:
PROCEDURE SUBSTR(IN str VARCHAR, IN offset INTEGER, IN len INTEGER, OUT newstr VARCHAR)
BEGIN
SET newstr = SUBSTRING(str, offset, len);
END
Modify the Capabilities
In order to modify the capabilities, navigate to the appropriate (latest revision) datasource capabilities file and add a mapping for that source. Here is an example for the new SUBSTR function:
SUBSTR(~string,~whole_number): SUBSTR($1, $2)
SUBSTR(~string,~whole_number,~whole_number): SUBSTR($1, $2, $3)
Restart the server in order to reload the modified capabilities file. Your SQL references to SUBSTR should now be accepted.
There are two steps that must be completed in order to expand the SQL syntax in the TDV, which are described in the next sections.
Create a New Custom Function/User Defined Function (UDF)
In order to emulate the supportability of a new keyword, you must create a new custom function in Composite that allows you to interpret the new keyword in your query during parsing. For SUBSTR, this is completed with the creation of a stored procedure in the SQL Script that takes zero or more scalar parameters as inputs, and has exactly one scalar output parameter.
After the procedure is created, navigate to the Administration > Custom Functions menu and enable the procedure as a custom function. Here is an example:
PROCEDURE SUBSTR(IN str VARCHAR, IN offset INTEGER, IN len INTEGER, OUT newstr VARCHAR)
BEGIN
SET newstr = SUBSTRING(str, offset, len);
END
Modify the Capabilities
In order to modify the capabilities, navigate to the appropriate (latest revision) datasource capabilities file and add a mapping for that source. Here is an example for the new SUBSTR function:
SUBSTR(~string,~whole_number): SUBSTR($1, $2)
SUBSTR(~string,~whole_number,~whole_number): SUBSTR($1, $2, $3)
Restart the server in order to reload the modified capabilities file. Your SQL references to SUBSTR should now be accepted.
Feedback
Yes
No
Powered by
