From: Lachlan D. <lac...@gm...> - 2009-04-23 05:21:08
|
On 23/04/2009, at 1:42 PM, Thomas Wedderburn-Bisshop wrote: > thanks, that's really helpful. I would happily turn off the default > behaviour. > > But the real problem is that the performance is poor because WO is > generating all that UPPER() SQL when I tell it I want a case- > insensitive search, rather than using a collation. What you want is to create indexes in mysql for UPPER(col). > So I guess my supplementary question is: how can I get WO to use the > appropriate collation instead of using UPPER? For example, > utf8_general_ci for 'likeinsensitive' and utf8_general_cs for 'like' ? You can't afaik. That's set in the schema for the db/attribute. > On 23/04/2009, at 1:31 PM, Ramsey Lee Gurley wrote: > >> Hi Thomas, >> >> Do you _want_ the default behavior from MySQL? If you're using >> MySQL Administrator, this is easy to change. If you change your >> default collation, you can get rid of the case insensitive behavior >> of MySQL. If you *do* want the case insensitive behavior but want >> just passwords to be case sensitive, you can set this on a column by >> column basis too. >> >> To quote from a random internet message board... >> >> http://www.phpbuilder.com/board/showthread.php?t=10344217 >> >> using a "WHERE first_name = 'Bob'" the following will match: >> >> 'Bob' : utf8_bin, utf8_general_ci, and utf8_general_cs >> 'Böb' : utf8_general_ci and utf8_general_cs >> 'BÖB' : utf8_general_ci >> >> Ramsey >> >> <Picture 1.png> >> On Apr 22, 2009, at 10:44 PM, Thomas Wedderburn-Bisshop wrote: >> >>> Apologies if this is the wrong list, but I found a really old >>> posting >>> by Anjo saying it might be in Wonder. >>> >>> I am running a WO 5.4.3 app (using a recent Wonder version) that >>> talks >>> to a MySQL database via the mysql-connector-java-5.1.6 JDBC adaptor. >>> >>> As I do with other databases, I am using the EOF qualifiers 'like' >>> and >>> 'likeinsensitive' where appropriate. However, a co-worker pointed >>> out >>> that in MySQL, a normal query is always case insensitive, and >>> WebObjects is making the situation worse by generating SQL for >>> insensitivelike in the form: >>> >>> SELECT ... WHERE ... UPPER(to.familyName) LIKE UPPER('Smith') ESCAPE >>> "|" AND UPPER(.... >>> >>> Besides being a performance issue in its own right, this means MySQL >>> is not using any indexes on the queried columns because it doesn't >>> work when UPPER is used. It also means (as my testing has shown) >>> that >>> 'like' is case insensitive, so passwords that are supposed to be >>> case- >>> sensitive are not. >>> >>> Of course I could change all my application's likeinsensitive to >>> like, >>> but I'd be burning my bridges for using other databases. >>> >>> As far as I can tell, Wonder doesn't have a MySQL adaptor. So does >>> anybody have any suggestions on how I could fix this in one place >>> for >>> MySQL without breaking it for other databases? >>> >>> Regards >>> Thomas >>> >>> >>> ------------------------------------------------------------------------------ >>> Stay on top of everything new and different, both inside and >>> around Java (TM) technology - register by April 22, and save >>> $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. >>> 300 plus technical and hands-on sessions. Register today. >>> Use priority code J9JMT32. http://p.sf.net/sfu/p >>> _______________________________________________ >>> Wonder-disc mailing list >>> Won...@li... >>> https://lists.sourceforge.net/lists/listinfo/wonder-disc >> > > > ------------------------------------------------------------------------------ > Stay on top of everything new and different, both inside and > around Java (TM) technology - register by April 22, and save > $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > 300 plus technical and hands-on sessions. Register today. > Use priority code J9JMT32. http://p.sf.net/sfu/p > _______________________________________________ > Wonder-disc mailing list > Won...@li... > https://lists.sourceforge.net/lists/listinfo/wonder-disc with regards, -- Lachlan Deck |