Announcement Announcement Module
Collapse
No announcement yet.
RowMapper, ResultSetExtractor and RowCallbackHandler not called for procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • RowMapper, ResultSetExtractor and RowCallbackHandler not called for procedure

    hi!

    i have the following problem. i have a stored procedure in oracle which has 2 out parameters (date). i need to get this 2 dates with a certain timezone so I'd like to use the resultSet.getTimestamp(2, cetCalendar) method (where i can pass a calendar object with the correct timezone).

    i define my output params like this:

    Code:
    SqlOutParameter from = new SqlOutParameter(OUTPARAM_FROM, Types.TIMESTAMP, new RowMapper() {
    
                    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                        System.out.println("mapRow");
                        return resultSet.getTimestamp(1, cetCalendar);
                    }
    
                    public void processRow(ResultSet resultSet) throws SQLException {
                        System.out.println("mapRow");
                    }
    
                    public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                        System.out.println("mapRow");
                        return null;  //To change body of implemented methods use File | Settings | File Templates.
                    }
                });
    strange thing is ... the RowMapper is never called. also if i use RowCallbackHandler or ResultSetExtractor .. none of the methods are ever called. the results show up in the resultmap ... but they are not processed.
    i'm using spring 2.0.7 with oracle 9.2.0.6.0.

    pls help, thx sascha

    Code:
    private class DateRangeGetter extends StoredProcedure {
             private static final String PROCEDURE_NAME = TS_VALUES_PKG+".Get_Range_p";
    
    		private static final String PARAM_TIMESERIES_ID = "timeseriesId";
    
    		private static final String PARAM_TRACE_ID = "traceId";
    
    		private static final String PARAM_SCENARIO_ID = "scenarioId";
    
            private static final String OUTPARAM_FROM = "from";
    
            private static final String OUTPARAM_TO = "to";
    
    		private Log logger = LogFactory.getLog(ValuesGetter.class);
    
            public DateRangeGetter(DataSource ds) {
    			super(ds, PROCEDURE_NAME);
    
    			if (logger.isDebugEnabled())
    				logger.debug("DateRangeGetter instantiated.");
    
    			setFunction(false);
    
                System.out.println("hansi");
                
                SqlOutParameter from = new SqlOutParameter(OUTPARAM_FROM, Types.TIMESTAMP, new RowMapper() {
    
                    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                        System.out.println("mapRow");
                        return resultSet.getTimestamp(1, cetCalendar);
                    }
    
                    public void processRow(ResultSet resultSet) throws SQLException {
                        System.out.println("mapRow");
                    }
    
                    public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                        System.out.println("mapRow");
                        return null;  //To change body of implemented methods use File | Settings | File Templates.
                    }
                });
    
                System.out.println("resultset supported: " + from.isResultSetSupported());
    
                SqlOutParameter to = new SqlOutParameter(OUTPARAM_TO, Types.TIMESTAMP,new RowMapper() {
    
                    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                        System.out.println("mapRow");
                        return resultSet.getTimestamp(2, cetCalendar);
                    }
    
                    public void processRow(ResultSet resultSet) throws SQLException {
                        System.out.println("mapRow");
                    }
    
                    public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                        System.out.println("mapRow");
                        return null;  //To change body of implemented methods use File | Settings | File Templates.
                    }
                });
    
    			// declare the in parameters in correct order
    			declareParameter(new SqlParameter(PARAM_TIMESERIES_ID,
    					Types.NUMERIC));
    			declareParameter(new SqlParameter(PARAM_TRACE_ID, Types.NUMERIC));
    			declareParameter(new SqlParameter(PARAM_SCENARIO_ID, Types.NUMERIC));
    			declareParameter(from);
                declareParameter(to);
    
    			compile();
    		}
    
    
    		public Interval getRange(
    				TimeseriesValuesDescriptor descriptor) {
    			notNull(descriptor, "descriptor");
    
    			HashMap<String, Object> inputParameter = new HashMap<String, Object>();
    			inputParameter.put(PARAM_TIMESERIES_ID, descriptor.getTimeseriesId());
    			inputParameter.put(PARAM_TRACE_ID, descriptor.getTraceId());
    			inputParameter.put(PARAM_SCENARIO_ID, descriptor.getScenarioId());
    
    			// execute function
    			@SuppressWarnings("unchecked")
    			Map resultMap = execute(inputParameter);
    
                DateTimeZone zone = DateTimeZone.forTimeZone(cetCalendar.getTimeZone());
    //these dates are allready wrong
                Object from = resultMap.get(OUTPARAM_FROM);
                Object to = resultMap.get(OUTPARAM_TO);
                return null;
    		}
        }

  • #2
    maybe it's because there is no actual result set for the procedure call? it's really a procedure .. so no return value. only out params.

    what would be the correct way to do it? obtain a CallableStatement from the template? but how can i do this? or just switch to plain jdbc (but do i still have the transaction handling by spring then)?

    thx sascha

    Comment


    • #3
      i just tried something like this ...

      Code:
      HashMap<String, Object> inputParameter = new HashMap<String, Object>();
      			inputParameter.put(PARAM_TIMESERIES_ID, descriptor.getTimeseriesId());
      			inputParameter.put(PARAM_TRACE_ID, descriptor.getTraceId());
      			inputParameter.put(PARAM_SCENARIO_ID, descriptor.getScenarioId());
      
                  CallableStatementCreator creator = newCallableStatementCreator(inputParameter);
                  CallableStatement statement = creator.createCallableStatement(getDataSource().getConnection());
      
                  statement.execute();
      
                  ResultSet rs = statement.getResultSet();
                  rs.next();
      but the result set is null

      Comment


      • #4
        found the solution

        Code:
         CallableStatementCreator creator = newCallableStatementCreator(inputParameter);
                    CallableStatement statement = creator.createCallableStatement(getDataSource().getConnection());
        
                    statement.execute();
        
                    Timestamp from = statement.getTimestamp(4, cetCalendar);
                    Timestamp to = statement.getTimestamp(5, cetCalendar);
                    
                    if(from == null || to == null)
                        return null;
                    return new Interval(new DateTime((from).getTime()), new DateTime((to).getTime()));
        this did the trick!

        Comment

        Working...
        X