Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Table name turned lower-case and not working
I've run into the same problem when attempting to work with versions of a database deployed Oracle, Postgres and MySQL.  As Oracle defaults to UPPERCASE and MySQL et al to lowercase it's requiring custom orm.xml files to remap all the table and column names depending on which database is being targeted.  

The business problem-solution is to replicate data from a transaction optimized Oracle RDBMS to a lower cost MySQL warehouse: we need the same beans to access both systems.  Forcing either database to use a non-standard case just transfers the problem to the next application.  If I force the Oracle to lowercase then every application that runs against the RDBMS will have to know that, ditto for MySQL being forced to UPPERCASE.

It would seem attractive for Eclipselink to be able to
  • automatically convert the case of Table and Column names to match the defaults of the host RDBMS, or
  • test the case on each table and column at the time the EntityBean is validated and setup the necessary conversions for the target RDBMS so that subsequent SQL is correctly generated.
Mark

On Wed, Oct 17, 2012 at 8:23 AM, DL <daniel@xxxxxxxxxxxxxxxx> wrote:

Thank you both Chris and Tom! Especially for the debugging tips.

My tables are created by EclipseLink with my @Entity annotations automatically during deployment.

I have resolved my issue, I think what happened to my project was that, 
- On one hand, all the table names are created in all uppercase. Not sure if it was EclipseLink doing that or JDBC driver or something else. 
- On the other hand, when native queries are executed, the table names are converted to all lowercase even though I specified them as upper case in code (I haven't tried the \" method in code yet though).
- Therefore the mismatch and not-found exception.
- This was not a problem when the server (housing GlassFish and MySQL) was running Windows because MySQL default settings in Windows is case-insensitive. This becomes a problem however when the server runs on Debian (or any unix distro I believe) as the MySQL default settings become case-sensitive.

To resolve it, I could either 
1. Specify all the table names as lower case using annotations like @Table, or
2. Change the MySQL settings (setting lower_case_table_names = 1 to be specific) so that all table names are stored and looked-up as lower case.

I used the 2nd option as that's easier for me. 

I found some related stackoverflow threads too..

Hope this will be useful for others!

Daniel


On 17 October 2012 23:59, Christopher Delahunt <christopher.delahunt@xxxxxxxxxx> wrote:
How was your table created?

MySql is generally case sensitive on unix based systems as described here:
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
and defaults to lower case - which is why MYTABLE appears as 'mytable' in the logs.  If the table was defined in upper case with delimeters/quotes, you must use the same format in all SQL queries.
try "select * from \"MYTABLE\" order by rand() limit " + ApplicationConfig.RESULT_SET_LIMIT;

Best Regards,
Chris


On 17/10/2012 8:33 AM, Tom Ware wrote:
Hi Daniel,

Is there any change your JDBC driver is making the change. We do not
edit native query strings in EclipseLink - we send them as provided. You
should be able to tell what we are sending by setting the logging level
to FINER or more and looking at the log. (Persistence Unit Property:
eclipselink.logging.level=FINER)

-Tom

On 16/10/2012 7:23 PM, DL wrote:
Hello Everyone,

I'm new to this group. Nice to meet you all!

What brings me here is a problem I can't find a solution on the net..

On my native query, my table name is in upper-case,

public static final String NATIVE_QUERY_MYSQL_READ_RANDOM =
"select * from MYTABLE order by rand() limit "
+ ApplicationConfig.RESULT_SET_LIMIT;

But when the following is executed, an exception is thrown,

Query queryRandom = em.createNativeQuery(
MyClass.NATIVE_QUERY_MYSQL_READ_RANDOM,
MyClass.class
);

The exception indicates that the table name has been converted to all
lower-case.

Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.3.2.v20111125-r10461):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table
'mydatabase.mytable' doesn't exist
Error Code: 1146
Call: SELECT * FROM mytable ORDER BY RAND() LIMIT 10
Query: ReadAllQuery(referenceClass=MyTable sql="SELECT * FROM mytable
ORDER BY
RAND() LIMIT 10")
at
org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)

at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)



This was not a problem when the server is on a Windows machine, but I
recently
did this on a unix machine (debian) and it has been not working since
due to
this exception.

Any suggestion help is much appreciated.. thanks!

Daniel



_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users




--

Mark Millman | Mizar, LLC
mark.millman@xxxxxxxxx | www.mizar.com
office (360) 945-2643 | mobile (360) 220-3504
Google voice (rings both numbers) (360) 36-ALCOR
589 S Beach Rd. | Point Roberts, WA 98281

 The information contained in this communication may be confidential, is intended only for the use of the recipient(s) named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender.


Back to the top