Newbee to Hibernating .....

I am trying to use "getNamedQuery". When I run the SQL, as shown by putting the property "hibernate.show_sql" in config file, The SQL Runs perfectly fine. but in Hibernate it is throwing an exception of "invalid Column", What is annoying is , It worked fine when I was not using named query.

below is the copy of my hibernate.cfg.xml

==============================

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>

<session-factory>
<property name="connection.username">pdbread</property>
<property name="connection.url">
jdbc:oracle:thin:@chi1dds1:1521:pdbdev
</property>
<property name="dialect">
org.hibernate.dialect.Oracle9Dialect
</property>
<property name="myeclipse.connection.profile">PDBDEV</property>
<property name="connection.password">owner</property>
<property name="hibernate.show_sql">true</property>
<property name="connection.driver_class">
oracle.jdbc.driver.OracleDriver
</property>

<mapping resource="com/uscellular/shared/services/hibernate/Organization.hbm.xml" />
</session-factory>

</hibernate-configuration>

========================

The Organization.hbm.xml file
=====================

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping>
<class name="com.uscellular.shared.services.hibernate.Organization" table="ORGANIZATION" schema="PDBREAD">
<id name="organizationKey" type="java.lang.Long">
<column name="ORGANIZATION_KEY" precision="10" scale="0" />
<generator class="assigned" />
</id>
<property name="region" type="java.lang.String">
<column name="REGION" length="30" />
</property>
<property name="clusterDesc" type="java.lang.String">
<column name="CLUSTER_DESC" length="30" />
</property>
<property name="submarketId" type="java.lang.String">
<column name="SUBMARKET_ID" length="3" />
</property>
<property name="submarketDesc" type="java.lang.String">
<column name="SUBMARKET_DESC" length="30" />
</property>
<property name="marketStatus" type="java.lang.String">
<column name="MARKET_STATUS" length="1" />
</property>
<property name="dataCenter" type="java.lang.String">
<column name="DATA_CENTER" length="3" />
</property>
<property name="roamingAdminFee" type="java.lang.Double">
<column name="ROAMING_ADMIN_FEE" precision="9" />
</property>
<property name="networkSurcharge" type="java.lang.Double">
<column name="NETWORK_SURCHARGE" precision="9" />
</property>
<property name="state911Fee" type="java.lang.Double">
<column name="STATE_911_FEE" precision="9" />
</property>
<property name="state911FeeType" type="java.lang.String">
<column name="STATE_911_FEE_TYPE" length="3" />
</property>
<property name="additionalComments" type="java.lang.String">
<column name="ADDITIONAL_COMMENTS" length="2000" />
</property>
<property name="caresModificationDate" type="java.util.Date">
<column name="CARES_MODIFICATION_DATE" length="7" />
</property>
<property name="modificationDate" type="java.util.Date">
<column name="MODIFICATION_DATE" length="7" />
</property>
<property name="modificationUser" type="java.lang.String">
<column name="MODIFICATION_USER" length="10" />
</property>
<property name="approvalDate" type="java.util.Date">
<column name="APPROVAL_DATE" length="7" />
</property>
<property name="approvalUser" type="java.lang.String">
<column name="APPROVAL_USER" length="10" />
</property>
<property name="lastReleaseDate" type="java.util.Date">
<column name="LAST_RELEASE_DATE" length="7" />
</property>
<property name="clusterId" type="java.lang.Long">
<column name="CLUSTER_ID" precision="10" scale="0" not-null="true" />
</property>
</class>
<sql-query name="GetAllOrganization">
<return alias="Organization" class="com.uscellular.shared.services.hibernate.Organization">
<return-property name="organizationKey" column="ORGANIZATION_KEY"/>
<return-property name="region" column="REGION"/>
<return-property name="clusterDesc" column="CLUSTER_DESC"/>
<return-property name="submarketId" column="SUBMARKET_ID"/>
<return-property name="submarketDesc" column="SUBMARKET_DESC"/>
<return-property name="marketStatus" column="MARKET_STATUS"/>
<return-property name="dataCenter" column="DATA_CENTER"/>
<return-property name="roamingAdminFee" column="ROAMING_ADMIN_FEE"/>
<return-property name="networkSurcharge" column="NETWORK_SURCHARGE"/>
<return-property name="state911Fee" column="STATE_911_FEE"/>
<return-property name="state911FeeType" column="STATE_911_FEE_TYPE"/>
<return-property name="additionalComments" column="ADDITIONAL_COMMENTS"/>
<return-property name="caresModificationDate" column="CARES_MODIFICATION_DATE"/>
<return-property name="modificationDate" column="MODIFICATION_DATE"/>
<return-property name="modificationUser" column="MODIFICATION_USER"/>
<return-property name="approvalDate" column="APPROVAL_DATE"/>
<return-property name="approvalUser" column="APPROVAL_USER"/>
<return-property name="lastReleaseDate" column="LAST_RELEASE_DATE"/>
<return-property name="clusterId" column="CLUSTER_ID"/>
</return>
select organization_key, region, cluster_desc, submarket_id, submarket_desc, market_status, data_center, roaming_admin_fee, network_surcharge, state_911_fee, state_911_fee_type, additional_comments , cares_modification_date, modification_date, modification_user, approval_date, approval_user , last_release_date, cluster_id from organization
</sql-query>

