This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 2491
    
    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: 18541
    
    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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    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
 
Similar Threads
Whats wrong with this NamedQuery ?.
sql script works in mssql but not in mysql
Select statement error
security issue
Native Queries