db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas J. Taylor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-342) ERROR 42818: Comparisons between 'LONG VARCHAR' and 'CHAR' are not supported
Date Wed, 08 Jun 2005 14:47:29 GMT
    [ 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


Mime
View raw message