11:42 a.m.
Author: shawkins
Date: 2010-10-13 12:42:50 -0400 (Wed, 13 Oct 2010)
New Revision: 2646
Modified:
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java
branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml
Log:
TEIID-1303 fix for sybase timestamp to string and full outer join handling. also updated
literal handling to work with JTDS
Modified:
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCExecutionFactory.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -603,7 +603,7 @@
*/
public String translateLiteralTime(Time timeValue) {
if (!hasTimeType()) {
- return "{ts '1970-01-01 " + formatDateValue(timeValue) +
"'}"; //$NON-NLS-1$ //$NON-NLS-2$
+ return translateLiteralTimestamp(new Timestamp(timeValue.getTime()));
}
return "{t '" + formatDateValue(timeValue) + "'}";
//$NON-NLS-1$ //$NON-NLS-2$
}
Modified:
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/access/AccessExecutionFactory.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -38,6 +38,7 @@
public AccessExecutionFactory() {
setSupportsOrderBy(false);
+ setDatabaseVersion("2003"); //$NON-NLS-1$
}
@Override
Modified:
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sqlserver/SQLServerExecutionFactory.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -24,6 +24,7 @@
*/
package org.teiid.translator.jdbc.sqlserver;
+import java.sql.Time;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@@ -204,5 +205,9 @@
public boolean booleanNullable() {
return true;
}
-
+
+ public boolean hasTimeType() {
+ return getDatabaseVersion().compareTo(V_2005) >= 0;
+ }
+
}
Modified:
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/sybase/SybaseExecutionFactory.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -25,11 +25,14 @@
package org.teiid.translator.jdbc.sybase;
import java.sql.CallableStatement;
+import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.LinkedList;
import java.util.List;
import org.teiid.language.Command;
@@ -37,10 +40,10 @@
import org.teiid.language.Limit;
import org.teiid.language.OrderBy;
import org.teiid.language.SetQuery;
+import org.teiid.translator.ExecutionContext;
+import org.teiid.translator.SourceSystemFunctions;
import org.teiid.translator.Translator;
import org.teiid.translator.TranslatorException;
-import org.teiid.translator.ExecutionContext;
-import org.teiid.translator.SourceSystemFunctions;
import org.teiid.translator.TypeFacility;
import org.teiid.translator.jdbc.AliasModifier;
import org.teiid.translator.jdbc.ConvertModifier;
@@ -60,6 +63,7 @@
public SybaseExecutionFactory() {
setDatabaseVersion(TWELVE_5);
+ setSupportsFullOuterJoins(false);
}
public void start() throws TranslatorException {
@@ -173,13 +177,13 @@
return Arrays.asList("stuff(stuff(convert(varchar, ",
function.getParameters().get(0), ", 102), 5, 1, '-'), 8, 1,
'-')"); //$NON-NLS-1$ //$NON-NLS-2$
}
- /**
- * Written to only support version 15
- * @param function
- * @return
- */
+ //TODO: this looses the milliseconds
protected List<?> convertTimestampToString(Function function) {
- return Arrays.asList("stuff(convert(varchar, ",
function.getParameters().get(0), ", 123), 11, 1, ' ')"); //$NON-NLS-1$
//$NON-NLS-2$
+ LinkedList<Object> result = new LinkedList<Object>();
+ result.addAll(convertDateToString(function));
+ result.add(' ');
+ result.addAll(convertTimeToString(function));
+ return result;
}
@Override
@@ -365,4 +369,15 @@
}
super.bindValue(stmt, param, paramType, i);
}
+
+ @Override
+ public String translateLiteralTimestamp(Timestamp timestampValue) {
+ return "CAST('" + formatDateValue(timestampValue) +"' AS
DATETIME)"; //$NON-NLS-1$ //$NON-NLS-2$
+ }
+
+ @Override
+ public String translateLiteralDate(Date dateValue) {
+ return "CAST('" + formatDateValue(dateValue) +"' AS
DATE)"; //$NON-NLS-1$ //$NON-NLS-2$
+ }
+
}
Modified:
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -559,7 +559,7 @@
helpTestVisitor(getTestVDB(),
"select {t '13:59:59'} FROM parts", //$NON-NLS-1$
null,
- "SELECT {ts '1970-01-01 13:59:59'} FROM PARTS");
//$NON-NLS-1$
+ "SELECT to_date('1970-01-01 13:59:59', 'YYYY-MM-DD
HH24:MI:SS') FROM PARTS"); //$NON-NLS-1$
}
@Test public void testTimestampLiteral() throws Exception {
Modified:
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseConvertModifier.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -105,7 +105,7 @@
LANG_FACTORY.createLiteral("date", String.class)},
//$NON-NLS-1$
java.sql.Date.class);
- helpGetString1(func, "cast(stuff(stuff(convert(varchar, {ts '1989-03-03
07:08:12.0'}, 102), 5, 1, '-'), 8, 1, '-') AS datetime)");
//$NON-NLS-1$
+ helpGetString1(func, "cast(stuff(stuff(convert(varchar,
CAST('1989-03-03 07:08:12.0' AS DATETIME), 102), 5, 1, '-'), 8, 1,
'-') AS datetime)"); //$NON-NLS-1$
}
/********************END of cast(date AS INPUT) ******************/
@@ -129,7 +129,7 @@
LANG_FACTORY.createLiteral("time", String.class)},
//$NON-NLS-1$
java.sql.Time.class);
- helpGetString1(func, "cast('1970-01-01 ' + convert(varchar, {ts
'1989-03-03 07:08:12.0'}, 8) AS datetime)"); //$NON-NLS-1$
+ helpGetString1(func, "cast('1970-01-01 ' + convert(varchar,
CAST('1989-03-03 07:08:12.0' AS DATETIME), 8) AS datetime)"); //$NON-NLS-1$
}
/********************END of cast(time AS INPUT) ******************/
@@ -152,7 +152,7 @@
LANG_FACTORY.createLiteral("timestamp", String.class)},
//$NON-NLS-1$
java.sql.Timestamp.class);
- helpGetString1(func, "{ts '1970-01-01 12:02:03'}");
//$NON-NLS-1$
+ helpGetString1(func, "CAST('1970-01-01 12:02:03.0' AS
DATETIME)"); //$NON-NLS-1$
}
@Test public void testDateToTimestamp() throws Exception {
@@ -162,7 +162,7 @@
LANG_FACTORY.createLiteral("timestamp", String.class)},
//$NON-NLS-1$
java.sql.Timestamp.class);
- helpGetString1(func, "{d '1989-03-03'}"); //$NON-NLS-1$
+ helpGetString1(func, "CAST('1989-03-03' AS DATE)");
//$NON-NLS-1$
}
/********************END of cast(timestamp AS INPUT) ******************/
@@ -185,7 +185,7 @@
LANG_FACTORY.createLiteral("string", String.class)},
//$NON-NLS-1$
String.class);
- helpGetString1(func, "stuff(convert(varchar, {ts '2003-11-01
12:05:02.0'}, 123), 11, 1, ' ')"); //$NON-NLS-1$
+ helpGetString1(func, "stuff(stuff(convert(varchar, CAST('2003-11-01
12:05:02.0' AS DATETIME), 102), 5, 1, '-'), 8, 1, '-')
convert(varchar, CAST('2003-11-01 12:05:02.0' AS DATETIME), 8)");
//$NON-NLS-1$
}
@Test public void testDateToString() throws Exception {
@@ -196,7 +196,7 @@
LANG_FACTORY.createLiteral("string", String.class)},
//$NON-NLS-1$
String.class);
- helpGetString1(func, "stuff(stuff(convert(varchar, {d
'2003-11-01'}, 102), 5, 1, '-'), 8, 1, '-')");
//$NON-NLS-1$
+ helpGetString1(func, "stuff(stuff(convert(varchar,
CAST('2003-11-01' AS DATE), 102), 5, 1, '-'), 8, 1, '-')");
//$NON-NLS-1$
}
@Test public void testTimeToString() throws Exception {
@@ -207,7 +207,7 @@
LANG_FACTORY.createLiteral("string", String.class)},
//$NON-NLS-1$
String.class);
- helpGetString1(func, "convert(varchar, {ts '1970-01-01 03:10:01'},
8)"); //$NON-NLS-1$
+ helpGetString1(func, "convert(varchar, CAST('1970-01-01 03:10:01.0'
AS DATETIME), 8)"); //$NON-NLS-1$
}
@Test public void testBigDecimalToString() throws Exception {
Modified:
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java
===================================================================
---
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/sybase/TestSybaseSQLConversionVisitor.java 2010-10-13
16:42:50 UTC (rev 2646)
@@ -189,21 +189,21 @@
public void testDateLiteral() {
helpTestVisitor(getTestVDB(),
"select {d '2002-12-31'} FROM parts", //$NON-NLS-1$
- "SELECT {d '2002-12-31'} FROM PARTS"); //$NON-NLS-1$
+ "SELECT CAST('2002-12-31' AS DATE) FROM PARTS");
//$NON-NLS-1$
}
@Test
public void testTimeLiteral() {
helpTestVisitor(getTestVDB(),
"select {t '13:59:59'} FROM parts", //$NON-NLS-1$
- "SELECT {ts '1970-01-01 13:59:59'} FROM PARTS");
//$NON-NLS-1$
+ "SELECT CAST('1970-01-01 13:59:59.0' AS DATETIME) FROM
PARTS"); //$NON-NLS-1$
}
@Test
public void testTimestampLiteral() {
helpTestVisitor(getTestVDB(),
"select {ts '2002-12-31 13:59:59'} FROM parts",
//$NON-NLS-1$
- "SELECT {ts '2002-12-31 13:59:59.0'} FROM PARTS");
//$NON-NLS-1$
+ "SELECT CAST('2002-12-31 13:59:59.0' AS DATETIME) FROM
PARTS"); //$NON-NLS-1$
}
@Test
Modified:
branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml
===================================================================
---
branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-10-13
15:46:43 UTC (rev 2645)
+++
branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-10-13
16:42:50 UTC (rev 2646)
@@ -1206,8 +1206,9 @@
<para>Subqueries supported in the criteria of the outer query include
subqueries in an IN clause, subqueries using the ANY/SOME or ALL predicate quantifier, and
subqueries using the EXISTS predicate. </para>
<example>
<title>Example Subquery in WHERE Using EXISTS</title>
- <programlisting>SELECT a FROM X WHERE EXISTS (SELECT b, c FROM Y WHERE
c=3)</programlisting>
+ <programlisting>SELECT a FROM X WHERE EXISTS (SELECT 1 FROM Y WHERE
c=X.a)</programlisting>
</example>
+ <note><para>EXISTS subqueries should typically follow the convention
"SELECT 1 FROM ..." to prevent unnecessary evaluation of select
expressions.</para></note>
<para>
The following usages of subqueries must each select only one column, but can
return any number of rows.
</para>