[
http://issues.apache.org/jira/browse/DERBY-342?page=comments#action_12313047 ]
Thomas J. Taylor commented on DERBY-342:
----------------------------------------
I haven't seen any documentation in the 10.0 manuals anywhere documenting this
behavior.
However, it seems that the limitation is that Derby doesn't support comparing
'LONG VARCHAR' with anything (not just 'CHAR'). This is also a problem with the
CLOB data type.
For example, if I run the following Java code, it fails for both
java.sql.Statements and java.sql.PreparedStatements with different exceptions:
public class DerbyLONGVARCHARTest {
public static void main(String[] args) {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
java.sql.Connection conn =
java.sql.DriverManager.getConnection("jdbc:derby:c:/derby/LONGVARCHARTestDB;create=true");
java.sql.Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE test (id INTEGER, value LONG VARCHAR)");
stmt.executeUpdate("INSERT INTO test VALUES (1,
'abcdefghijklmnopqrstuvwxyz'), (2, '0123456789')");
// SELECT statement
try {
ResultSet rs = stmt.executeQuery("SELECT id FROM test WHERE value =
'0123456789'"); // THROWS SQLException
while (rs.next()) {
System.out.println("id="+rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace(); // prints: "ERROR 42818: Comparisons between
'LONG VARCHAR' and 'CHAR' are not supported."
}
try {
java.sql.PreparedStatement pstmt = conn.prepareStatement("SELECT id
FROM test WHERE value = ?");
pstmt .setString(1, "0123456789");
java.sql.ResultSet rs = preparedSelect.executeQuery(); // THROWS
SQLException
while (rs.next()) {
System.out.println("id="+rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace(); // prints "ERROR 42818: Comparisons between
'LONG VARCHAR' and 'LONG VARCHAR' are not supported."
}
}
}
> ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported
> ----------------------------------------------------------------------------
>
> Key: DERBY-342
> URL: http://issues.apache.org/jira/browse/DERBY-342
> Project: Derby
> Type: Improvement
> Components: JDBC, Tools
> Versions: 10.0.2.1
> Environment: Windows XP Professional, Service Pack 2; Dell Latitude D600;
> 1.5GB RAM
> Reporter: Thomas J. Taylor
>
> Trying to run the same SQL query through JDBC-Derby and IJ results in the
> same error:
> ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported.
> The same Java code/query works with MySQL 4.1 (Connector/J) and MS Access
> 2002 (JDBC-ODBC)
> Table Definition:
> CREATE TABLE itemdata (valuetext LONG VARCHAR);
> SQL Statement
> ij>SELECT * FROM itemdata WHERE valuetext = 'asdf';
> ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not
> supported.
> I have tried to use the 'cast' function to perform a LONG VARCHAR comparison:
> ij> select * from itemdata where valuetext = cast('asdf' as long varchar);
> ERROR 42818: Comparisons between 'LONG VARCHAR' and 'LONG VARCHAR' are not
> supported.
> The only way I've been able to get ANY result is the following:
> ij> select * from itemdata where cast(valuetext as char) = 'asdf';
> VALUETEXT
>
>
> --------------------------------------------------------------------------------------------------------------------------------
> ERROR 22001: A truncation error was encountered trying to shrink CHAR
> 'asdf' to length 1.
> If I try to cast to VARCHAR, I get a syntax error:
> ij> select * from itemdata where cast(valuetext as varchar) = 'asdf';
> ERROR 42X01: Syntax error: Encountered ")" at line 1, column 63.
> I also have this problem if I change itemdata.valuetext to CLOB.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
