Re: [eclipselink-users] Table name turned lower-case and not working

[Christopher Delahunt <christopher.delahunt@xxxxxxxxxx>]

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