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: [EMAIL PROTECTED]
Skype: emmanuel_cecchet
