Hello,
I am running a test on MS SQL Server and MySQL4 with Hibernate3.
Using their own hql dialect
I've got a table core_password_logs like this :
(class_id, item_id, version, .....)
This is OK in MS SQL Server
Code:
SELECT *
FROM core_password_logs V
WHERE (version IN
(SELECT MAX(version)
FROM core_password_logs W
WHERE W.item_id = V.item_id))
the ID is a composite key with (class_id,item_id)
the HQL is :
Code:
query="From HibPasswordLog PWD Where
PWD.id.version in (select max(PWDmax.id.version)
from HibPasswordLog PWDmax
where PWDmax.id.classId = PWD.id.classId
AND PWDmax.id.itemId = PWD.id.itemId)";
and MySQL does not like it at all !!
Code:
SQL Error: 1064, SQLState: 42000
Syntax error or access violation: You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(hibpasswor1_.version) from core_password_logs hib
pas
Can't load class mapping.password.HibPasswordLog
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
ANY IDEA ? how should be the right sql and hql query ?
Login
FAQ
Search
