Hi,

I am trying to move an application over to MySQL from Oracle. For most of the application things seem to be working well but when I try and view a particular jsp page which via a dao performs a very simple query I get an error. I never got this when running with Oracle. I am wondering if it's not the query that's at fault but the fact I am using the openInSessionViewFilter where I can't control how it aliases other tables?

I have switched over to using the correct dialect.

My query in hsql is:

from Entry e
where e.accountId = :ac
and (lower(e.name.firstName) like lower(:fn)
or lower(e.name.lastName) like lower(:fn))
and e.isCurrent=1

Entry has a one component mapping component name="name"... as well as a many-to-one mapping and a set mapping.

Hibernate version: 2.1.8

The exception:

2005-08-25 14:06:56,681 WARN [util.JDBCExceptionReporter] - SQL Error: 1248, SQLState: 42000
2005-08-25 14:06:56,681 ERROR [util.JDBCExceptionReporter] - Every derived table must have its own alias

Failed to load entries; nested exception is:
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: Every derived table must have its
own alias at ...

Name and version of the database you are using: MySQL 4.1.13a

select * from ( select entry0_.entryId as entryId,
entry0_.accountId as accountId,
entry0_.syncDataSourceId as syncData3_,
entry0_.isCurrent as isCurrent,
entry0_.dateCreated as dateCrea5_,
entry0_.dateLastChanged as dateLast6_,
entry0_.isReadOnly as isReadOnly,
entry0_.modifiedBy as modifiedBy,
entry0_.firstName as firstName,
entry0_.lastName as lastName,
entry0_.additionalNames as additio11_,
entry0_.prefix as prefix,
entry0_.suffix as suffix,
entry0_.formattedName as formatt14_,
entry0_.defaultEmail as default15_,
entry0_.defaultEmailType as default16_,
entry0_.defaultPhone as default17_,
entry0_.defaultPhoneType as default18_,
entry0_.modifyingDeviceId as modifyi19_
from entry entry0_
where (entry0_.accountId=? )
and((lower(entry0_.firstName)like lower(?))or(lower(entry0_.lastName)like lower(?)))
and(entry0_.isCurrent=1 )
order by lower(entry0_.firstName)||lower(entry0_.lastName) )
where rownum <= ?

I'm having the same problem, did you ever get arround solving it?

That's strange. I don't think rownum is valid in mysql.

We are migrating from Oracle to MySQL. The hibernate config file still had Oracele as the dialect. Changed to MySQLDialect and is working (for most parts).
thnx