db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emmanuel Cecchet <m...@frogthinker.org>
Subject CLOB comparison behavior
Date Sat, 08 Nov 2008 00:02:19 GMT
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