Author: shawkins Date: 2012-01-16 12:31:15 -0500 (Mon, 16 Jan 2012) New Revision: 3794 Modified: branches/7.6.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java branches/7.6.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java Log: TEIID-1864 correcting frac_second handling for sybase Modified: branches/7.6.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java =================================================================== --- branches/7.6.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2012-01-13 19:26:57 UTC (rev 3793) +++ branches/7.6.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2012-01-16 17:31:15 UTC (rev 3794) @@ -36,10 +36,13 @@ import java.util.List; import org.teiid.language.Command; +import org.teiid.language.Expression; import org.teiid.language.Function; import org.teiid.language.LanguageObject; import org.teiid.language.Limit; +import org.teiid.language.Literal; import org.teiid.language.OrderBy; +import org.teiid.language.SQLConstants; import org.teiid.language.SetQuery; import org.teiid.translator.ExecutionContext; import org.teiid.translator.SourceSystemFunctions; @@ -100,10 +103,30 @@ registerFunctionModifier(SourceSystemFunctions.QUARTER, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.SECOND, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.WEEK, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, new EscapeSyntaxModifier()); - registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.LENGTH, new EscapeSyntaxModifier()); registerFunctionModifier(SourceSystemFunctions.ATAN2, new EscapeSyntaxModifier()); + registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, new EscapeSyntaxModifier() { + + @Override + public List<?> translate(Function function) { + if (!isFracSeconds(function)) { + return super.translate(function); + } + //convert from billionths to thousandths + return Arrays.asList("dateadd(millisecond, ", function.getParameters().get(1), "/1000000, ", function.getParameters().get(2), ")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + }); + registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, new EscapeSyntaxModifier() { + + @Override + public List<?> translate(Function function) { + if (!isFracSeconds(function)) { + return super.translate(function); + } + //convert from billionths to thousandths + return Arrays.asList("datediff(millisecond, ", function.getParameters().get(1), ",", function.getParameters().get(2), ")*1000000"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + } + }); //add in type conversion ConvertModifier convertModifier = new ConvertModifier(); @@ -390,5 +413,10 @@ protected boolean supportsCrossJoin() { return false; } + + private boolean isFracSeconds(Function function) { + Expression e = function.getParameters().get(0); + return (e instanceof Literal && SQLConstants.NonReserved.SQL_TSI_FRAC_SECOND.equalsIgnoreCase((String)((Literal)e).getValue())); + } } Modified: branches/7.6.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java =================================================================== --- branches/7.6.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2012-01-13 19:26:57 UTC (rev 3793) +++ branches/7.6.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2012-01-16 17:31:15 UTC (rev 3794) @@ -250,5 +250,10 @@ helpTestVisitor(getBQTVDB(), input, output); } - + @Test public void testTimestampFunctions() { + helpTestVisitor(getBQTVDB(), + "SELECT timestampadd(sql_tsi_second, 1, timestampvalue), timestampadd(sql_tsi_frac_second, 1000, timestampvalue), timestampdiff(sql_tsi_frac_second, timestampvalue, timestampvalue) from bqt1.smalla", //$NON-NLS-1$ + "SELECT {fn timestampadd(sql_tsi_second, 1, SmallA.TimestampValue)}, dateadd(millisecond, 1000/1000000, SmallA.TimestampValue), datediff(millisecond, SmallA.TimestampValue,SmallA.TimestampValue)*1000000 FROM SmallA"); //$NON-NLS-1$ + } + }