db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject RE: case sensitive comparisons
Date Wed, 01 Nov 2006 17:57:13 GMT
MS SQL Server (and I think Sybase) have server level settings
to determine if searchs are case sensitive or not (at install
time only!).  In some version, like SQLServer 2000, this can 
be set at the individual database level (at creation time).
The default is for all searches to be case-insensitive.

I'm not sure about SQL 2005, but I think it might be more
flexible in this regard.

FWIW, this is the option that determines if the server will
treat SQL identifiers as case sensitive or not.  This is 
because identifiers are looked up in the Syscatlog tables 
and these are effected by the overall server setting.

> -----Original Message-----
> From: Thomas Fischer [mailto:tfischer@apache.org] 
> Sent: Wednesday, November 01, 2006 7:50 AM
> To: torque-dev@db.apache.org
> Subject: case sensitive comparisons
> 
> Hi,
> 
> I am currently looking into the scape text problems 
> (TORQUE-57), and, as always when it comes changing the SQL 
> generation, one sees one thing after another that should be 
> changed.  So I ran into the issue of case-sensitive and 
> case-insensitive compares (for case insensitive likes, 
> postgres uses ILIKE, but most other databases have to do 
> UPPER()). When testing this, I was astonished to see that 
> mysql by default does case insensitive comparisons by default.
> 
> Now I'm asking myself the following:
> 
> - are there other databases where string comparisons are case 
> insensitive by default ? To get clues, I looked at the 
> DB.ignoreCase() method in the dapters, and I found that in 
> addition to mysql, there is no action performed in the 
> following databases :
> DBAxion, DBCloudscape, DBInformix, DBInstantDB, DBWeblogic 
> Does anybody know wheter comparison is indeed 
> case-insensitive in these databases ?
> 
> - Of course this different behaviour is not good for 
> compatibility between databases. One can force mysql to make 
> case sensitive comparisons by adding the keyword BINARY to 
> the comparison. The manual says this can have the following 
> side effects
> 1) trailing spaces are important (so obviously, they are not 
> important in
>     usual comparisons, which sounds also strange to me)
> 2) it may happen that indices are not used efficiently.
> Given this, forcing mysql to compare case-sensitive by 
> default is not a good idea in my opinion. However, what we 
> could do is the following:
> - use a Boolean instead of a boolean for ignoreCase in 
> Criteria and Criterion. Default would be null (I don't care 
> whether comparison is case sensitive or case insensitive) and 
> would use the database default. Setting this to Boolean.TRUE 
> would mean "force comparison to be case-sensitive" 
> and setting it to Boolean.FALSE would mean "force comparison 
> to be case insensitive". We could throw an SqlException if a 
> particular database does not offer a particular mode.
> Of course, this is a change in the API, so this would 
> definitely not be in 3.2.1, but I'd target that in 4.0
> 
>    Any opinions ?
> 
>        Thomas
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
> 
> 

Duke CE Privacy Statement
Please be advised that this e-mail and any files transmitted with it are confidential communication
or may otherwise be privileged or confidential and are intended solely for the individual
or entity to whom they are addressed.  If you are not the intended recipient you may not rely
on the contents of this email or any attachments, and we ask that you  please not read, copy
or retransmit this communication, but reply to the sender and destroy the email, its contents,
and all copies thereof immediately.  Any unauthorized dissemination, distribution or copying
of this communication is strictly prohibited.



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


Mime
View raw message