Re: Reg. Problem with Cursors still open.

[Sven Boden <li...@pandora.be>]

I will take a closer look at this during the week-end. At first sight 
(and with a lot of speculation... it's getting late) it looks you're 
trying to use something which is not yet in iBATIS... What you can do 
with iBATIS/Oracle is to get a cursor back (without mapping) it... look 
for example at JIRA IBATIS-53. What you seem to be doing is trying to 
get the out parameter to map to the  ResultMap... and this works because 
you're very lucky.... the first out ResultSet iBATIS finds will probably 
be used as return ResultSet.
I would also expect the cursor to be closed if iBATIS sees the out 
parameter as the actual return ResultSet. But it's not "intended use" of 
iBATIS.

So next question... which query do you use to see how many cursors are 
open on Oracle side. There are some "cursor tables" in Oracle which show 
a cursor as open while in fact it's just lingering.

And if all my assumptions in the first alinea are correct, then what you 
really want to do is not yet in iBATIS (the clean way anyway). And may 
not be in iBATIS for the next weeks/months ... in a clean way.

Regards,
Sven


clement.k.justin@jpmchase.com wrote:

>Hai,
>
>My code goes like this...
>
><sqlMap>
>    <typeAlias alias="Employee" type="test.Employee" />
>    <resultMap id="employee-map" class="Employee">
>        <result property="name" column="ENAME" />
>        <result property="employeeNumber" column="EMPNO" />
>        <result property="departmentNumber" column="DEPTNO" />
>    </resultMap>
>    <parameterMap id="single-rs" class="Employee" >
>        <parameter property="in1" jdbcType="int" 
>javaType="java.lang.Integer" mode="IN"/>
>        <parameter property="output1" jdbcType="ORACLECURSOR" 
>javaType="java.sql.ResultSet" mode="OUT"/> 
>    </parameterMap>
>    <procedure id="GetSingleEmpRs" parameterMap="single-rs" 
>resultMap="employee-map">
>         { call GetSingleEmpRS(?, ?) }
>    </procedure>
></sqlMap>
>
>Java:
>--------
>package test;
>import java.io.Reader;
>import java.util.HashMap;
>import java.util.List;
>import java.util.Map;
>
>import com.ibatis.common.resources.Resources;
>import com.ibatis.sqlmap.client.SqlMapClient;
>import com.ibatis.sqlmap.client.SqlMapClientBuilder;
>
>public class Main {
>
> 
>            public static void main(String arg[]) throws Exception {
>                String resource;
>                Reader reader;
>                List list;
>                SqlMapClient sqlMap;
>                resource = "test/SqlMapConfig.xml";
>                reader = Resources.getResourceAsReader (resource);
>                sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
>                Employee emp = new Employee();
>                emp.setIn1(new Integer(10));
>                // use queryForList because the procedure map defines a 
>resultmap
>                // for the statement
>                list = sqlMap.queryForList("GetSingleEmpRs", emp); 
>
>                System.out.println("--------------------");
>                System.out.println( list );
>                System.out.println("--------------------"); 
>            }
>}
>
>SP:
>---
>CREATE OR REPLACE PROCEDURE sp_GetSingleEmpRS (
>   p_in                       IN       VARCHAR2,
>   p_cursor_o                 OUT      sys_refcursor
>)
>IS
>BEGIN
>   OPEN p_cursor_o FOR
>      SELECT name,
>             employeeNumber,
>             departmentNumber,
>      FROM   employee;
>END;
>
>When i print the List items it list out all the selected employee infor 
>from DB. My problem is the Cursor opened at SP is not getting closed. Each 
>time when i execute this Java Class, one Cursor is getting incremented in 
>DB Side. 
>
>Do you have any solution for closing the Ref Cursors both from iBatis side 
>and application end.
>
>I got a reply from Sven saying that, iBatis will close the Cursor, 
>Application has to close the Result Cursor. In the above application, the 
>Java class doesnot have the hold of the Result Cursor. Can you suggest me 
>with a sample code to over come this issue.
>
>regards,
>Clement
>  
>