-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Invalid column name ref cursor stored procedure error
PostPosted: Wed Apr 19, 2006 5:43 pm 
Newbie

Joined: Fri Oct 28, 2005 11:49 pm
Posts: 5
Hi

Getting a invalid column name error. As far as I can tell everything is fine.
Any suggestions would be very much appreciated.

Hibernate version: 3.1.2

Mapping documents:
Code:
<sql-query name="selectAllBuUits_SP" callable="true">
  <return class="com.encana.rtd.dao.to.BuTO">
    <return-property name="buId" column="bu_id"/>
    <return-property name="buName" column="bu_name"/>           
  </return>
  { ? = call rtd.rtd_int.get_bu() }
</sql-query>


Return Class:
Code:
package com.encana.rtd.dao.to;

import java.io.Serializable;
import java.util.Date;
import java.util.Set;

public class BuTO implements Serializable {
   private Long   buId;
   private String   buName;
   private String   buStatus;
   private Date   lastModDt;
    private Set     sbus;

   public BuTO() {}
   
   public BuTO(Long buId, String buName) {
      this.buId = buId;
      this.buName = buName;
   }

   public BuTO(Long buId, String buName,Set sbus) {
      this.buId = buId;
      this.buName = buName;
      this.sbus = sbus;
   }
   
   public Set getSbus() {
      return sbus;
   }

   public void setSbus(Set sbus) {
      this.sbus = sbus;
   }

   public Long getBuId() {
      return buId;
   }
   public void setBuId(Long buId) {
      this.buId = buId;
   }
   public String getBuName() {
      return buName;
   }
   public void setBuName(String buName) {
      this.buName = buName;
   }
   public String getBuStatus() {
      return buStatus;
   }
   public void setBuStatus(String buStatus) {
      this.buStatus = buStatus;
   }
   public Date getLastModDt() {
      return lastModDt;
   }
   public void setLastModDt(Date lastModDt) {
      this.lastModDt = lastModDt;
   }

}



Stored procedure:
Code:
CREATE OR REPLACE PACKAGE rtd_int AS   
   FUNCTION get_bu RETURN SYS_REFCURSOR;
END;
/

CREATE OR REPLACE PACKAGE BODY rtd_int AS

   FUNCTION get_bu RETURN SYS_REFCURSOR IS
      bu_cv SYS_REFCURSOR;
   BEGIN
      OPEN bu_cv FOR
      SELECT bu_id, bu_name FROM bus_units
       WHERE bu_status = 'A'
       ORDER BY bu_name;
      RETURN bu_cv;
   END;
END;
/

Full stack trace of any exception that occurs:
Code:
com.encana.esrp.bo.exception.EsrpBOException: com.encana.dao.exception.DAOException: org.hibernate.exception.GenericJDBCException: could not execute query
   at com.encana.esrp.bo.impl.RtdBuBO.getBuCodes(Unknown Source)
   at com.encana.esrp.ui.action.RtdWellSearchAction.setFormProperties(RtdWellSearchAction.java:118)
   at com.encana.esrp.ui.action.RtdWellSearchAction.actionRead(RtdWellSearchAction.java:192)
   at com.encana.esrp.ui.action.EsrpDispatchAction.read(EsrpDispatchAction.java:103)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
   at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
   at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1006)
   at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:419)
   at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:315)
   at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6718)
   at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
   at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
   at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3764)
   at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2644)
   at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:219)
   at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:178)
Caused by: com.encana.dao.exception.DAOException: org.hibernate.exception.GenericJDBCException: could not execute query
   at com.encana.dao.HibernateDAOBase.namedRetrieve(Unknown Source)
   at com.encana.rtd.dao.impl.BuDAO.retrieveCodes(Unknown Source)
   ... 26 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2148)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
   ... 28 more
Caused by: 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:3295)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914)
   at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:1661)
   at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getTimestamp(Unknown Source)
   at org.hibernate.type.TimestampType.get(TimestampType.java:30)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:1899)
   at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1372)
   at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1300)
   at org.hibernate.loader.Loader.getRow(Loader.java:1197)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:569)
   at org.hibernate.loader.Loader.doQuery(Loader.java:689)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   ... 34 more


Name and version of the database you are using: Oracle 9.2.0.1
drivers: 10.2.0.1.0

The generated SQL (show_sql=true):
Hibernate: { ? = call rtd.rtd_int.get_bu() }

