You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Andrius Juozapaitis <an...@gmail.com> on 2009/04/14 12:45:43 UTC
Ibatis not closing prepared statements?
Hey,
I've encountered an annoying error, ORA-01000 (too many open cursors)
in our application. What we're doing is we're invoking a stored
procedure, that returns a cursor, which is mapped to a domain object
by a result map.
<procedure id="getRealEstateRegister2" parameterMap="myRegister2">
{ ? = call NTR3_WEB.EPREKYBA_NTR.REGISTRAI_VAR_2CURS(?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
</procedure>
The parameter map is:
<parameterMap id="myRegister2" class="java.util.Map">
<parameter property="result" jdbcType="NUMERIC" mode="OUT" />
....
<parameter property="list" javaType="java.sql.ResultSet"
jdbcType="ORACLECURSOR" resultMap="register-entry-map" mode="OUT"/>
....
</parameterMap>
and the cursor is being mapped to a domain object by this map:
<resultMap id="register-entry-map" class="support.RealEstateRegisterEntry">
<result property="id" column="REG_ID"/>
<result property="systemRegistrationNr" column="REG_TARN_NR"/>
.....
</resultMap>
It seems that ibatis is not closing the prepared statement in
com.ibatis.sqlmap.engine.execution.SqlExecutor:514
...
private static void closeStatement(SessionScope sessionScope,
PreparedStatement ps) {
if (ps != null) {
if (!sessionScope.hasPreparedStatement(ps)) {
try {
ps.close(); <<<< never gets here...
} catch (SQLException e) {
// ignore
}
}
}
}
And oracle starts leaking cursors. Any idea how to solve this? What's
the idea behind nto closing the statement if it's in session scope? On
repeated executions the session scope doesn't contain that statement
anyway? We are also using spring + jboss connection pooling, so
closing the connection after the query is kinda out of question.
best regards,
Andrius Juozapaitis
Re: Ibatis not closing prepared statements?
Posted by Andrius Juozapaitis <an...@gmail.com>.
hey,
just thought I'll report back on this one. The problem, as it turns
out, was in our ibatis mapping, but not in an obvious way. The
specific mapping that was causing problems was defined as
<parameterMap id="myRegister2" class="java.util.Map">
....
<parameter property="list" jdbcType="ORACLECURSOR"
resultMap="register-entry-map" mode="OUT"/>
....
</parameterMap>
(notice that the property didn't have the
javaType="java.sql.ResultSet" specified).
This cursor was a legacy and no longer in use (was always returned
empty, asoracle jdbc driver barfs if I try to return null instead,
saying it's closed), so I figured the javaType wouldn't matter, but
SqlExecutor.retrieveOutputParameters() method has a check that
explicitly expects a "java.sql.ResultSet" java type:
for (int i = 0; i < mappings.length; i++) {
ParameterMapping mapping = ((ParameterMapping) mappings[i]);
if (mapping.isOutputAllowed()) {
if ("java.sql.ResultSet".equalsIgnoreCase(mapping.getJavaTypeName())) {
ResultSet rs = (ResultSet) cs.getObject(i + 1);
.....
rs.close();
} else {
parameters[i] = mapping.getTypeHandler().getResult(cs, i + 1);
}
}
}
so the cursor was never getting closed, even though it was always empty.
hope this helps someone.
Andrius Juozapaitis
On Tue, Apr 14, 2009 at 6:06 PM, Jeff Butler <je...@gmail.com> wrote:
> This may be related to iBATIS prepared statement caching. Try turning
> it off with this setting in your SqlMapConfig file:
>
> <settings statementCachingEnabled="false"/>
>
> Jeff Butler
>
>
> On Tue, Apr 14, 2009 at 5:45 AM, Andrius Juozapaitis <an...@gmail.com> wrote:
>> Hey,
>>
>> I've encountered an annoying error, ORA-01000 (too many open cursors)
>> in our application. What we're doing is we're invoking a stored
>> procedure, that returns a cursor, which is mapped to a domain object
>> by a result map.
>>
>> <procedure id="getRealEstateRegister2" parameterMap="myRegister2">
>> { ? = call NTR3_WEB.EPREKYBA_NTR.REGISTRAI_VAR_2CURS(?, ?, ?,
>> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
>> </procedure>
>>
>> The parameter map is:
>>
>>
>> <parameterMap id="myRegister2" class="java.util.Map">
>> <parameter property="result" jdbcType="NUMERIC" mode="OUT" />
>> ....
>> <parameter property="list" javaType="java.sql.ResultSet"
>> jdbcType="ORACLECURSOR" resultMap="register-entry-map" mode="OUT"/>
>> ....
>> </parameterMap>
>>
>>
>> and the cursor is being mapped to a domain object by this map:
>>
>> <resultMap id="register-entry-map" class="support.RealEstateRegisterEntry">
>> <result property="id" column="REG_ID"/>
>> <result property="systemRegistrationNr" column="REG_TARN_NR"/>
>> .....
>> </resultMap>
>>
>> It seems that ibatis is not closing the prepared statement in
>> com.ibatis.sqlmap.engine.execution.SqlExecutor:514
>> ...
>> private static void closeStatement(SessionScope sessionScope,
>> PreparedStatement ps) {
>> if (ps != null) {
>> if (!sessionScope.hasPreparedStatement(ps)) {
>> try {
>> ps.close(); <<<< never gets here...
>> } catch (SQLException e) {
>> // ignore
>> }
>> }
>> }
>> }
>>
>>
>> And oracle starts leaking cursors. Any idea how to solve this? What's
>> the idea behind nto closing the statement if it's in session scope? On
>> repeated executions the session scope doesn't contain that statement
>> anyway? We are also using spring + jboss connection pooling, so
>> closing the connection after the query is kinda out of question.
>>
>> best regards,
>> Andrius Juozapaitis
>>
>
Re: Ibatis not closing prepared statements?
Posted by Jeff Butler <je...@gmail.com>.
This may be related to iBATIS prepared statement caching. Try turning
it off with this setting in your SqlMapConfig file:
<settings statementCachingEnabled="false"/>
Jeff Butler
On Tue, Apr 14, 2009 at 5:45 AM, Andrius Juozapaitis <an...@gmail.com> wrote:
> Hey,
>
> I've encountered an annoying error, ORA-01000 (too many open cursors)
> in our application. What we're doing is we're invoking a stored
> procedure, that returns a cursor, which is mapped to a domain object
> by a result map.
>
> <procedure id="getRealEstateRegister2" parameterMap="myRegister2">
> { ? = call NTR3_WEB.EPREKYBA_NTR.REGISTRAI_VAR_2CURS(?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
> </procedure>
>
> The parameter map is:
>
>
> <parameterMap id="myRegister2" class="java.util.Map">
> <parameter property="result" jdbcType="NUMERIC" mode="OUT" />
> ....
> <parameter property="list" javaType="java.sql.ResultSet"
> jdbcType="ORACLECURSOR" resultMap="register-entry-map" mode="OUT"/>
> ....
> </parameterMap>
>
>
> and the cursor is being mapped to a domain object by this map:
>
> <resultMap id="register-entry-map" class="support.RealEstateRegisterEntry">
> <result property="id" column="REG_ID"/>
> <result property="systemRegistrationNr" column="REG_TARN_NR"/>
> .....
> </resultMap>
>
> It seems that ibatis is not closing the prepared statement in
> com.ibatis.sqlmap.engine.execution.SqlExecutor:514
> ...
> private static void closeStatement(SessionScope sessionScope,
> PreparedStatement ps) {
> if (ps != null) {
> if (!sessionScope.hasPreparedStatement(ps)) {
> try {
> ps.close(); <<<< never gets here...
> } catch (SQLException e) {
> // ignore
> }
> }
> }
> }
>
>
> And oracle starts leaking cursors. Any idea how to solve this? What's
> the idea behind nto closing the statement if it's in session scope? On
> repeated executions the session scope doesn't contain that statement
> anyway? We are also using spring + jboss connection pooling, so
> closing the connection after the query is kinda out of question.
>
> best regards,
> Andrius Juozapaitis
>