Lutz,

did you try to set the DEFAULT character set of the database to
ISO8859_1? It doesn't hurt all your fields because you specify their
character set at field level. But it would probably help with your
search queries.

Best Regards

Stefan

> 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