Debug level Hibernate log excerpt:
Code:
2006-04-19 15:25:31,302 DEBUG org.hibernate.engine.query.QueryPlanCache:117  - located native-sql query plan in cache ({ ? = call rtd.rtd_int.get_bu() })
2006-04-19 15:25:31,302 DEBUG org.hibernate.impl.SessionImpl:1664  - SQL query: { ? = call rtd.rtd_int.get_bu() }
2006-04-19 15:25:31,302 DEBUG org.hibernate.jdbc.AbstractBatcher:311  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2006-04-19 15:25:31,302 DEBUG org.hibernate.jdbc.ConnectionManager:358  - opening JDBC connection
2006-04-19 15:25:31,302 DEBUG org.hibernate.SQL:346  - { ? = call rtd.rtd_int.get_bu() }
2006-04-19 15:25:31,302 DEBUG org.hibernate.jdbc.AbstractBatcher:424  - preparing statement
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:327  - about to open ResultSet (open ResultSets: 0, globally: 0)
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:682  - processing result set
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:687  - result set row: 0
2006-04-19 15:25:31,317 DEBUG org.hibernate.type.LongType:123  - returning '30' as column: bu_id
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:1164  - result row: EntityKey[com.encana.rtd.dao.to.BuTO#30]
2006-04-19 15:25:31,317 DEBUG org.hibernate.loader.Loader:1347  - Initializing object from ResultSet: [com.encana.rtd.dao.to.BuTO#30]
2006-04-19 15:25:31,317 DEBUG org.hibernate.persister.entity.AbstractEntityPersister:1860  - Hydrating entity: [com.encana.rtd.dao.to.BuTO#30]
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:334  - about to close ResultSet (open ResultSets: 1, globally: 1)
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:319  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.AbstractBatcher:470  - closing statement
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.ConnectionManager:341  - aggressively releasing JDBC connection
2006-04-19 15:25:31,317 DEBUG org.hibernate.jdbc.ConnectionManager:378  - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]


_________________
Thanks,
Brent Parsons


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 20, 2006 1:32 pm 
Newbie

Joined: Fri Oct 28, 2005 11:49 pm
Posts: 5
Hi

Ok, here is what the problem is.
There are other attributes in the BuTO mapping file. HIbernate is trying to pull those in from the result set, even though they are not defined in the
<sql-query mapping.

This is a bug for sure.
Even without the return-property mappings, hibernate is still trying to get columns that are in the mapping file but not the result set.

Mabye I am missing something, but I cannot see what it would be.

Code:
<sql-query name="selectAllBuUits_SP" callable="true">
  <return class="com.encana.rtd.dao.to.BuTO">
    <return-property name="buId" column="bu_id"/>
    <return-property name="buName" column="bu_name"/>           
  </return>
  { ? = call rtd.rtd_int.get_bu() }
</sql-query>


Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="true" package="com.encana.rtd.dao.to">
   <class name="BuTO" table="rtd.bus_units">
      <id name="buId" column="bu_id" type="long" unsaved-value="null">
         <generator class="native"/>
      </id>

      <timestamp name="lastModDt" column="last_mod_dt"/>

      <property name="buName" column="bu_name" type="string" not-null="true" length="25"/>
      <property name="buStatus" column="bu_status" type="string" not-null="true" length="1"/>
      
      <set
            name="sbus"
            lazy="false"
            inverse="true"
            cascade="none"
            sort="unsorted"
            order-by="SBU_NAME">
            <key column="BU_ID"/>
            <one-to-many class="com.encana.rtd.dao.to.SbusTO"/>
        </set>
   
   
   </class>
</hibernate-mapping>

_________________
Thanks,
Brent Parsons


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 20, 2006 2:56 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
how can it be a bug when you tell hibernate to return a BuTO class ?
That of course require us to get all the columns for it, otherwise it would be a partial object and not something that would be persistent.

If you want partial objects returned from <sql-query> use hibernate 3.2 from svn with resulttransformer (see our blog about how to use it)

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 20, 2006 6:09 pm 
Newbie

Joined: Fri Oct 28, 2005 11:49 pm
Posts: 5
Hi

I understand your point. In this case, we are not looking to get something that is going to be persisted, but instead a listing to build a drop down control.

Maybe you can tell me what the purpose of the return-property is for. I assumed it was meant to emulate something like

select new BuTO(bu.buId,bubuName) from BuTO bu


Code:
<sql-query name="selectAllBuUits_SP" callable="true">
  <return class="com.encana.rtd.dao.to.BuTO">
    <return-property name="buId" column="bu_id"/>
    <return-property name="buName" column="bu_name"/>           
  </return>
  { ? = call rtd.rtd_int.get_bu() }
</sql-query>



Thanks,
Brent Parsons

_________________
Thanks,
Brent Parsons


Top
 Profile  
 
 Post subject:
PostPosted: Fri Apr 21, 2006 1:36 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
no, select new BuTO(x,y) from ... for native sql have not been possible until I added resulttransformer support (see http://blog.hibernate.org/cgi-bin/blosx ... ql_and_sql)

<return-property> is simply their to allow you to redefine how a property is going to be retreived. e.g. use different column aliases than what is mapped.

_________________
Max
Don't forget to rate


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.