db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: CLOB comparison behavior
Date Mon, 10 Nov 2008 13:42:41 GMT
Hi Emmanuel,

Comparison operators are not supported on large object values. This 
limitation is discussed in the Derby Reference Guide in a section titled 
"Mapping of java.sql.Blob and java.sql.Clob interfaces": 
http://db.apache.org/derby/docs/10.4/ref/ You can probably achieve what 
you need by casting the Clob to a smaller string type. E.g.:

create table t ( a clob, b clob );

insert into t( a, b ) values ( 'abc', 'abc' );

-- fails
select * from t where a = b;

-- succeeds
select * from t
where cast ( a as varchar( 1000 ) ) = cast (b as varchar( 1000 ) );

Hope this helps,
-Rick

Emmanuel Cecchet wrote:
> Hi,
>
> I have a problem when comparing CLOBs using - instead of LIKE.
> Here is the use case:
> ij> connect 'jdbc:derby:foo;create=true';
> ij> create table foo (id int, data clob);
> 0 rows inserted/updated/deleted
> ij> insert into foo values (1, 'joe the plumber');
> 1 row inserted/updated/deleted
> ij> select * from foo where data LIKE 'joe the plumber';
> ID         |DATA
> ------------------------------------------------------------
> 1          |joe the plumber
>
> 1 row selected
> ij> select * from foo where data='joe the plumber';
> ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR 
> (UCS_BASIC)' are not supported. Types must be comparable. String types 
> must also have matching collation. If collation does not match, a 
> possible solution is to cast operands toforce them to the default 
> collation (e.g. SELECT tablename FROM sys.systables WHERE 
> CAST(tablename AS VARCHAR(128)) = 'T1')
>
> I kind of expected that one so I did cast as follows:
>
> ij> select * from foo where data=CAST('joe the plumber' AS CLOB);
> ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CLOB 
> (UCS_BASIC)' are not supported. Types must be comparable. String types 
> must also have matching collation. If collation does not match, a 
> possible solution is to cast operands to force them to the default 
> collation (e.g. SELECT tablename FROM sys.systables WHERE 
> CAST(tablename AS VARCHAR(128)) = 'T1')
>
> The same things happens if I use PreparedStatement.setClob(). Can't we 
> compare 2 CLOBs with = ?
>
> Thanks in advance for your help,
> Emmanuel
>


Mime
View raw message