Re: IBatis and Oracle stored functions - strongly typed cursors

[Jan Vissers <Ja...@cumquat.nl>]

Ok,

Got a weakly typed cursor function (sys_refcursor Oracle9i 
functionality) working now. Thanks to Jerome.
Instead of using javax.servlet.jsp.jstl.sql.Result I used 
*oracle.jdbc.rowset.OracleCachedRowSet* which is available for the 10g 
jdbc drivers.

  public Object getResult(ResultGetter getter) throws SQLException {
    OracleCachedRowSet cachedResult = new OracleCachedRowSet();
    ResultSet rs = null;
    try {
      rs = (ResultSet)getter.getObject();
      cachedResult.populate(rs);
    } finally {
      rs.close();
    }
    return cachedResult;
  }

In this case a stored function is called to insert a record, for example:

  function  insert_person
            (name     in varchar2
            ,lastname in varchar2
            ,age      in number           
            )
  return    sys_refcursor

To get this 'weak' cursor back-into bean mode I do:

    Map parameters = new HashMap(4);  
    parameters.put("name","Jan");
    parameters.put("lastname","Vissers");
    parameters.put("age",new Integer(34));
    sql.insert("insertPersonPck",parameters);
    OracleCachedRowSet dataInserted = 
(OracleCachedRowSet)parameters.get("result");

    Map properties = new HashMap();
    ResultSetMetaData md = dataInserted.getMetaData();
    int cols = md.getColumnCount();
    if (dataInserted.next()) for (int i=1; i<=cols ; i++) {
      properties.put(md.getColumnName(i),dataInserted.getString(i)); 
//TODO Change this?
    }
    Person insertedPerson = new Person();
    BeanUtils.populate(insertedPerson,properties);


Is there any other smarter way of doing this? Is it possible to have the 
sqlMap procedure like:

  <procedure id="insertPersonPck" parameterClass="<my actual class>" 
resultMap="<my weak ref cursor containing map">
    <![CDATA[{?= call CLAF_PERSON_PCK.INSERT_PERSON(?,?,?)}]]> 
  </procedure>


and
What about 'strongly' typed cursors?

Thx.
Jan.


Jan Vissers wrote:

> Hi,
>
> I'm evaluating IBatis sqlMap/DAO wrt Oracle functionality, like:
>
>     * "RETURNING ... INTO ..." Clause
>     * CLOB (oracle.sql.CLOB) >32K
>     * BLOB (oracle.sql.BLOB)
>     * XMLType (oracle.xdb.XMLType)
>     * CallableStatement...
>
> It looks to me that none of these are really supported. I, for 
> instance have a packaged function:
>
>     function insert_record( p_i_values in pck2.refcursortype)
>     return   pck2.refcursortype;
>
>
> Is there any way to call these types of objects from IBatis?
>
> Thx.
> Jan.
> -- 
> Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=0&t=70> 
>  And  	Get Thunderbird <http://www.mozilla.org/products/thunderbird/>
>

-- 
Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=0&t=55> 
 And  	Get Thunderbird <http://www.mozilla.org/products/thunderbird/>