Hi,

I am running hibernate 2.1.7c and I am having a problem with this executing this query. Here is the sql which runs fine:

select z.surrogate_id from zone z, section s, sectiondevicemap map, device d, linecontroller c where z.surrogate_id = s.zone_id and s.surrogate_id = map.section_id and map.device_address = d.address and map.controller_address = c.address and map.protocol = c.protocol_id and d.controller_id = c.surrogate_id and d.surrogate_id = 21653

Here is the code I am executing for hibernate:

//create the query
StringBuffer queryStr = new StringBuffer();
queryStr.append("select z.surrogate_id from zone z, section s, ");
queryStr.append("sectiondevicemap map, device d, linecontroller c ");
queryStr.append("where ");
queryStr.append("z.surrogate_id = s.zone_id and ");
queryStr.append("s.surrogate_id = map.section_id and ");
queryStr.append("map.device_address = d.address and ");
queryStr.append("map.controller_address = c.address and ");
queryStr.append("map.protocol = c.protocol_id and ");
queryStr.append("d.controller_id = c.surrogate_id and ");
queryStr.append("d.surrogate_id = ");
queryStr.append(location);

try {
beginTransaction();
session = getSession();
query = session.createQuery(queryStr.toString());
query.setCacheable(true);
Long id = (Long)query.uniqueResult();
commitTransaction();
return id;
} catch (HibernateException ex) {
ex.printStackTrace();
rollbackTransaction();
throw new DataException(ex);
} catch (Throwable error) {
error.printStackTrace();
logger.error(error);
throw new DataException(error);
} finally {
closeSession();
}

Here is the exception:

Caused by: net.sf.hibernate.QueryException: in expected: z [select z.surrogate_id from zone z, section s, sectiondevicemap map, device d, linecontroller c where z.surrogate_id = s.zone_id and s.surrogate_id = map.section_id and map.device_address = d.address and map.controller_address = c.address and map.protocol = c.protocol_id and d.controller_id = c.surrogate_id and d.surrogate_id = 21653]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:295)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1571)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1542)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at net.sf.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:550)
at com.reddline.oms.picksystem.model.PickSystemDataAccessor.getZone(PickSystemDataAccessor.java:119)
... 6 more

Thanks.

chuck

Hi,

After doing some more investigation, it appears that hibernate is expecting an "in" statement in the from clause. I am assuming it is because of the zone z? Does anyone know why this is an error. I have tried another query (same query) except return z.* and call createSQLQuery() and it works fine. I am confused. The SQL is fine. Any help/clarification would be greatly appreciated.