[teiid-issues] [JBoss JIRA] Commented: (TEIID-360) SQLServer SQLTranslator issues

Sat Jul 25 00:05:58 EDT 2009

    [ https://jira.jboss.org/jira/browse/TEIID-360?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12477504#action_12477504 ] 

Steven Hawkins commented on TEIID-360:
--------------------------------------

>From a recent dev list discussion, I proposed users attempting to represent source sybase/sql server datetime columns as time types in Teiid do so by using a timestamp in a physical layer (which is the default upon import of a datetime), then perform type reconciliation in a virtual model to a time type using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source datatime>), time)

This was forced due to the change in this issue of the time literal year to 1970 rather than 1900.  There were other situations that would fail using the 1900 approach, since the engine assumes that the conversion of time to timestamp uses the year 1970 and connector prepared statement binds of time values use 1970 as well.  

The rationale for the change to 1970 and the proposed workaround above was to ensure consistency in all situations - without putting undo effort in the connector level.

The follow-on issues to that suggested fix are that the sql server translator needs to support timestampadd and it would be good to have the rewriter understand how to invert the timestampadd function.  Also since anyone following the fix won't be affected, I will change back the time literal translation back to 1900 to support legacy usage (however I suspect that this is a bit of an internally manufactured issue).

> SQLServer SQLTranslator issues
> ------------------------------
>
>                 Key: TEIID-360
>                 URL: https://jira.jboss.org/jira/browse/TEIID-360
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 6.0.0
>            Reporter: Steven Hawkins
>             Fix For: 6.2.0
>
>
> The time literal for Access/SQLServer is incorrect - should use 1970 not 1900
> Limit for a set op needs to use a subquery
> Support for repeat should be aliased to replicate

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira