How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source

[ragava28]

Hi All,

How do I define TIMESTAMPADD method input parameters in VDB so that , it gets pushed to source db. In my case source db is GE Proficy Historian ,and TIMESTAMPADD function is supported in Teiid & Proficy.

Translator : jdbc simple

Option I tried.

1.     Created "Source function" in Teiid Designer vdb. As per teiid documentation for TIMESTAMPADD ,I created  these input parameters in function . Chapter 4. Scalar Functions

TIMESTAMPADD(interval, count, timestamp)

Data types

interval : string (I hope this is creating issue. this is a enumerator ,not sure what data type we have to give for this. looking at teiid documentation I gave it as string. )

count : integer

timestamp :  timestamp

2. Deployed VDB and invoked function from source model (ProficyODBCSrc) directly, which gives me below error.

SELECT ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

SELECT ProficyODBCSrc.TIMESTAMPADD(SQL_TSI_HOUR, - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

Error: TEIID30068 The function 'ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', -24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))' is an unknown form.  Check that the function name and number of arguments is correct.

SQLState:  50000

ErrorCode: 30068

Can you please help in  resolving this.

[rareddy]

Raghu,

Turn on the Teiid logging in DEBUG or turn on COMMAND_LOG and take at the source command being sent to the source. You will see if Teiid engine is pushing to source or not, and see the exact query and then you figure out why the error?

Ramesh..

[ragava28]

Hi  Ramesh ,

Here is the log.  When executed the queries from Squirrel.

SELECT ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

============================================================

2016-05-01 23:28:11,155 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #13)  START USER COMMAND: startTime=2016-05-01 23:28:11.155 requestID=scqYjuXTyKrJ.3 txID=null sessionID=scqYjuXTyKrJ applicationName=JDBC principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 sql=SELECT ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

2016-05-01 23:28:11,155 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #13)  START USER COMMAND: startTime=2016-05-01 23:28:11.155 requestID=scqYjuXTyKrJ.3 txID=null sessionID=scqYjuXTyKrJ applicationName=JDBC principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 sql=SELECT ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

2016-05-01 23:28:11,155 DEBUG [org.teiid.COMMAND_LOG] (Worker167_QueryProcessorQueue47145)  ERROR USER COMMAND: endTime=2016-05-01 23:28:11.155 requestID=scqYjuXTyKrJ.3 txID=null sessionID=scqYjuXTyKrJ principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 finalRowCount=null

2016-05-01 23:28:11,155 DEBUG [org.teiid.COMMAND_LOG] (Worker167_QueryProcessorQueue47145)  ERROR USER COMMAND: endTime=2016-05-01 23:28:11.155 requestID=scqYjuXTyKrJ.3 txID=null sessionID=scqYjuXTyKrJ principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 finalRowCount=null

2016-05-01 23:28:11,155 WARN  [org.teiid.PROCESSOR] (Worker167_QueryProcessorQueue47145) TEIID30020 Processing exception for request scqYjuXTyKrJ.3 'TEIID30068 The function 'ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', -24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))' is an unknown form.  Check that the function name and number of arguments is correct.'. Originally QueryResolverException ResolverVisitor.java:599. Enable more detailed logging to see the entire stacktrace.

2016-05-01 23:28:11,155 WARN  [org.teiid.PROCESSOR] (Worker167_QueryProcessorQueue47145) TEIID30020 Processing exception for request scqYjuXTyKrJ.3 'TEIID30068 The function 'ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', -24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))' is an unknown form.  Check that the function name and number of arguments is correct.'. Originally QueryResolverException ResolverVisitor.java:599. Enable more detailed logging to see the entire stacktrace.

============================================================

