Spring JDBC Stored Procedure execute() - Endless Looping

[xstevens]

I am currently writing a Spring JDBC object that is getting stuck in an endless loop inside of JdbcTemplate's extractReturnedResultSets method.

For background, JdbcBaseDao extends Spring's JdbcSupportDao. My stored proc should be called like "{ call pck_user.sp_get_user (?,?,?) }". Where the first parameter is an IN OUT REFCURSOR, the second is IN email, and the third is IN password.

Am I setting this proc up wrong?

================================================== ===============
public class JdbcUserDao extends JdbcBaseDao implements UserDao {

public JdbcUserDao() {
}

public User getUser(long userId) {
Long uid = new Long(userId);
UserStoredProcedure sproc = new UserStoredProcedure(this.getDataSource());
List users = sproc.execute(uid, null, null);
if (users != null && users.size() == 1) {
return (User)users.get(0);
}
return null;
}

public User getUser(String email, String password) {
UserStoredProcedure sproc = new UserStoredProcedure(this.getDataSource());
List users = sproc.execute(null, email, password);
if (users != null && users.size() == 1) {
return (User)users.get(0);
}
return null;
}

private class UserStoredProcedure extends StoredProcedure {

public static final String SQL = "pck_user.sp_get_user";

public UserStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(false);
setSql(SQL);
// SQL parameters must be declared in the order in which the stored proc expects them
declareParameter(new SqlOutParameter("refcursor", OracleTypes.CURSOR, new UserRowMapper()));
//declareParameter(new SqlReturnResultSet("refcursor", new UserRowMapper()));
//declareParameter(new SqlParameter("user_id", OracleTypes.NUMBER));
declareParameter(new SqlParameter("email", OracleTypes.VARCHAR));
declareParameter(new SqlParameter("password", OracleTypes.VARCHAR));
compile();
}

public List execute(Long uid, String email, String password) {
Map inParams = new HashMap();
//inParams.put("user_id", uid);
inParams.put("email", email);
inParams.put("password", password);

Map results = execute(inParams);
List userResults = null;
if (results != null) {
userResults = (List)results.get("refcursor");
}

return userResults;
}

}

private class UserRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
User u = new User();
u.setId(rs.getLong("user_id"));
u.setFirstName(rs.getString("first_name"));
u.setLastName(rs.getString("last_name"));
u.setEmail(rs.getString("email"));
u.setPassword(rs.getString("front_end_password"));
u.setPostalCode(rs.getInt("postal_code"));
u.setCountryCode(rs.getInt("country_code"));
int birthYear = rs.getInt("birth_year");
int birthMonth = rs.getInt("month_code");
int birthDate = rs.getInt("date_code");
u.setDob(new GregorianCalendar(birthYear, birthMonth, birthDate));

return u;
}
}

}

Here is the code that loops in Spring's JdbcTemplate. updateCount is always 1, but moreResults is always false.
================================================== =================
protected Map extractReturnedResultSets(CallableStatement cs, List parameters, int updateCount)
throws SQLException {

Map returnedResults = new HashMap();
int rsIndex = 0;
boolean moreResults;
do {
if (updateCount == -1) {
Object param = null;
if (parameters != null && parameters.size() > rsIndex) {
param = parameters.get(rsIndex);
}
if (param instanceof SqlReturnResultSet) {
SqlReturnResultSet rsParam = (SqlReturnResultSet) param;
returnedResults.putAll(processResultSet(cs.getResu ltSet(), rsParam));
}
else {
logger.warn("Results returned from stored procedure but a corresponding " +
"SqlOutParameter/SqlReturnResultSet parameter was not declared");
}
rsIndex++;
}
moreResults = cs.getMoreResults();
updateCount = cs.getUpdateCount();
if (logger.isDebugEnabled()) {
logger.debug("CallableStatement.getUpdateCount() returned " + updateCount);
}
}
while (moreResults || updateCount != -1);
return returnedResults;
}

[trisberg]

If you are using Oracle then this is caused by a bug in the Oracle JDBC driver. The solution is to upgrade to a recent version of the driver. See this post for more detail http://forum.springframework.org/sho...88&postcount=4

[xstevens]

I upgraded to Oracle 10.1.0.4 and the new driver solved my problem. Thanks!