db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emmanuel Cecchet <m...@frogthinker.org>
Subject Re: CLOB comparison behavior
Date Tue, 11 Nov 2008 18:56:38 GMT
Hi Rick,

Thanks for the clarification.
I guess that if I know in advance the size of the varchar (which is 
needed for the cast) there is no need for me to use a CLOB and I should 
use varchar upfront.
I still don't really get why there is this limitation with CLOBs but at 
least now I know that it won't work with the = operator.

Thanks again for your help,
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
>>
>
>


-- 
Emmanuel Cecchet
FTO @ Frog Thinker 
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet


Mime
View raw message