Hi, i am new to hibernate so i am not sure the way i trying to do is correct or not, but i had create a function to handle all native select ...

public List getBySQLQuery(String query){
return getSession().createSQLQuery(query).list();
}

here my probelm is...when i try to create the sql and pass it to the function, it will sometimes throws me with exception : INFO [Thread-44] BigIntegerType.nullSafeGet(132) | could not read column value from result set: ; Column '' not found.
WARN [Thread-44] JDBCExceptionReporter.logExceptions(71) | SQL Error: 0, SQLState: S0022
ERROR [Thread-44] JDBCExceptionReporter.logExceptions(72) | Column '' not found.

but i copy the sql that used by hibernate and run it in sql client, it's works and the result set return.


This is a successful query :
select history.create_date as create_date, component.component_name as component_name, user.name as name, count(*) as hours from task_history as history left join user as user on history.user_id = user.user_id left join component as component on history.component_id = component.component_id left join country as country on history.country_id = country.country_id where 1 and history.country_id = 3 and history.shortcode is null and history.component_id = 10 and history.create_date between 20070303 and 20070320 group by history.user_id, history.create_date, history.component_id

and this is a fail one :
select history.create_date as create_date, component.component_name as component_name, user.name as name, count(*) as hours from task_history as history left join user as user on history.user_id = user.user_id left join component as component on history.component_id = component.component_id left join country as country on history.country_id = country.country_id where 1 and history.country_id = 3 and history.shortcode is null and history.component_id = 10 and history.user_id = 27 and history.create_date between 20070303 and 20070320 group by history.user_id, history.create_date, history.component_id

the difficult between this 2 pair is that there is history.user_id in the where clause...

another success one:
Hibernate: select history.create_date as create_date, component.component_name as component_name, user.name as name, count(*) as hours from task_history as history left join user as user on history.user_id = user.user_id left join component as component on history.component_id = component.component_id left join country as country on history.country_id = country.country_id where 1 and history.country_id = 3 and history.shortcode is null and history.component_id = 10 and history.user_id = 27 and history.create_date between 20070301 and 20070320 group by history.user_id, history.create_date, history.component_id

for this success one, if you compare with the fail one, you will find just the start date of create_date is changed...I really got no idea on why will have such problem....maybe the way i try to use the native query is wrong...

here is the exception found on fail one:
INFO [Thread-44] BigIntegerType.nullSafeGet(132) | could not read column value from result set: ; Column '' not found.
WARN [Thread-44] JDBCExceptionReporter.logExceptions(71) | SQL Error: 0, SQLState: S0022
ERROR [Thread-44] JDBCExceptionReporter.logExceptions(72) | Column '' not found.
Mar 20, 2007 1:55:30 PM org.zkoss.zk.ui.impl.UiEngineImpl handleError:616
SEVERE: >>org.hibernate.exception.SQLGrammarException: could not execute query
[SQL: 0, S0022]
>>java.sql.SQLException: Column '' not found.
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
>> at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:955)
>> at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:1226)
>> at org.apache.commons.dbcp.DelegatingResultSet.getBigDecimal(DelegatingResultSet.java:304)
>> at org.apache.commons.dbcp.DelegatingResultSet.getBigDecimal(DelegatingResultSet.java:304)
>> at org.hibernate.type.BigIntegerType.get(BigIntegerType.java:34)
>> at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
>> at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:139)

i really got no idea on why such thing would happen, maybe the way i try is wrong...but i got no idea on why sometimes it works but sometimes not....

I would be appreciate if any one can help me...Many thanks.

Hibernate version i am using : Hibernate 3.2 cr2