[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Getting Jboss to accept ref cursor or recognize it

[rsood72]

In our company we currently use Oracle8i and currently our JBoss Server is mapped to Oracle 8i (maybe going forward we will use Oracle10g). Now for a new report that I am working on, my db team has written a stored procedure, the signature of which is some what like this
CREATE OR REPLACE PACKAGE hrdlchg_package as
Type cur_type is ref cursor;
  |   procedure HRDLCHG_RPT(
  |      I_FROMDTDATE       IN ROOMCLASSHURDLES.DTDATE%TYPE,
  |      I_TODTDATE         IN ROOMCLASSHURDLES.DTDATE%TYPE,
  |      I_LROOMCLASSID     IN VARCHAR2,
  |      I_NLOS             IN VARCHAR2,
  |      I_DTMRHDATESTART   IN ROOMCLASSHURDLES.DTMRHDATETIME%TYPE,
  |      I_DTMRHDATEEND     IN ROOMCLASSHURDLES.DTMRHDATETIME%TYPE,
  |      I_ORDERBY          IN VARCHAR2,
  |      list_cur in out cur_type );
  |   procedure HISTORY_RPT(
  |      I_STARTDATE        IN  DATE,
  |      I_ENDDATE          IN  DATE,
  |      I_RHDATESTART      IN  DATE,
  |      I_NLOS             IN  VARCHAR2,
  |      I_LROOMCLASSID     IN  VARCHAR2,
  |      I_ORDERBY          IN  VARCHAR2,
  |      list_cur in out cur_type);
  |   end HRDLCHG_PACKAGE;
The thing to note here is the declaration of cur_type as ref cursor

Now it so happens that jBoss does not have a way to map the jdbc types Types.Other or Types.Ref to this sql type. 
In my standardjbosscmp-jdbc.xml file I tried to map java.sql.ResultSet to Types.Ref and than to ref cursor but that did not work
Something like this
<mapping>
  |             <java-type>java.sql.ResultSet</java-type>
  |             <jdbc-type>REF</jdbc-type>
  |             <sql-type>ref cursor</sql-type>
  |             
  |          </mapping>So would anyone know how to get Jboss to recognize refcursor. 
In the java code I was making call like this

if( ServerUtil.isOracleDB() ) {
  | 		cStmt = GetConnection.getPreparedCall( "{call hrdlchg_package.HISTORY_RPT(?,?,?,?,?,?,?)}");
  | 		//register the OUT parameter of pl/sql function as a OracleTypes.Cursor datatype
  | 		
  | 		cStmt.setTimestamp(1, this.arrivalFrom);
  | 		cStmt.setTimestamp(2, this.arrivalTo);
  | 		cStmt.setTimestamp(3, this.baselineDate);
  | 		cStmt.setString(4, this.LOS);
  | 		cStmt.setString(5, this.roomClasses);
  | 		cStmt.setString(6, this.colToOrderBy);
  | 		
  | 		//cStmt.setNull(1, Types.REF);
  | 		cStmt.registerOutParameter(7, Types.REF);
  | 		cStmt.executeUpdate();
  | 		rs = (ResultSet)cStmt.getObject(1);let me know


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3975203#3975203

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3975203