GeeCON Prague 2014*
The moose likes JDBC and the fly likes MySQL Case Sensitive Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL Case Sensitive Problem" Watch "MySQL Case Sensitive Problem" New topic
Author

MySQL Case Sensitive Problem

Tommy Leung
Greenhorn

Joined: Jun 07, 2008
Posts: 9
Hi

I have a jsp website which requires users to fill up verification code which is case sensitive when users register or login. However, if user signed in with Capital letter (according to the code image generated), it returned error. If user signed in using all small cap, it is ok. Is it related to MySQL problem? I use MySQL 5.0, can I just add lower_case_table_names=0 to my.ini and fix that problem?

Thanks for advice.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

can I just add lower_case_table_names=0 to my.ini and fix that problem?
No. That will not fix your problem. Can you please show the sql statement that you use to validate the input?


OCUP UML fundamental and ITIL foundation
youtube channel
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

So (just guessing here) your problem is that the data in the database column is "HabQrs" and the user enters "HABQRS" and the two don't match?

If so, then allowing lower-case table names (or making table names case-insensitive, or whatever that MySQL configuration option does) isn't anything to do with that. That would just affect table names and not the contents of tables.

I expect that MySQL has a function named something like "upper" which would allow you to compare an upper-case version of the data in the database to your user's input.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

The "lower_case_table_names" is only related to SQL queries themselves... for example handling "select * from widgets" the same as "select * from WIDGETS". It has nothing to do with WHERE clause conditionals. If case is a concern there is a switch to turn on case insensitive comparisons although for a quick solution could also do "where lower(name) = lower(?)" where ? is the value you're matching on.


My Blog: Down Home Country Coding with Scott Selikoff
Tommy Leung
Greenhorn

Joined: Jun 07, 2008
Posts: 9
When import data into MySQL, table names always lower case even import SQL is case-sensitive, i.e. both upper and lower case.This will cause Hibernate / JDBC to fail cause they are case-sensitive. Is it correct?
[ June 09, 2008: Message edited by: Tommy Leung ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: MySQL Case Sensitive Problem