MySQL Case Sensitive Problem

[Tommy Leung]

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]

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?

[Paul Clapham]

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]

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.

[Tommy Leung]

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 ]