SELECT ProficyODBCSrc.TIMESTAMPADD(SQL_TSI_HOUR, - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

========================================================================

2016-05-01 23:34:31,094 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #13)  START USER COMMAND: startTime=2016-05-01 23:34:31.094 requestID=scqYjuXTyKrJ.4 txID=null sessionID=scqYjuXTyKrJ applicationName=JDBC principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 sql=SELECT ProficyODBCSrc.TIMESTAMPADD(SQL_TSI_HOUR, - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

2016-05-01 23:34:31,094 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #13)  START USER COMMAND: startTime=2016-05-01 23:34:31.094 requestID=scqYjuXTyKrJ.4 txID=null sessionID=scqYjuXTyKrJ applicationName=JDBC principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 sql=SELECT ProficyODBCSrc.TIMESTAMPADD(SQL_TSI_HOUR, - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

2016-05-01 23:34:31,110 DEBUG [org.teiid.COMMAND_LOG] (Worker168_QueryProcessorQueue47146)  ERROR USER COMMAND: endTime=2016-05-01 23:34:31.11 requestID=scqYjuXTyKrJ.4 txID=null sessionID=scqYjuXTyKrJ principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 finalRowCount=null

2016-05-01 23:34:31,110 DEBUG [org.teiid.COMMAND_LOG] (Worker168_QueryProcessorQueue47146)  ERROR USER COMMAND: endTime=2016-05-01 23:34:31.11 requestID=scqYjuXTyKrJ.4 txID=null sessionID=scqYjuXTyKrJ principal=admin@dsds-security-domain vdbName=ProficySource vdbVersion=1 finalRowCount=null

2016-05-01 23:34:31,110 WARN  [org.teiid.PROCESSOR] (Worker168_QueryProcessorQueue47146) TEIID30020 Processing exception for request scqYjuXTyKrJ.4 'TEIID31119 Symbol SQL_TSI_HOUR is specified with an unknown group context'. Originally QueryResolverException ResolverVisitor.java:105. Enable more detailed logging to see the entire stacktrace.

2016-05-01 23:34:31,110 WARN  [org.teiid.PROCESSOR] (Worker168_QueryProcessorQueue47146) TEIID30020 Processing exception for request scqYjuXTyKrJ.4 'TEIID31119 Symbol SQL_TSI_HOUR is specified with an unknown group context'. Originally QueryResolverException ResolverVisitor.java:105. Enable more detailed logging to see the entire stacktrace.

========================================================================

[shawkins]

The function signature from the designer screenshot expects an integer, string, and timestamp.  Above you are calling with a string, integer, and timestamp - with the arguments reversed.  Then you are calling with a keyword for the interval, which would only be recognized for the built-in Teiid timestampadd function, and not for a UDF.

[ragava28]

Steven,

I checked order of input parameters.Order in which I am passing is correct.   i.e string, integer & timestamp. Teiid designer is showing parameters in alphabetical order  ,which we can discard.  Here is the snap of expected input parameters & their position/order from Squirrel .

Confusion which I have is with datatype of "Interval"  parameter of TIMESTAMPADD method .  when we are pushing this method from VDB to proficy. what is the data type we have to provide for this.

Below command works in VDB & Proficy. But in this first parameter(Interval) is neither string nor integer, then how do we define that in VDB, so that interval parameter is properly pushed to proficy  ?  I tried with string which is not working (above example). Any thoughts on this ?

SELECT TIMESTAMPADD(SQL_TSI_HOUR, - 24, '2012-12-12 00:00:00')

[shawkins]

> I checked order of input parameters.Order in which I am passing is correct.   i.e string, integer & timestamp. Teiid designer is showing parameters in alphabetical order  ,which we can discard. 

It should display them in positional order.  Unless you asked for alphabetic ordering, that seems like a bug - or the ordering in Designer is not the same as the source function.

> Here is the snap of expected input parameters & their position/order from Squirrel .

Is that from the source function, or from Squirrel connecting to the VDB?

> Below command works in VDB & Proficy. But in this first parameter(Interval) is neither string nor integer, then how do we define that in VDB, so that interval parameter is properly pushed to proficy  ?

It makes a difference whether you are calling the Teiid system function timestampadd or defining a source/udf timestampadd function.  For the system function the parser recognizes the use of keywords for the interval.  For other timestampadd functions the parser will see them as just regular functions and expect normal literal/expression arguments.

The engine will make a choice to push the standard system function to a source based upon the capabilities.  What is the translator you are using?  If it's custom you'll need to add the timestampadd function into the list of functions supported in the ExecutionFactory.getSupportedFunctions.

It is also fine to use an alternative source definition for timestampadd, but the calling signature will need to match the metadata seen in Designer - which may need some clarification.

[ragava28]

>  I checked order of input parameters.Order in which I am passing is correct.  i.e string, integer & timestamp. Teiid designer is showing parameters in alphabetical order  ,which we can discard.

It should display them in positional order.  Unless you asked for alphabetic ordering, that seems like a bug - or the ordering in Designer is not the same as the source function.

(Your are right . I have changed the sorting order, due to that you see that change in order.  )

> Here is the snap of expected input parameters & their position/order from Squirrel .

Is that from the source function, or from Squirrel connecting to the VDB?

Source function definition(i.e proficy function definition) is  TIMESTAMPADD(interval,count,timstamp). below is the example

• SELECT TIMESTAMPADD(SQL_TSI_HOUR, - 24, '2012-12-12 00:00:00')

Looking at below Teiid documentation, I have added TIMESTAMPADD function to VDB Proficy Source model as "Source function". My first reply has screen shots of same.

Source Supported Functions - Teiid 9.0 (draft) - Project Documentation Editor

After adding the function to VDB  Proficy Source model & I connected to VDB Proficy Source model from squirrel to test it . This screen shot is from squirrel.

> Below command works in VDB & Proficy. But in this first parameter(Interval) is neither string nor integer, then how do we define that in VDB, so that interval parameter is properly pushed to proficy  ?

>It makes a difference whether you are calling the Teiid system function timestampadd or defining a source/udf timestampadd function.  For the system function the parser recognizes the use of keywords for the interval.  For other timestampadd functions the parser will see them as just regular functions and expect normal literal/expression arguments.

I am not trying to invoke Teiid system function , I am trying to define a source timestampadd function. I hope when you say "normal literal/expression arguments"   you saying that "interval" parameter should be XMLLiteral datatype. I tried even that. but no luck. If my understating is wrong please correct me.

>The engine will make a choice to push the standard system function to a source based upon the capabilities.  What is the translator you are using?  If it's custom you'll need to add the timestampadd function into the list of functions supported in the ExecutionFactory.getSupportedFunctions.

I am using jdbc-simple. I also tried with jdbc -ansi

It is also fine to use an alternative source definition for timestampadd, but the calling signature will need to match the metadata seen in Designer - which may need some clarification.

sorry . I didn't understand this.

[shawkins]

> I am not trying to invoke Teiid system function , I am trying to define a source timestampadd function. I hope when you say "normal literal/expression arguments"   you saying that "interval" parameter should be XMLLiteral datatype. I tried even that. but no luck. If my understating is wrong please correct me.

I was just clarifying why you can call the Teiid system function.  No,  interval would not be an xmlliteral - from your screenshot it is defined as a string type, so you would just use a string literal.

> I am using jdbc-simple. I also tried with jdbc -ansi

Neither of those will allow the pushdown of the built-in Teiid function, but will allow the pushdown of a function defined on a source model as you are trying to do.

Assuming that the argument types/positions and name is correct, you would expect:

SELECT ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

To work as you are providing an appropriate string literal for the interval.  Can you provide your vdb / workspace so that we can confirm what you are seeing?

[ragava28]

Steven,

I have attached the VDB.

Ragards

Raghu

[shawkins]

The issue here is that while the metadata record is marked as a source function, it is not a valid function definition.  No return parameter is defined.  The server for legacy reasons does not treat that as an error, but ideally designer should show you a warning.

Instead the server still sees this as a source procedure, which you can call:

call ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

[ragava28]

Hi Steven,

I have now defined return result for the Source function. below is the screenshot of same,even after that I am not able to invoke the function .  Can you please help on the same.

call ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

[shawkins]

What is shown above returns a result set / cursor - a function is expected to only have a scalar return parameter.  Adding a return value parameter should be an option add as a child.

[ragava28]

Steven ,

Created function by adding return value parameter (Direction: Return).   Below is the definition of function. but still not luck.

     Invoking it from Squirrel.

          call ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

[shawkins]

> call ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

Using the call/exec keyword is currently only for stored procedures.  Now that it looks like you have the this correctly defined as a function, you should use select/values:

select ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', - 24, parseTimestamp('2012-12-12 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

[ragava28]

Steven,

I tried select. still no luck. Below are details of error.

===================

Error: TEIID30328 Unable to evaluate ProficyODBCSrc.TIMESTAMPADD('SQL_TSI_HOUR', -24, {ts'2012-12-12 00:00:00.0'}): TEIID30341 Function ProficyODBCSrc.TIMESTAMPADD is marked in the function metadata as a function that must be evaluated at the source.

SQLState:  50000

ErrorCode: 30328

===================