[prev in list] [next in list] [prev in thread] [next in thread] 

List:       ojb-dev
Subject:    Re: Oracle and case-insensitive search
From:       Martin_Kalén <mkalen () apache ! org>
Date:       2004-08-24 11:41:30
Message-ID: 412B296A.5040909 () apache ! org
[Download RAW message or body]

Frank Renaers wrote:

> In Oracle a search on a string (Criteria.addLike) seems to be Case
> Sensitive.
> 
> How do you guys solve this problem.

Normally with Oracle, you would create your indexes on UPPER(COLUMN_NAME) and use
Java's String.toUpperCase() function when performing like-searches.

> In MsSql and MySql a Criteria.addLike is not case sensitive !

Then change your code to use .toUpperCase and your criteria to user Uit will work 
cross-platform between all three of them with your current OJB version.


I don't think this will be added permanently to OJB since we then loose the possibility to 
distinguish between eg "SELECT x FROM y WHERE z LIKE 'value%';" and "SELECT x FROM y WHERE 
z LIKE 'VALUE%'" in able databases like Oracle. (Plus we can't really break existing 
semantics.)

What we could consider is to use the PostreSQL middle way with eg "Criteria.addIlike". 
PostgreSQL uses the ILIKE-operator for case-insensitive (locale-aware) matching.

Hibernate's Expression works like this. See 
http://www.hibernate.org/hib_docs/api/net/sf/hibernate/expression/Expression.html


MySQL seems to be able to perform case-sensitive matching with a 'binary string' 
directive. See http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html


MSSQL also seems to be able to get case-sensivity either at install-time or by 
specififying collation parameters at query-time. See 
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm


Keep existing RDBMS-depentent functionality and add two new well-definied methods 
controlled by the platform impl?

Any comments? Jakob?

Regards,
  Martin

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]