Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL Case Sensitive Problem

 
Tommy Leung
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2596
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21296
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic