-->
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.  [ 4 posts ] 
Author Message
 Post subject: Stored Procedure for Querying does not work
PostPosted: Tue Aug 23, 2005 8:08 am 
Newbie

Joined: Tue Aug 23, 2005 7:40 am
Posts: 2
I am attempting to call an Oracle function (that returns a SYS_REFCURSOR ) using the Halogen framework. The intent is to have the stored procedure return a collection of Halogen Objects.

Although I have exactly the same scenario as mentioned in the Chapter 17 of the Halogen progammer's reference, I am still unable to get get the program executed.

Everytime we run the program we get an SQLException mentioning 'invalid column name'. But I double-checked the Stored-Procedure and the HBM files, and could not find any discrepencies in either of them.
I even tried returning a single column, and writing the simplest of queries possible, but didn't get any success.

Hibernate version:3.0.5

Mapping documents:
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>
  <class name="com.m365.routing.model.OperatorNetwork" table="operator_network" dynamic-update="false" dynamic-insert="false">
    <id name="operatorNetworkId" column="operatornetworkid" type="java.lang.Long">
      <generator class="increment" />
    </id>
    <property name="mnc" type="java.lang.String" update="true" insert="true" column="mnc" />
    <property name="networkTypeId" type="java.lang.Long" update="true" insert="true" column="networktypeid" />
    <property name="se13NetworkCode" type="java.lang.String" update="true" insert="true" column="se13networkcode" />
    <property name="networkName" type="java.lang.String" update="true" insert="true" column="networkname" />
    <property name="ppcin" type="java.lang.String" update="true" insert="true" column="ppcin" />
    <property name="abbrnetName" type="java.lang.String" update="true" insert="true" column="abbrnetname" />
    <property name="networkStatusId" type="java.lang.Long" update="true" insert="true" column="networkstatusid" />
    <property name="sim" type="java.lang.String" update="true" insert="true" column="sim" />
    <property name="lastRemoteUpdate" type="java.util.Calendar" update="true" insert="true" column="lastremoteupdate" />
    <property name="createDate" type="java.util.Calendar" update="true" insert="true" column="createdate" />
    <property name="updateDate" type="java.util.Calendar" update="true" insert="true" column="updatedate" />
    <many-to-one name="country" class="com.m365.routing.model.Country" cascade="none" outer-join="auto" update="true" insert="true" column="countryid" />
    <many-to-one name="gsmOperator" class="com.m365.routing.model.GsmOperator" cascade="none" outer-join="auto" update="true" insert="true" column="operatorid" />
  </class>
 
<sql-query name="selectAllOperatorNetworks_SP" callable="true">
        <return alias="optnet" class="com.m365.routing.model.OperatorNetwork">
           <return-property name="operatorNetworkId" column="opnid"/>
        </return>
        {?=call SelectAllOperatorNetworks()}
        </sql-query>
       
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
Code:
List list = session.getNamedQuery("selectAllOperatorNetworks_SP").list();


Full stack trace of any exception that occurs:
Code:
java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:305)
   at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3718)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2583)
   at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1617)
   at org.hibernate.type.LongType.get(LongType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at com.m365.routing.dao.OperatorNetworkDAO.getAll(OperatorNetworkDAO.java:66)
   at com.m365.routing.test.TestOperatorNetworkDAO.main(TestOperatorNetworkDAO.java:45)

Name and version of the database you are using:
Oracle 9.2

The generated SQL (show_sql=true):
Code:
Hibernate: {?=call SelectAllOperatorNetworks()}


Debug level Hibernate log excerpt:
Code:
17:20:41,190  INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:oracle:thin:@192.168.1.165:1521:ibank
17:20:41,242 DEBUG SessionImpl:250 - opened session at timestamp: 4607171957514240
17:20:41,273 DEBUG SessionImpl:1405 - SQL query: {?=call SelectAllOperatorNetworks()}
17:20:41,273 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
17:20:41,273 DEBUG ConnectionManager:296 - opening JDBC connection
17:20:41,273 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
17:20:41,273 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
17:20:41,273 DEBUG SQL:324 - {?=call SelectAllOperatorNetworks()}
Hibernate: {?=call SelectAllOperatorNetworks()}
17:20:41,273 DEBUG AbstractBatcher:378 - preparing statement
17:20:41,346 DEBUG AbstractBatcher:306 - about to open ResultSet (open ResultSets: 0, globally: 0)
17:20:41,346 DEBUG Loader:405 - processing result set
17:20:41,346 DEBUG Loader:410 - result set row: 0
17:20:41,367 DEBUG AbstractBatcher:313 - about to close ResultSet (open ResultSets: 1, globally: 1)
17:20:41,377 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
17:20:41,377 DEBUG AbstractBatcher:416 - closing statement
17:20:41,377 DEBUG JDBCExceptionReporter:63 - could not execute query [{?=call SelectAllOperatorNetworks()}]
java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 23, 2005 8:59 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
in 3.0.5 you need to list all columns via an return-property
in 3.1 just dont specify and alias and it will automatically use the column names used in your mappings.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 23, 2005 9:00 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
btw. what is Halogen framework ?

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 23, 2005 9:10 am 
Newbie

Joined: Tue Aug 23, 2005 7:40 am
Posts: 2
Sorry about the typo, I meant Hibernate not Halogen.
max wrote:
btw. what is Halogen framework ?


Halogen is another ORM framework (non-opensource) I worked on in my previous project. The name is still on the tip of my tongue (or... my fingertips). ;-)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.