Announcement Announcement Module
Collapse
No announcement yet.
extractOutputParameters incorrectly throws SQLException: Cursor is closed? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • extractOutputParameters incorrectly throws SQLException: Cursor is closed?

    I'm using the StoredProcedure component, having some Oracle out parameter of type sys_refcursor, and one of them actually has null value when the database stored procedure returns.
    The problem here is that I expect no Exception at all to be thrown.
    Having null value returned in one of the out parameters is a normal scenario and I have to process the rest of the out parameters, those having non-null return values.
    I can't do that though, cause java.sql.CallableStatement.getObject throws "Cursor is closed." exception in extractOutputParameters:
    Code:
    Object out = cs.getObject(sqlColIndex);
    and Springframework propagates this exception up to my
    Code:
    storedProcedure.execute(inputParamsMap);
    call.

    I realize it is a known issue.
    I also saw these threads:
    http://forum.springframework.org/arc...p/t-13555.html
    http://forum.springframework.org/arc...p/t-13118.html

    For me the workaround (which was suggested in the thread #13555 by trisberg) seems not to be proper when I use custom rowmappers for my cursor typed out parameters. This is trisberg's workaround:

    Code:
    List mydata =
    (List)getJdbcTemplate().execute(
    new CallableStatementCreator() {
    public CallableStatement createCallableStatement(Connection connection) throws SQLException {
    CallableStatement cs = connection.prepareCall("{call get_names(?, ?)}");
    cs.registerOutParameter(1, OracleTypes.CURSOR);
    cs.registerOutParameter(2, Types.INTEGER);
    return cs;
    }
    },
    new CallableStatementCallback() {
    public Object doInCallableStatement(CallableStatement cs) throws SQLException {
    cs.execute();
    int ret = cs.getInt(2);
    List rowdata = new ArrayList();
    if (ret == 0) {
    ResultSet rs = (ResultSet) cs.getObject(1);
    while (rs.next()) {
    rowdata.add(rs.getString(2));
    }
    rs.close();
    }
    return rowdata;
    }
    });
    return mydata;
    E.g. trisberg's CallableStatementCallback implementation does not call processResultSet which takes care of rowmappers. And God knows what other functions from the original implementation need to be called in order to properly handle everything.

    So can you guys recommend a nice, elegant workaround which handles every issue originally handled by the framework but does not propagate this very exception.
    I would prefer not tinkering with my own version of SpringFramework.

    Thanks a lot.

    Barna Péter Gergő

  • #2
    I see no one has provided a solution yet and since you didn't like the original workaround I'll suggest an alternate way of handling this that you might like better. Any way you solve this you are going to have to provide some code that handles the exception thrown when there is no result set to be returned.

    What you can do is provide your own SqlReturnType handler for the parameter that returns the ref cursor. In this handler you would retrieve the result set and catch any exceptions thrown. If they are of a certain type just ignore them and return an empty list. If everything works OK just loop over the result set and use a RowMapper to generate object that you add to a list that can be returned.

    Her is an example implementation:

    Code:
        private class ReturnOptionalRefCursor implements SqlReturnType {
            private RowMapper rowMapper = new TestRowMapper();
    
            public Object getTypeValue(CallableStatement cs, int ix, int sqlType, String typeName) throws SQLException {
                try {
                    ResultSet rs = (ResultSet) cs.getObject(ix);
                    List l = new ArrayList();
                    for (int i = 0; rs.next(); i++) {
                        l.add(rowMapper.mapRow(rs, i));
                    }
                    return l;
                }
                catch (SQLException e) {
                    // check the exception and ignore certain exceptions
                    if (e.getMessage() != null && e.getMessage().startsWith("Cursor is closed")) {
                        return new ArrayList();
                    }
                    else {
                        throw e;
                    }
                }
            }
    
        }
    The way you would declare the SqlOutParameter to use this would be:
    Code:
                declareParameter(new SqlOutParameter(
                        "refcursor", 
                        oracle.jdbc.OracleTypes.CURSOR, 
                        null, 
                        new ReturnOptionalRefCursor()));
    The first parameter is the name of the parameter, second is the Oracle specific SQL Type for ref cursors, the third parameter is an optional type name that we don't need here and the fourth and last is an instance of your SqlReturnType implementation.

    This way you can continue to use the StoredProcedure class.
    Last edited by trisberg; Sep 4th, 2007, 09:48 PM.

    Comment

    Working...
    X