[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Getting Jboss to accept ref cursor or recognize it
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#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...