-
1. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
rareddy May 1, 2016 3:27 AM (in response to ragava28)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..
-
2. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
ragava28 May 2, 2016 12:39 AM (in response to rareddy)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.
========================================================================
-
3. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
shawkins May 2, 2016 3:48 AM (in response to ragava28)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.
-
4. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
ragava28 May 2, 2016 6:18 PM (in response to shawkins)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')
-
5. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
shawkins May 3, 2016 3:15 AM (in response to 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.
> 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.
-
6. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
ragava28 May 3, 2016 12:43 PM (in response to 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.
(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.
-
7. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
shawkins May 4, 2016 4:48 AM (in response to ragava28)> 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?
-
8. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
ragava28 May 4, 2016 12:48 PM (in response to shawkins)Steven,
I have attached the VDB.
Ragards
Raghu
-
ProficySource.vdb.zip 140.2 KB
-
-
9. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
shawkins May 5, 2016 3:32 AM (in response to ragava28)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'))
-
10. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
ragava28 May 31, 2016 3:31 PM (in response to shawkins)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'))
-
11. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
shawkins May 31, 2016 3:38 PM (in response to ragava28)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.
-
13. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
shawkins May 31, 2016 4:47 PM (in response to ragava28)> 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'))
-
14. Re: How to define TIMESTAMPADD(Source supporting functions) method input parameters, so that it can be pushed Source
ragava28 May 31, 2016 4:56 PM (in response to shawkins)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
===================