You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2009/01/09 18:40:20 UTC
svn commit: r733094 - in /db/derby/code/branches/10.3/java:
engine/org/apache/derby/iapi/types/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Fri Jan 9 09:40:19 2009
New Revision: 733094
URL: http://svn.apache.org/viewvc?rev=733094&view=rev
Log:
DERBY-3975: SELECT DISTINCT may return duplicates with territory-based collation
Merged revision 728822 from trunk.
Modified:
db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java Fri Jan 9 09:40:19 2009
@@ -163,6 +163,16 @@
return holderForCollationSensitiveInfo.stringCompare(char1, char2);
}
+ /**
+ * Return a hash code that is consistent with
+ * {@link #stringCompare(SQLChar, SQLChar)}.
+ *
+ * @return hash code
+ */
+ public int hashCode() {
+ return hashCodeForCollation();
+ }
+
/**
* This method implements the like function for char (with no escape value).
* The difference in this method and the same method in superclass is that
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java Fri Jan 9 09:40:19 2009
@@ -164,6 +164,16 @@
return holderForCollationSensitiveInfo.stringCompare(char1, char2);
}
+ /**
+ * Return a hash code that is consistent with
+ * {@link #stringCompare(SQLChar, SQLChar)}.
+ *
+ * @return hash code
+ */
+ public int hashCode() {
+ return hashCodeForCollation();
+ }
+
/**
* This method implements the like function for char (with no escape value).
* The difference in this method and the same method in superclass is that
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java Fri Jan 9 09:40:19 2009
@@ -165,6 +165,16 @@
return holderForCollationSensitiveInfo.stringCompare(char1, char2);
}
+ /**
+ * Return a hash code that is consistent with
+ * {@link #stringCompare(SQLChar, SQLChar)}.
+ *
+ * @return hash code
+ */
+ public int hashCode() {
+ return hashCodeForCollation();
+ }
+
/**
* This method implements the like function for char (with no escape value).
* The difference in this method and the same method in superclass is that
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java Fri Jan 9 09:40:19 2009
@@ -171,6 +171,16 @@
return holderForCollationSensitiveInfo.stringCompare(char1, char2);
}
+ /**
+ * Return a hash code that is consistent with
+ * {@link #stringCompare(SQLChar, SQLChar)}.
+ *
+ * @return hash code
+ */
+ public int hashCode() {
+ return hashCodeForCollation();
+ }
+
/**
* This method implements the like function for char (with no escape value).
* The difference in this method and the same method in superclass is that
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java Fri Jan 9 09:40:19 2009
@@ -2655,6 +2655,11 @@
*/
public int hashCode()
{
+ if (SanityManager.DEBUG) {
+ SanityManager.ASSERT(!(this instanceof CollationElementsInterface),
+ "SQLChar.hashCode() does not work with collation");
+ }
+
try {
if (getString() == null)
{
@@ -2703,6 +2708,24 @@
}
/**
+ * Hash code implementation for collator sensitive subclasses.
+ */
+ int hashCodeForCollation() {
+ CollationKey key = null;
+
+ try {
+ key = getCollationKey();
+ } catch (StandardException se) {
+ // ignore exceptions, like we do in hashCode()
+ if (SanityManager.DEBUG) {
+ SanityManager.THROWASSERT("Unexpected exception", se);
+ }
+ }
+
+ return key == null ? 0 : key.hashCode();
+ }
+
+ /**
* Get a SQLVarchar for a built-in string function.
*
* @return a SQLVarchar.
Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Fri Jan 9 09:40:19 2009
@@ -279,59 +279,78 @@
//End of parameter testing
s.close();
- compareAgrave(conn,1,1);
+ compareAgrave(1, 1, 2);
}
public void testFrenchCollation() throws SQLException {
- Connection conn = getConnection();
- compareAgrave(conn,2,1);
+ compareAgrave(2, 1, 1);
}
-
/**
* For a TERRITORY_BASED collation french database, differences between pre-composed accents such
* as "\u00C0" (A-grave) and combining accents such as "A\u0300" (A, combining-grave) should match
* for = and like. But they do not match for UCS_BASIC. We insert both into a table and search
* based on equal and like.
*
- * @param conn
* @param expectedMatchCountForEqual number of rows we expect back for =.
* 2 for French, 1 for English
* @param expectedMatchCountForLike number of rows we expect back for LIKE.
* 1 for French and English
+ * @param expectedDistinctRows number of rows expected from SELECT DISTINCT
* @throws SQLException
*/
- private void compareAgrave(Connection conn, int expectedMatchCountForEqual,
- int expectedMatchCountForLike) throws SQLException {
-
+ private void compareAgrave(int expectedMatchCountForEqual,
+ int expectedMatchCountForLike, int expectedDistinctRows)
+ throws SQLException {
+ String[] dataTypes = {"VARCHAR(5)", "CHAR(5)"};
+ for (int i = 0; i < dataTypes.length; i++) {
+ compareAgrave(dataTypes[i], expectedMatchCountForEqual,
+ expectedMatchCountForLike, expectedDistinctRows);
+ }
+}
+
+/**
+ * Helper for {@link #compareAgrave(int, int, int)} which performs the test
+ * for one data type.
+ */
+private void compareAgrave(String dataType, int expectedMatchCountForEqual,
+ int expectedMatchCountForLike, int expectedDistinctRows)
+ throws SQLException {
+
+ // Create the two strings that are supposed to be equal in French locale.
String agrave = "\u00C0";
String agraveCombined ="A\u0300";
- Statement s = conn.createStatement();
+
+ Statement s = createStatement();
try {
s.executeUpdate("DROP TABLE T");
}catch (SQLException se) {}
- s.executeUpdate("CREATE TABLE T (vc varchar(30))");
- PreparedStatement ps = conn.prepareStatement("INSERT INTO T VALUES (?)");
+ s.executeUpdate("CREATE TABLE T (vc " + dataType + ")");
+ PreparedStatement ps = prepareStatement("INSERT INTO T VALUES (?)");
ps.setString(1,agrave);
ps.executeUpdate();
ps.setString(1,agraveCombined);
ps.executeUpdate();
- ps.close();
- ps = conn.prepareStatement("SELECT COUNT(*) FROM T WHERE VC = ?");
+ ps = prepareStatement("SELECT COUNT(*) FROM T WHERE VC = ?");
ps.setString(1, agrave);
ResultSet rs = ps.executeQuery();
JDBC.assertSingleValueResultSet(rs, Integer.toString(expectedMatchCountForEqual));
- ps = conn.prepareStatement("SELECT COUNT(*) FROM T WHERE VC LIKE ?");
+ // Use '%' at the end of the pattern so that we also match the trailing
+ // blanks if the data type is CHAR instead of VARCHAR.
+ ps = prepareStatement("SELECT COUNT(*) FROM T WHERE VC LIKE ? || '%'");
ps.setString(1, agrave);
rs = ps.executeQuery();
JDBC.assertSingleValueResultSet(rs, Integer.toString(expectedMatchCountForLike));
- rs.close();
- ps.close();
- s.close();
+
+ // DERBY-3975: They should match for distinct, the same way as for =
+ int distinctRows = JDBC.assertDrainResults(
+ s.executeQuery("SELECT DISTINCT VC FROM T"));
+ assertEquals("Unexpected number of distinct rows",
+ expectedDistinctRows, distinctRows);
}
@@ -545,8 +564,9 @@
private void commonTestingForTerritoryBasedDB(Statement s) throws SQLException{
PreparedStatement ps;
- ResultSet rs;
- Connection conn = s.getConnection();
+ ResultSet rs;
+
+ Connection conn = s.getConnection();
s.executeUpdate("set schema APP");
//Following sql will fail because the compilation schema is user schema
@@ -751,12 +771,12 @@
null);
//Do some parameter testing for XMLSERIALIZE. ? is not supported inside
//the XMLSERIALIZE function and hence following will result in errors.
- checkPreparedStatementError(conn, "SELECT XMLSERIALIZE(x as CHAR(10)) " +
+ assertCompileError("42Z70", "SELECT XMLSERIALIZE(x as CHAR(10)) " +
" FROM xmlTable, SYS.SYSTABLES WHERE " +
- " XMLSERIALIZE(? as CHAR(10)) = TABLENAME", "42Z70");
- checkPreparedStatementError(conn, "SELECT XMLSERIALIZE(x as CHAR(10)) FROM " +
+ " XMLSERIALIZE(? as CHAR(10)) = TABLENAME");
+ assertCompileError("42Z70", "SELECT XMLSERIALIZE(x as CHAR(10)) FROM " +
" xmlTable, SYS.SYSTABLES WHERE XMLSERIALIZE(? as CHAR(10)) = " +
- " CAST(TABLENAME AS CHAR(10))", "42Z70");
+ " CAST(TABLENAME AS CHAR(10))");
}
//Start of user defined function testing
@@ -782,7 +802,7 @@
//from the context which in this case is from TABLENAME and TABLENAME
//has collation type of UCS_BASIC
s.executeUpdate("set schema APP");
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" ? = TABLENAME");
ps.setString(1, "SYSCOLUMNS");
rs = ps.executeQuery();
@@ -796,13 +816,13 @@
//territory based since the result of SUBSTR always picks up the
//collation of it's first operand. So the comparison between left hand
//side with terriotry based and right hand side with UCS_BASIC will fail.
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
- " SUBSTR(?,2) = TABLENAME", "42818");
+ assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " SUBSTR(?,2) = TABLENAME");
//To fix the problem above, we need to CAST TABLENAME so that the result
//of CAST will pick up the collation of the current schema and this will
//cause both the operands of SUBSTR(?,2) = CAST(TABLENAME AS CHAR(10))
//to have same collation
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" SUBSTR(?,2) = CAST(TABLENAME AS CHAR(10))");
ps.setString(1, "aSYSCOLUMNS");
rs = ps.executeQuery();
@@ -816,16 +836,16 @@
//take collation from context which here will be TABLENAME and hence the
//result of concatenation will have collation type of it's 2 operands,
//namely UCS_BASIC
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES " +
- " WHERE TABLENAME || ? LIKE 'SYSCOLUMNS '", "42ZA2");
+ assertCompileError("42ZA2", "SELECT TABLENAME FROM SYS.SYSTABLES " +
+ " WHERE TABLENAME || ? LIKE 'SYSCOLUMNS '");
//The query above can be made to work if we are in SYS schema or if we use
//CAST while we are trying to run the query is user schema
//Let's try CAST first
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" CAST((TABLENAME || ?) AS CHAR(20)) LIKE 'SYSCOLUMNS'");
//try switching to SYS schema and then run the original query without CAST
s.executeUpdate("set schema SYS");
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
" WHERE TABLENAME || ? LIKE 'SYSCOLUMNS'");
s.executeUpdate("set schema APP");
//The following will fail because the left hand side of LIKE has collation
@@ -843,13 +863,13 @@
//Do parameter testing for IS NULL
//Following query will pass because it doesn't matter what the collation of
//? is when doing a NULL check
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" ? IS NULL");
ps.setString(1, " ");
rs = ps.executeQuery();
JDBC.assertEmpty(rs);
//Now do the testing for IS NOT NULL
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" ? IS NOT NULL");
ps.setNull(1, java.sql.Types.VARCHAR);
rs = ps.executeQuery();
@@ -859,28 +879,28 @@
//Following query will fail because LENGTH operator is not allowed to take
//a parameter. I just wanted to have a test case out for the changes that
//are going into engine code (ie LengthOperatorNode)
- checkPreparedStatementError(conn, "SELECT COUNT(*) FROM CUSTOMER WHERE " +
- " LENGTH(?) != 0", "42X36");
+ assertCompileError("42X36", "SELECT COUNT(*) FROM CUSTOMER WHERE " +
+ " LENGTH(?) != 0");
//Do parameter testing for BETWEEN
//Following should pass for ? will take the collation from the context and
//hence, it will be UCS_BASIC
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" TABLENAME NOT BETWEEN ? AND TABLENAME");
ps.setString(1, " ");
rs = ps.executeQuery();
JDBC.assertEmpty(rs);
//Following will fail because ? will take collation of territory based but
//the left hand side has collation of UCS_BASIC
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
- " TABLENAME NOT BETWEEN ? AND 'SYSCOLUMNS'", "42818");
+ assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " TABLENAME NOT BETWEEN ? AND 'SYSCOLUMNS'");
//Do parameter testing with COALESCE
//following will pass because the ? inside the COALESCE will take the
//collation type of the other operand which is TABLENAME. The result of
//COALESCE will have collation type of UCS_BASIC and that is the same
//collation that the ? on rhs of = will get.
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" COALESCE(TABLENAME, ?) = ?");
ps.setString(1, " ");
ps.setString(2, "SYSCOLUMNS ");
@@ -895,22 +915,22 @@
//territory based since the result of LTRIM always picks up the
//collation of it's operand. So the comparison between left hand
//side with terriotry based and right hand side with UCS_BASIC will fail.
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
- " LTRIM(?) = TABLENAME", "42818");
+ assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " LTRIM(?) = TABLENAME");
//To fix the problem above, we need to CAST TABLENAME so that the result
//of CAST will pick up the collation of the current schema and this will
//cause both the operands of LTRIM(?) = CAST(TABLENAME AS CHAR(10))
//to have same collation
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" LTRIM(?) = CAST(TABLENAME AS CHAR(10))");
ps.setString(1, " SYSCOLUMNS");
rs = ps.executeQuery();
JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});
//Similar testing for RTRIM
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
- " RTRIM(?) = TABLENAME", "42818");
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " RTRIM(?) = TABLENAME");
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" RTRIM(?) = CAST(TABLENAME AS CHAR(10))");
ps.setString(1, "SYSCOLUMNS ");
rs = ps.executeQuery();
@@ -922,12 +942,12 @@
//And the ? in TRIM will pick up it's collation from 'a' and hence the
//comparison between territory based character string returned from TRIM
//function will fail against UCS_BASIC based TABLENAME on the right
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
- " TRIM('a' FROM ?) = TABLENAME", "42818");
+ assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " TRIM('a' FROM ?) = TABLENAME");
//The problem can be fixed by using CAST on TABLENAME so the resultant of
//CAST string will compare fine with the output of TRIM. Note CAST always
//picks up the collation of the compilation schema.
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" TRIM('a' FROM ?) = CAST(TABLENAME AS CHAR(10))");
ps.setString(1, "aSYSCOLUMNS");
rs = ps.executeQuery();
@@ -937,7 +957,7 @@
//from it's first parameter which is a SUBSTR on TABLENAME and hence the
//result of TRIM will have UCS_BASIC collation which matches the collation
//on the right.
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" TRIM(LEADING SUBSTR(TABLENAME, LENGTH(TABLENAME)) FROM ?) = TABLENAME");
ps.setString(1, "SYSCOLUMNS");
rs = ps.executeQuery();
@@ -948,13 +968,13 @@
//Following will fail because 'LOOKFORME' has collation of territory based
//but TABLENAME has collation of UCS_BASIC and hence LOCATE will fail
//because the collation types of it's two operands do not match
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" LOCATE(?, TABLENAME) != 0");
ps.setString(1, "ABC");
rs = ps.executeQuery();
JDBC.assertEmpty(rs);
//Just switch the parameter position and try the sql again
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" LOCATE(TABLENAME, ?) != 0");
ps.setString(1, "ABC");
rs = ps.executeQuery();
@@ -964,7 +984,7 @@
//Following will work just fine because ? will take it's collation from the
//context which in this case will be collation of TABLENAME which has
//collation type of UCS_BASIC.
- ps = conn.prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE ? IN " +
+ ps = prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE ? IN " +
" (SELECT TABLENAME FROM SYS.SYSTABLES)");
ps.setString(1, "SYSCOLUMNS");
rs = ps.executeQuery();
@@ -976,14 +996,14 @@
//current schema which is the user schema and hence it's collation type
//will be territory based. But that collation does not match the left hand
//side on IN clause and hence it results in compliation error.
- checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES " +
+ assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES " +
" WHERE TABLENAME NOT IN (?, ' SYSCOLUMNS ') AND " +
- " CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ", "42818");
+ " CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
//We can make the query work in 2 ways
//1)Be in the SYS schema and then ? will take the collation of UCS_BASIC
//because that is what the character string literal ' SYSCOLUMNS ' has.
s.executeUpdate("set schema SYS");
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
" WHERE TABLENAME NOT IN (?, ' SYSCOLUMNS ') AND " +
" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
ps.setString(1, "aSYSCOLUMNS");
@@ -992,7 +1012,7 @@
//2)The other way to fix the query would be to do a CAST on TABLENAME so
//it will have the collation of current schema which is APP
s.executeUpdate("set schema APP");
- ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
" CAST(TABLENAME AS CHAR(10)) NOT IN (?, ' SYSCOLUMNS ') AND " +
" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
ps.setString(1, "aSYSCOLUMNS");
@@ -1002,7 +1022,7 @@
//Following will not fail because collation of ? here does not matter
//since we are not doing a collation related method
s.executeUpdate("set schema SYS");
- ps = conn.prepareStatement("INSERT INTO APP.CUSTOMER(NAME) VALUES(?)");
+ ps = prepareStatement("INSERT INTO APP.CUSTOMER(NAME) VALUES(?)");
ps.setString(1, "SYSCOLUMNS");
ps.executeUpdate();
ps.close();
@@ -1039,7 +1059,7 @@
s.execute("create table assoc (x char(10) not null primary key, "+
" y char(100))");
s.execute("create table assocout(x char(10))");
- ps = conn.prepareStatement("insert into assoc values (?, 'hello')");
+ ps = prepareStatement("insert into assoc values (?, 'hello')");
ps.setString(1, new Integer(10).toString());
ps.executeUpdate();
@@ -1080,7 +1100,7 @@
//by number of collation elements that special character _ represents
s.executeUpdate("create table DERBY_2967(c11 int)");
s.executeUpdate("insert into DERBY_2967 values 1");
- ps = conn.prepareStatement("select 1 from DERBY_2967 where '\uFA2D' like ?");
+ ps = prepareStatement("select 1 from DERBY_2967 where '\uFA2D' like ?");
String[] match = { "%", "_", "\uFA2D" };
for (int i = 0; i < match.length; i++) {
ps.setString(1, match[i]);
@@ -1201,24 +1221,6 @@
}
/**
- * Make sure that attempt to prepare the statement will give the passed error
- *
- * @param con Connection on which query should be prepared
- * @param query Query to be prepared
- * @param error Prepared statement will give this error for the passed query
- */
-private void checkPreparedStatementError(Connection con, String query,
- String error)
-{
- try{
- con.prepareStatement(query);
- fail("Expected error '" + error + "' but no error was thrown.");
- } catch (SQLException sqle) {
- assertSQLState(error, sqle);
- }
-
-}
-/**
* Execute the passed statement and compare the results against the
* expectedResult
*