JdbcTemplate call with ResultSet

[scorchio96]

I'm trying to get the results of a JdbcTemplate call on a stored procedure. I can't figure out from the documentation how to extract the results.

Can anyone provide me with an example of how this should be used?

Thanks,

Kim

MS SQL Stored proc:

CREATE PROCEDURE
SP_GET_NEXT_SEQU (@MYTABLE char(25))
AS
BEGIN TRANSACTION
  BEGIN
  UPDATE F_SEQU WITH (UPDLOCK)  Set SQ_COUNT=SQ_COUNT+1 WHERE SQ_TABLE=@MYTABLE
  SELECT SQ_COUNT  FROM F_SEQU WHERE SQ_TABLE=@MYTABLE
  END
COMMIT TRANSACTION


GO

And the method in my DAO that extends JdbcDaoSupport:

    public int getNextSequ(final String tableName) {
        
        int sequ = 0;
        final List sequs = new ArrayList();
        
        RowCallbackHandler rch = new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                sequs.add(new Integer(rs.getInt(1)));
            }
        };
        
        List params = new ArrayList();
        SqlReturnResultSet rsr = new SqlReturnResultSet("sequ", rch);        
        params.add(rsr);
        
        Map results = getJdbcTemplate().call(new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement cs = con.prepareCall("{call SP_GET_NEXT_SEQU (?)}");              
                cs.setString(1, tableName);
                return cs;
            }
        }, params);
        
        if (!sequs.isEmpty())
            sequ = ((Integer)sequs.iterator().next()).intValue();        
           
        return sequ;
        
    }

[trisberg]

I think you got it. Your Spring code should work as is. What problems are you encountering?

I don't know if it is a good idea to call a stored procedure that does it's own transaction management - you should verify that it does not interfere with any ongoing transaction that you might have already when you call this stored procedure.

[scorchio96]

The call does not return a result set. The stored procedure itself does return a single row single column result when executed in query analyser.

I am confused as to why the SqlReturnResultSet is included in params but not the input parameter.

The documentation doesn't provide any examples on how to get a result set using the template.

[trisberg]

There are two types of parameters in and out. Only refernces for the out parameters need to be supplied since you are directly setting the in parameters in the createCallableStatement callback. You could also supply the in parameters but they would not be used in your case. Some other framework classes like the StoredProcedure class do use them.

The SqlReturnResultSet is a special type of out parameter that returns a resultset. The resultset will be handled by the RowCallbackHandler that you provide to the out parameter. The callback method processRow gets called onece for each row in the resultset and you can map the data to any datastructure that you prefer. The actual resultset is passed in as a parameter to this callback method. In you example the List sequs will contain the data from the returned resultset.

You might find the StoredProcedure class easier to work with. Here is a sample of what this would look like.

	public void callProc(DataSource dataSource) {	
		SeqProcedure sp = new SeqProcedure(dataSource);
		int seq = sp.execute("xxx");
		System.out.println(seq);
	}

	public class SeqProcedure extends StoredProcedure {
		SeqProcedure(DataSource dataSource) {
			super(dataSource, "SP_GET_NEXT_SEQU");
			declareParameter(new SqlReturnResultSet("sequ", new RowMapper() {
				public Object mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					Integer seq = new Integer(rs.getInt(1));
					return seq;
				}
			}));
			declareParameter(new SqlParameter("table", Types.VARCHAR));
			compile();
		}
		
		public int execute(String tableName) {
			Map input = new HashMap(1);
			input.put("table", tableName);
			Map out = execute(input);
			List l = (List)out.get("sequ");
			int seq = ((Number) l.get(0)).intValue();
			return seq;
		}
	}

The constructor of the SeqProcedure defines the in and out parameters and in this example I used the RowMapper for mapping the rows returned in the resultset. The execute method is here used to mimic the signature of the stored procedure and it will create the actual map that gets passed in and extract the data returned.

[scorchio96]

Thanks. I'll give it a try.