1

I have this JSP code which performs a very simple SQL query. The query returns a Clob which has to be converted to a String. I am using Oracle for the database, Resin for the server.

The JSP works just fine most of the time but sometimes it throws a SQLException because the connection was already closed. The exception is thrown during the conversion from Clob to String.

Here is the code causing the issue:

<sql:setDataSource dataSource="jdbc/oracle"/>
<sql:transaction>

    <sql:query scope="request" var="query" >
        SELECT * FROM TABLE WHERE x=?
        <sql:param value="${param.y}"/>
    </sql:query>

    <c:forEach items="${query.rows}" var="value">
        <%
            try {
                Map map = (Map) pageContext.getAttribute("value");
                CLOB clob = ((CLOB) map.get("someClob"));
                String str=  clob.getSubString(1, (int) clob.length()));

            } catch (Exception ex) {
        //print out exception
            }
        %>
     </c:forEach>
</sql:transaction>

Has anyone seen this before?

EDIT: using <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

casolorz
  • 8,486
  • 19
  • 93
  • 200

1 Answers1

0

Are you using "http://java.sun.com/jstl/sql_rt" or "http://java.sun.com/jstl/sql" tag namespace?

If */sql_rt than try with the latter.

The CLOBs can be difficult to deal with because sql:query is supposed to close ResultSet and Statement on tag close. At that point the CLOB might be left 'detached' ( I don't know if Oracle's CLOBs can function after the parent ResultSet and Statement were closed ).

The "http://java.sun.com/jstl/sql" 'query' tag should not close the connection before sql:transaction tag closes where as "http://java.sun.com/jstl/sql_rt" will close the connection.

Incarnate1970th
  • 202
  • 2
  • 7
  • Thanks for the info. That is basically what we've figured out so far. It is very odd because it seems to only close it sometimes, like let's say every 20 refreshes one of them will close it. We are using `http://java.sun.com/jsp/jstl/sql`, not sure if that makes a difference? It is almost like the connection might get closed if it runs out of connections on the pool and needs to reclaim it or something. – casolorz Jul 28 '14 at 16:54
  • Try with finer logging for com.caucho.sql . That might show when the connection is closed. If can print the String.valueOf(pageContext.getAttribute("caucho.jstl.sql.conn")) you'll be able to correlate connection used inside the transaction tag and the logs. If you can let us know what you find it would be great! Also, if you can add a spy tag to the database definition it would be helpful. – Incarnate1970th Jul 28 '14 at 17:18
  • I'll see if I'm allowed to do that, the issue only happens on our production servers and it is a big deal to restart them because of some ongoing streams. I might just have to spend some time trying to recreate it on my local server. Thanks. – casolorz Jul 28 '14 at 17:24
  • I see. It's best trying that on dev/qa with a load test like jMeter, etc. The finer logging will produce tons of output on a loaded server which may quickly put the server out of commission for running out of disc quota for logs. – Incarnate1970th Jul 28 '14 at 17:34