Problem "Cursor is Closed"

[hsmedrado]

Hi everyone!

I´m having a problem to call StoredProcedures with Spring.

The problem is that sometimes the cursor return closed(correctly). There any solution to ignore the problem "Cursor is Closed"?

Follow the exception:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call sCcApiJpConsultaExtrato(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [null]; error code [0]; Cursor is closed.; nested exception is java.sql.SQLException: Cursor is closed.
Caused by: 
java.sql.SQLException: Cursor is closed.
	at oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:323)
	at oracle.jdbc.driver.ResultSetAccessor.getObject(ResultSetAccessor.java:85)
	at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:1401)
	at org.springframework.jdbc.core.JdbcTemplate.extractOutputParameters(JdbcTemplate.java:993)
	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:927)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:883)
	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:915)
	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113)
	at com.eversystems.il.dao.DAOImpl.execute(DAOImpl.java:69)
	at com.eversystems.il.handle.transaction.EV6CMessageHandler.handle(EV6CMessageHandler.java:97)
	at com.eversystems.il.handle.transaction.EV6CMessageHandler.handle(EV6CMessageHandler.java:1)
	at com.eversystems.il.processor.MessageProcessorImpl.process(MessageProcessorImpl.java:135)
	at com.eversystems.il.jms.ILMessageListenerDefault.onMessage(ILMessageListenerDefault.java:35)
	at com.ibm.mq.jms.MQMessageConsumer.receiveAsync(MQMessageConsumer.java:3032)
	at com.ibm.mq.jms.SessionAsyncHelper.run(SessionAsyncHelper.java:412)
	at java.lang.Thread.run(Unknown Source)

Thank you

[denis.zhdanov]

Looks like the problem has nothing to do with spring. Something is wrong with the database/stored procedure.

[hsmedrado]

Looks like the problem has nothing to do with spring. Something is wrong with the database/stored procedure.

Hi Denis, thank you for answer.

But spring ever wait for a opened cursor?

[denis.zhdanov]

Hi Denis, thank you for answer.

But spring ever wait for a opened cursor?

Spring just performs stored procedure call and processes the result via standard jdbc api, nothing more, nothing less.

[hsmedrado]

Spring just performs stored procedure call and processes the result via standard jdbc api, nothing more, nothing less.

My question is if Spring verify if the cursor is open or closed... If this verification has been done by jdbc it´s ok for me.

tks

[hsmedrado]

Hi for all!

I corrected this problem...

I extended JdbcTemplate and overrided the method

Map extractOutputParameters(CallableStatement cs, List parameters) throws SQLException....

And tried the problem...following the code:

public Map extractOutputParameters(CallableStatement cs, List parameters) throws SQLException {
		Map returnedResults = new HashMap();
		int sqlColIndex = 1;
		for (int i = 0; i < parameters.size(); i++) {
			SqlParameter param = (SqlParameter) parameters.get(i);
			if (param instanceof SqlOutParameter) {
				SqlOutParameter outParam = (SqlOutParameter) param;
				if (outParam.isReturnTypeSupported()) {
						Object out = outParam.getSqlReturnType().getTypeValue(
								cs, sqlColIndex, outParam.getSqlType(), outParam.getTypeName());
						returnedResults.put(outParam.getName(), out);
					
				}
				else {
					try{
						Object out = cs.getObject(sqlColIndex);
						if (out instanceof ResultSet) {
							if (outParam.isResultSetSupported()) {
								returnedResults.putAll(processResultSet((ResultSet) out, outParam));
							}
							else {
								String rsName = outParam.getName();
								SqlReturnResultSet rsParam = new SqlReturnResultSet(rsName, new ColumnMapRowMapper());
								returnedResults.putAll(processResultSet(cs.getResultSet(), rsParam));
								logger.info("Added default SqlReturnResultSet parameter named " + rsName);
							}
						}
						else {
							returnedResults.put(outParam.getName(), out);
						}
					}catch (SQLException e) {
						log.debug(e.getMessage());
					}
				}
			}
			if (!(param.isResultsParameter())) {
				sqlColIndex++;
			}
		}
		return returnedResults;
	}