Hello Group,

I am experiencing big trouble while trying to solve the seemingly
simple problem of a case-insensitive search like select * from x
where upper(fieldname)=upper(:searchstring). what makes this task
difficult are the german umlauts.
the default character set of my database is none, collation and
charset are defined on field-level. i connect with lc_ctype=ISO8859_1
as a connection-parameter.
suppose i have a table defined like this:
create table colltest(bezeichnung varchar(100) character set ISO8859_1
collate de_DE);
with data like this:
insert into colltest(bezeichnung) values('Diät');
A Query
select upper(bezeichnung) from colltest where upper(bezeichnung) like
upper('%ä%');
returns nothing, which is correct, since firebird uppers field
'bezeichnung' with collation DE_DE but can not know what collation to
use to upper the searchstring. So you have 'Ä' on the left side and
'ä' on the right.
so i changed it to:
select upper(bezeichnung) from colltest where upper(bezeichnung) like
upper('%ä%' collate DE_DE);
and sure enough this returns the expected row. good, but not enough,
since i use firebird with jdbc and the 'hibernate' o/r-mapper and
therefore i need this to work with bind parameters in a prepared
statement. So i tried:
select upper(bezeichnung) from colltest where upper(bezeichnung) like
upper(? collate DE_DE);
this fails with "COLLATION DE_DE is not valid for specified CHARACTER
SET". seems firbird is unsure about the character set of the
parameter. I dont understand this, since i did not specify a character
set for the string '%ä%' either. so i tried to cast the parameter to a
specific character set:
select upper(bezeichnung) from colltest where upper(bezeichnung) like
upper(cast(? as varchar(50) character set ISO8859_1) collate DE_DE);
but this returns with: "-804 Data type unknown". I dont understand
what is unknown here, since everything is casted explicitly. The
casting syntax seems to be correct also, since:
select upper(bezeichnung) from colltest where upper(bezeichnung) like
upper(cast('%ä%' as varchar(50) character set ISO8859_1) collate DE_DE);
works fine.
Does anybody have a solution to this? using an uppered shadow field is
not an option, because i have to support multiple rdbms products and
therefore use the hibernate query language hql. so i have no
possibility to access a different field when "uppering" something.

Lutz