File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "MySQL Case Sensitive Problem" Watch "MySQL Case Sensitive Problem" New topic

MySQL Case Sensitive Problem

Tommy Leung

Joined: Jun 07, 2008
Posts: 9

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

Joined: Dec 20, 2006
Posts: 2565

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
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

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: 3753

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.

[OCA 8 Book] [Blog]
Tommy Leung

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 ]
I agree. Here's the link:
subject: MySQL Case Sensitive Problem
It's not a secret anymore!