Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBC Stored Procedure execute() - Endless Looping Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC Stored Procedure execute() - Endless Looping

    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;
    }

  • #2
    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

    Comment


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

      Comment

      Working...
      X