</hibernate-mapping>
===========================

when I make a call like this

>>>>

public Organization[] getAll() {
List<Organization> retList;
Organization[] org= null;
try {
org.hibernate.Session s = getSession();
retList = s.getNamedQuery("GetAllOrganization").list();
org = new Organization[retList.size()];
retList.toArray(org);
} catch (RuntimeException re) {
log.error("get failed", re);
throw re;
}
return org;
}
>>>>>

I get an Exception as below

Hibernate: select organization_key, region, cluster_desc, submarket_id, submarket_desc, market_status, data_center, roam
ing_admin_fee, network_surcharge, state_911_fee, state_911_fee_type, additional_comments , cares_modification_date, modi
fication_date, modification_user, approval_date, approval_user , last_release_date, cluster_id from organization
<Jan 9, 2007 11:04:45 AM CST> <Warning> <org.hibernate.util.JDBCExceptionReporter> <000000> <SQL Error: 17006, SQLState:
null>
<Jan 9, 2007 11:04:45 AM CST> <Error> <org.hibernate.util.JDBCExceptionReporter> <000000> <Invalid column name>
<Jan 9, 2007 11:04:45 AM CST> <Error> <com.uscellular.shared.services.hibernate.OrganizationDAO> <000000> <get failed
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:1518)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
Truncated. see log file for complete stacktrace
java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3291)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914)
Truncated. see log file for complete stacktrace
>
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:1518)
at org.hibernate.loader.Loader.list(Loader.java:1498)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1340)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at com.uscellular.shared.services.hibernate.OrganizationDAO.getAll(OrganizationDAO.java:68)
at com.uscellular.shared.services.ejb.MarketService.MarketServiceBean.getAllSubmarkets(MarketServiceBean.java:17
0)



Any help in this is appreciated, I am wondering, why this could be a problem, ( because of any mistake in my configuration or .hbm.xml file ) or something else. when the sql is perfectly running file

I have tried to remove the 'AS' clause in SQL - did not work
I tried to put like ' ORGANIZATION_KEY AS Organization.organizationKey ' = did not work
I tried to put ' From Organization org ' alias in table name - did not work
I have tried putting the Oracle schema.table name in the query - Did not work.

please help !!!!!!!!!!!!!!!!!! have already spent a week worth of time on this issue already.

Thanks

should work work

Thanks Chris, This works, but actually I wanted to use ORACLE Function in SQL, such as Count(*), NVL(), or Decode(). How can I doa that by using {org.*} , is there any way I can achieve that , Actually That is my real purpose of using named query at this time.


Appreciate your help. Thank you very much.

probably, but you can't do it with the notation I have above. Try to follow the notation here and see if that works. http://www.hibernate.org/hib_docs/v3/re ... l#d0e13171

The sql-query notation can be confusing and sometimes takes trial and error to find out what hibernate is expecting and where.

You can also try to return scalars and create the object in your own code. The scalar stuff is just a little below that.