Announcement Announcement Module
Collapse
No announcement yet.
SimpleJdbcCall : usage Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimpleJdbcCall : usage

    Hi,

    I'm trying to figure out how to use SimpleJdbcCall to retrieve rows from a Postgresql function.

    The Function works perfect when launched from pgAdmin:

    PHP Code:
    CREATE OR REPLACE FUNCTION test()
      
    RETURNS SETOF tastock AS
    $BODY$
    DECLARE
        
    ret_row tastock;
    BEGIN
        
    FOR ret_row IN
                     SELECT rownum
    tacodestockcode
                     FROM tastock 
    AS a
                     WHERE taCode 
    'HORIZONTAL_SUPPORT'
                     
    AND active 'Y' 
                     
    AND (SELECT max(date) - min(date)
                          
    FROM tastockprice AS bb 
                          WHERE a
    .rownum bb.tastockrownum ) >= 14
                     
    AND stockcode IN 
                     
    SELECT stockcode
                     FROM tastock 
    AS atastockprice AS 
                     WHERE taCode 
    'HORIZONTAL_RESISTANCE'
                     
    AND active 'Y' 
                     
    AND a.rownum b.tastockrownum  
                     
    AND (SELECT max(date) - min(date)
                          
    FROM tastockprice AS bb 
                          WHERE a
    .rownum bb.tastockrownum ) >= 14 )   
                     
    GROUP by rownumtacodestockcode
                     LOOP
                     
    return next ret_row;     
         
    END LOOP;
         RETURN;
    END;
    $BODY$
      
    LANGUAGE 'plpgsql' VOLATILE
      COST 100

    The Java spring Code I wrote is :

    PHP Code:
        @Autowired
        
    private DataSource ds;

        @
    SuppressWarnings("unchecked")
        public 
    Map<StringObjecttest(String tapattern1String tapattern2int minlength) {
        
    simpleJdbcCall = new SimpleJdbcCall(ds)
        .
    withoutProcedureColumnMetaDataAccess()
        .
    withProcedureName("test")
        .
    declareParameters(new SqlOutParameter("rownum"Types.INTEGER))
        .
    declareParameters(new SqlOutParameter("tacode"Types.VARCHAR))
        .
    declareParameters(new SqlOutParameter("stockcode"Types.VARCHAR))
        .
    returningResultSet("tastock", new ParameterizedRowMapper<TaStockDTO>() {
            public 
    TaStockDTO mapRow(ResultSet rsint rowNumthrows SQLException {
                
    TaStockDTO taStockDTO = new TaStockDTO();
                
    taStockDTO.setRowNum(rs.getInt(1));
                
    taStockDTO.setTaCode(rs.getString(2));
                
    taStockDTO.setStockCode(rs.getString(3));
                return 
    taStockDTO;
            }
            });
            
        
    Map result simpleJdbcCall.execute();
        return 
    result;
        } 
    I'm getting the following exception when simpleJdbcCall.execute() is called:

    PHP Code:
    org.springframework.jdbc.BadSqlGrammarExceptionCallableStatementCallback
    bad SQL grammar [{call test(?, ?, ?, ?)}]; 
    nested exception is org.postgresql.util.PSQLException
    A CallableStatement was excecuted with an invalid number of parameters 

    Many thanks for help,

    Frank

  • #2
    You are calling a function NOT a storedprocedure, you need to tell this to the SimpleJdbcCall by default it will use the call syntax for a StoredProcedure. Also your function doesn't have any outparams it is a no=arg function. I suggest you read the javadocs of SimpleJdbcCall.

    Comment


    • #3
      I've changed the method so that it calls a function.

      PHP Code:
          @SuppressWarnings("unchecked")
          public 
      void test() {
          
      SimpleJdbcCall simpleJdbcCall;
          
          
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
          
      jdbcTemplate.setResultsMapCaseInsensitive(true);
          
      simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
              .
      withFunctionName("test")
              .
      returningResultSet("tastock",
                      
      ParameterizedBeanPropertyRowMapper.newInstance(TaStockDTO.class));
          
          
      MapSqlParameterSource in = new MapSqlParameterSource();
          
      simpleJdbcCall.executeFunction(TaStockDTO.class, in);

      Exception I have now is :

      PHP Code:
      org.springframework.dao.InvalidDataAccessApiUsageExceptionRequired input parameter 'rownum' is missing 

      Comment


      • #4
        Does anybody know how to solve my problem ??

        thanks in advance,

        Frank

        Comment

        Working...
        X