Hi,

I have a mytable whose columns in hbm are defined as below

<property
name="Name"
column="name" />

<property
name="Age"
column="age" />

Table Data
name age
John 10
Peter 20

I want run simple query as

select sum(age), count(name) from mytable

the output at database end is 30, 2

Problem is when i try to execute same query using createSQLQuery of hibernate the result is 30, 30 which is same as the aggregation of first function.

Now when i tried using createQuery the output is 30,2 but the constraint for me is, i have to give exactly the same name of column as i have given in hbm ie:

select sum(Age), count(Name) from mytable

The input of query for me is from the user who knows database column names not the hbm property names. So they might only be giving the query which executes at database end.

Kindly help me out if there is some way to tackle the situation or is it like hql can only be used.

thanks

Hi,
Strange !!!
Can you post the log after logging the query and bind parameters.

log4j.logger.org.hibernate.type=debug
log4j.logger.org.hibernate.SQL=debug

Hello,

Following is the error i am getting when trying to use createQuery


java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.IdentNode
\-[IDENT] IdentNode: 'messageId' {originalText=messageId}

at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:140)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:702)
at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:531)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)

The query was

SELECT sum(Payloadsize), count(messageId) FROM OWMessageHistory


NOTE: here payloadsize,messageId is my column name now if instead of messageId if i use MessageId as given in hbm no such problem will occur. But my problem is user is going to give the query input and i want to use createSQlQuery.....

Also to be noted i used Payloadsize as given in hbm instead of column name payloadsize so no issues with that.

thanks

Hi,
>>> Strange !!!
My apologies for not being clear of my view.

Having to use property names ( case sensitive ) is clear.
Failure of SQLQuery ( wrong results ) appeared strange to me.
Please verify if,
the same SQL gets fired in both cases. ( HQLQuery & SQLQuery , ignoring column aliases )

Hi,

Following is the SQLQuery which gets executed and gives the wrong output

SELECT sum(Payloadsize), count(messageId) FROM OWMessageHistory

say 5,5 the aggregation of payloadsize and the correct output should be
5,2

Following is the HQLQuery which gets executed and gives the exception when method createQuery is invoked as it needs the the exact property name but once i give those it works fine (5,2) is output.

SELECT sum(Payloadsize), count(messageId) FROM OWMessageHistory

Exception occurs at invocation of

createQuery("SELECT sum(Payloadsize), count(messageId) FROM OWMessageHistory");


Hope i understood well what i need to provide you as input.

thanks

Any news on this?
I have a similar problem..

Grees,

Michael

I was also getting same problem when i was using following query.


When i modified it to


The problem got resolved. Hope it helps.