Stored Procedure Returns Update Count and ResultSet

[jclymer]

Hello,

I have a stored procedure in Sybase 12.5 that does some updates and returns 5 ResultSets. I’ve extended the StoredProcedure class and declared the parameters as shown in the examples I’ve seen. However, I was still unable to retrieve the ResultSets. After doing some debugging into the JdbcTemplate class, it seems that the call to cs.execute() only checks the return value once. In my case the first thing to be returned is an update count. So, unless a ResultSet is the first thing to be returned from a stored procedure, the doInCallableStatement () method won’t extract the ResultSets.

I’ve made some changes to the doInCallableStatement method to have it check the updateCount and the return value in an attempt to correct the problem. See the code below.

Am I missing something or is this a bug in the JdbcTemplate code?

Thanks,
Jeremy

public Map call(CallableStatementCreator csc, final List declaredParameters) throws DataAccessException {
		return (Map) execute(csc, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException {
				boolean retVal = cs.execute();
				int updateCount = cs.getUpdateCount();
				if (logger.isDebugEnabled()) {
					logger.debug("CallableStatement.execute returned [" + retVal + "]");
					logger.debug("CallableStatement.getUpdateCount returned [" + updateCount + "]");
				}
				Map returnedResults = new HashMap();
				if (retVal || (updateCount != -1)) {
					returnedResults.putAll(extractReturnedResultSets(cs, declaredParameters, updateCount));
				}
				returnedResults.putAll(extractOutputParameters(cs, declaredParameters));
				return returnedResults;
			}
		});
	}

protected Map extractReturnedResultSets(CallableStatement cs, List parameters, int updateCount) throws SQLException {
		Map returnedResults = new HashMap();
		int rsIndex = 0;
		boolean moreResults = false;		
		do {
			if(updateCount == -1) {
				Object param = null;
				while(parameters != null && 
					  parameters.size() > rsIndex &&
					  !(param instanceof SqlReturnResultSet)) {									
					param = parameters.get(rsIndex);
					rsIndex++;
				}
				if (param instanceof SqlReturnResultSet) {
					SqlReturnResultSet rsParam = (SqlReturnResultSet) param;
					returnedResults.putAll(processResultSet(cs.getResultSet(), rsParam));
				}
				else {
					logger.warn("ResultSet returned from stored procedure but a corresponding " +
											"SqlReturnResultSet parameter was not declared");
				}
			}
			moreResults = cs.getMoreResults();
			updateCount = cs.getUpdateCount();
		}
		while (moreResults || updateCount != -1);
		return returnedResults;
	}

[trisberg]

Looks like the JdbcTemplate code could be enhanced to handle update counts mixed with resultsets. Thanks for pointing this out. I'll try to add this within the next couple of days.

[trisberg]

I have fixed this issue. We now check for a mix of returned result sets and update counts. This will be part of the upcoming 1.1 final release.

[jclymer]

Hi Thomas,

Thanks for the quick response. I took a look at the code in cvs and realized you changed the way the parameters are pulled from the list. In my case I have an input parameter and 5 result set output parameters. The input parameter gets pulled first when rsIndex is zero causing the first result set to be skipped and the warning to be returned incorrectly. In my implementation I searched the parameter list to find the first available result set parameter. It probably could be refactored a bit to make the intention more clear, but I think you need to take into account non-result set parameters in the list.

Thanks,
Jeremy

[trisberg]

Jeremy,

I did not change the order of declaring/retrieving the parameters - the parameters that don't have a corresponding placeholder (SqlReturnResultSet) have always been assumed to be declared first followed by the parameters that have placeholders from left to right. It seemed logical at he time when I wrote the code since the JDBC spec recommends retrieving any results first before getting the value of any output parameters. We probably need to document this more clearly.

[jclymer]

I got it now, by putting the SqlReturnResultSets first, all get processed. Thanks again.

[faz]

Jeremy,

I to am using Sybase 12.5 and am having problems retrieving ResultSets. Basically within my stored procedure we 'insert into' either fixed database tables or temp tables created within the sproc prior to selecting out the result set.. As soon as we do this no results are returned whatso ever. The RowMapper does not get accessed.

However when you query and select directly from a fixed table the results are returned as expected.

Since you are already using sprocs would you be able to peform an insert within your sproc prior to selecting out just to confirm that its not my setup. Not to sure of the solution either.

The strnage thing is, that when I run the same Spring model processing via a Struts COntoller it works fine.

Thanks for any help.
Faz